如何在SQL的OPENXML中访问XML标签的相邻同级标签

你的XML结构中，<ENVELOPE>下包含多组<BILLFIXED>及其同级的<BILLOP>、<BILLCL>、<BILLDUE>、<BILLOVERDUE>节点。使用OPENXML时，由于上下文定位在<BILLFIXED>节点内，无法直接访问同级节点，需要调整XPath路径来关联对应的节点组。

方法一：修正OPENXML的XPath路径 #

通过../回到父节点<ENVELOPE>，并利用position()函数匹配每个<BILLFIXED>对应的同级节点组，同时修正原代码中<BILLCL>的路径错误：

DECLARE @xml xml = '<ENVELOPE>
    <BILLFIXED>
        <BILLDATE>29-Jun-2019</BILLDATE>
        <BILLREF>123</BILLREF>
        <BILLPARTY>ABC</BILLPARTY>
    </BILLFIXED>
    <BILLOP>200</BILLOP>
    <BILLCL>200</BILLCL>
    <BILLDUE>29-Jun-2019</BILLDUE>
    <BILLOVERDUE>1116</BILLOVERDUE>
    <BILLFIXED>
        <BILLDATE>30-Jun-2019</BILLDATE>
        <BILLREF>April To June -19</BILLREF>
        <BILLPARTY>efg</BILLPARTY>
    </BILLFIXED>
    <BILLOP>100</BILLOP>
    <BILLCL>100</BILLCL>
    <BILLDUE>30-Jun-2019</BILLDUE>
    <BILLOVERDUE>1115</BILLOVERDUE>
</ENVELOPE>'

DECLARE @hDoc AS INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml 

SELECT  
    BILLDATE, 
    BILLREF, 
    BILLPARTY, 
    BILLOP,
    BILLCL,
    BILLDUE, 
    BILLOVERDUE
FROM OPENXML(@hDoc, '//BILLFIXED') 
WITH ( 
    BillDate [varchar](50) 'BILLDATE', 
    BIllREF [varchar](50) 'BILLREF', 
    BILLPARTY [varchar](100) 'BILLPARTY',
    -- 通过../访问父节点，并通过position()匹配对应位置的同级节点
    BILLOP [varchar](100) '../BILLOP[position()=count(../BILLFIXED[. current()]/preceding-sibling::BILLFIXED)+1]',
    BILLCL [varchar](100) '../BILLCL[position()=count(../BILLFIXED[. current()]/preceding-sibling::BILLFIXED)+1]',
    BILLDUE [varchar](100) '../BILLDUE[position()=count(../BILLFIXED[. current()]/preceding-sibling::BILLFIXED)+1]',
    BILLOVERDUE [varchar](100) '../BILLOVERDUE[position()=count(../BILLFIXED[. current()]/preceding-sibling::BILLFIXED)+1]'
) 

EXEC sp_xml_removedocument @hDoc

说明：count(../BILLFIXED[. current()]/preceding-sibling::BILLFIXED)+1用于计算当前<BILLFIXED>的位置，确保匹配对应顺序的<BILLOP>等节点组。

方法二：使用XQuery（推荐） #

相较于OPENXML，XQuery更直观且性能更优，通过给每个<BILLFIXED>分配索引，匹配对应位置的同级节点：

DECLARE @xml xml = '<ENVELOPE>
    <BILLFIXED>
        <BILLDATE>29-Jun-2019</BILLDATE>
        <BILLREF>123</BILLREF>
        <BILLPARTY>ABC</BILLPARTY>
    </BILLFIXED>
    <BILLOP>200</BILLOP>
    <BILLCL>200</BILLCL>
    <BILLDUE>29-Jun-2019</BILLDUE>
    <BILLOVERDUE>1116</BILLOVERDUE>
    <BILLFIXED>
        <BILLDATE>30-Jun-2019</BILLDATE>
        <BILLREF>April To June -19</BILLREF>
        <BILLPARTY>efg</BILLPARTY>
    </BILLFIXED>
    <BILLOP>100</BILLOP>
    <BILLCL>100</BILLCL>
    <BILLDUE>30-Jun-2019</BILLDUE>
    <BILLOVERDUE>1115</BILLOVERDUE>
</ENVELOPE>'

SELECT
    billFixed.value('(BILLDATE/text())[1]', 'varchar(50)') AS BILLDATE,
    billFixed.value('(BILLREF/text())[1]', 'varchar(50)') AS BILLREF,
    billFixed.value('(BILLPARTY/text())[1]', 'varchar(100)') AS BILLPARTY,
    @xml.value('(ENVELOPE/BILLOP[position() = sql:column("idx")]/text())[1]', 'varchar(100)') AS BILLOP,
    @xml.value('(ENVELOPE/BILLCL[position() = sql:column("idx")]/text())[1]', 'varchar(100)') AS BILLCL,
    @xml.value('(ENVELOPE/BILLDUE[position() = sql:column("idx")]/text())[1]', 'varchar(100)') AS BILLDUE,
    @xml.value('(ENVELOPE/BILLOVERDUE[position() = sql:column("idx")]/text())[1]', 'varchar(100)') AS BILLOVERDUE
FROM (
    -- 为每个BILLFIXED生成索引，对应同级节点组的位置
    SELECT
        billFixed.query('.') AS billFixed,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS idx
    FROM @xml.nodes('/ENVELOPE/BILLFIXED') AS bf(billFixed)
) AS subQuery

说明：子查询中用ROW_NUMBER()给每个<BILLFIXED>分配顺序索引，主查询通过该索引匹配对应位置的<BILLOP>等节点，确保数据关联正确。

内容的提问来源于stack exchange，提问作者rajsx