Extracting Related XML Component

January 4, 2022

I’ve got the below XML where I’m successfully exporting all the values from the cmpD nodes. Where I’m getting stuck however, is how can I extract the related <bend> values that appear within the appropriate dtl node.

Here is the current result being produced:

Current Result

cmp cmpAmt  cmpCode
B   1       A
C   9       A
G   2       A
F   1       A
F   1       L

and here is what I’m trying to get the result as but with the appropriate <bend> value within <Dtl>.

Anyone know how this could be done?

Expected Result

cmp cmpAmt  cmpCode    bend
B   1       A          A 
C   9       A          A 
G   2       A          A
F   1       A          Z
F   1       L          Z

What I have currently:

DECLARE @XML XML = '
            <Dtl>
                <Type>R</Type>          
                <bend>A</bend>
                <cmpD>
                    <cmp>B</cmp>
                    <cmpAmt>1</cmpAmt>
                    <cmpCode>A</cmpCode>
                </cmpD>
                <cmpD>
                    <cmp>C</cmp>
                    <cmpAmt>9</cmpAmt>
                    <cmpCode>A</cmpCode>
                </cmpD>
                <cmpD>
                    <cmp>G</cmp>
                    <cmpAmt>2</cmpAmt>
                    <cmpCode>A</cmpCode>
                </cmpD>
            </Dtl>
            <Dtl>
                <Type>R</Type>
                <bend>Z</bend>
                <cmpD>
                    <cmp>F</cmp>
                    <cmpAmt>1</cmpAmt>
                    <cmpCode>A</cmpCode>
                </cmpD>
                <cmpD>
                    <cmp>F</cmp>
                    <cmpAmt>1</cmpAmt>
                    <cmpCode>L</cmpCode>
                </cmpD>
            </Dtl>'

and the SQL:

select 
    b.value('(cmp/text())[1]','nvarchar(max)'),
    b.value('(cmpAmt/text())[1]','nvarchar(max)'),
    b.value('(cmpCode/text())[1]','nvarchar(max)')
from
    @XML.nodes('/Dtl/cmpD') AS A(b)

>Solution :

You can first shred the DTL nodes, then feed that into another .nodes using cross apply to shred the cmpD nodes:

select
    x2.cmpD.value('(cmp/text())[1]','nvarchar(max)'),
    x2.cmpD.value('(cmpAmt/text())[1]','nvarchar(max)'),
    x2.cmpD.value('(cmpCode/text())[1]','nvarchar(max)'),
    x1.dtl.value('(bend/text())[1]','nvarchar(max)')
from
    @XML.nodes('/Dtl') x1(dtl)
cross apply x1.dtl.nodes('cmpD') AS x2(cmpD);

db<>fiddle

Note how the second .nodes refers to the first, and does not use a leading /.