Extracting Related XML Component-CodePudding

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)

CodePudding user response：

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 /.

I suggest you rethink the column types and lengths, can they all really need nvarchar(max)?