SQL Server中查询XML文档获取带父键的ReferencesValues表格结果

需求说明 #

编写SQL Server查询语句，从指定XML文档中提取ReferencesValues节点的数据，以表格形式返回结果，其中ParentKey字段关联XML中<Reference>标签内的Key值。

示例XML内容 #

<ReferenceType>
      <Reference>
        <Key>100000000517</Key>
        <Code>AGDS</Code>
        <Label>Member GDS / OVS</Label>
      </Reference>
      <ReferencesValues>
        <Key>200000003678</Key>
        <Code>FNGDSB</Code>
        <Label>ad1</Label>
      </ReferencesValues>
      <ReferencesValues>
        <Key>200000000221</Key>
        <Code>GDS01</Code>
        <Label>ad54</Label>
      </ReferencesValues>
    </ReferenceType>

解决方案SQL语句 #

使用CROSS APPLY结合XML节点查询即可实现需求，示例代码如下：

DECLARE @XmlData XML = '<ReferenceType>
      <Reference>
        <Key>100000000517</Key>
        <Code>AGDS</Code>
        <Label>Member GDS / OVS</Label>
      </Reference>
      <ReferencesValues>
        <Key>200000003678</Key>
        <Code>FNGDSB</Code>
        <Label>ad1</Label>
      </ReferencesValues>
      <ReferencesValues>
        <Key>200000000221</Key>
        <Code>GDS01</Code>
        <Label>ad54</Label>
      </ReferencesValues>
    </ReferenceType>';

SELECT
    rv.value('(Key/text())[1]', 'BIGINT') AS Key,
    rv.value('(Code/text())[1]', 'VARCHAR(50)') AS Code,
    rv.value('(Label/text())[1]', 'VARCHAR(100)') AS Label,
    ref.value('(Key/text())[1]', 'BIGINT') AS ParentKey
FROM @XmlData.nodes('/ReferenceType/ReferencesValues') AS Refs(rv)
CROSS APPLY @XmlData.nodes('/ReferenceType/Reference') AS ParentRef(ref);

查询结果 #

Key          Code    Label  ParentKey
200000003678 FNGDSB  ad1    100000000517
200000000221 GDS01   ad54   100000000517

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