Query XML document in SQL Server

Question

I am looking for a SQL Server query that will allow me to retrieve the ReferencesValues in a tabular result with a ParentKey field that refers to the 'key' field in the tag '* Reference'.

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>

Example result:

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

Answer 1

The only tricky thing is to get the reference parent value:

DECLARE @x xml = N'<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  n.value('Key[1]', 'bigint') [key]
    ,       n.value('Code[1]', 'nvarchar(100)') code
    ,       n.value('Label[1]', 'nvarchar(100)') label
    ,       n.value('../Reference[1]/Key[1]', 'nvarchar(100)') AS parentkey
    FROM    @x.nodes('ReferenceType/ReferencesValues') t(n)

There are some optimizations one can do as well:

    SELECT  n.value('(Key/text())[1]', 'bigint') [key]
    ,       n.value('(Code/text())[1]', 'nvarchar(100)') code
    ,       n.value('(Label/text())[1]', 'nvarchar(100)') label
    ,       n.value('(../Reference[1]/Key/text())[1]', 'nvarchar(100)') AS parentkey
    FROM    @x.nodes('ReferenceType/ReferencesValues') t(n)

or traversing xml in two steps to avoid looking behind:

    SELECT  n.value('(Key/text())[1]', 'bigint') [key]
    ,       n.value('(Code/text())[1]', 'nvarchar(100)') code
    ,       n.value('(Label/text())[1]', 'nvarchar(100)') label
    ,       t.value('(Reference[1]/Key/text())[1]', 'nvarchar(100)') AS parentkey
    FROM    @x.nodes('ReferenceType') t(t)
    cross apply t.nodes('ReferencesValues') n(n)