XML Column - three levels hierarchy - with Cross Apply

Question

My prior question was solved here. Now I'm adding one more level of complexity to it - data that is nested parent, child, grandchild.

You can see and run sample here: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=df2766c95383d4c8c2d1f55539634341

Sample Code, where Leg1 might be the trip out, and Leg2 might be the trip back. Each leg can have one or more flights.

DECLARE @xml XML='
<Reservation>
  <Name>Neal</Name>
    <Leg seq=''1''>
      <Flight>12</Flight>
    </Leg>
    <Leg seq=''2''>
      <Flight>34</Flight>
      <Flight>56</Flight>
    </Leg>
</Reservation>'
select @xml

DECLARE @xmlTable TABLE (
    xmlDoc Xml
);
Insert into @xmltable values (@xml)
--Select xmlDoc from @XmlTable 

Select xmlDoc.value('(//Name)[1]', 'varchar(30)') as Passenger,
       XmlData2.xmlDoc2.query('.') as XmlData2,
       XmlData2.xmlDoc2.value('./@seq', 'int') as LegSeq,
       XmlData3.xmlDoc3.query('.') as XmlData3,
       XmlData3.xmlDoc3.value('.', 'varchar(20)') as Flight
FROM @xmlTable as t
     CROSS APPLY 
        t.xmlDoc.nodes('//Leg') AS XmlData2(xmlDoc2)
     CROSS APPLY 
        t.xmlDoc.nodes('//Flight') AS XmlData3(xmlDoc3)

The issue is that I'm still need 3 rows returned, but now I'm getting 6.

Expected result would be:

Neal LegSeq=1 Flight=12 
Neal LegSeq=2 Flight=34
Neal LegSeq=2 Flight=56

Hi Neal, please include the expected output. There is any number of ways to produce three rows, that doesn't describe what your output needs to be really. — TT., May 26 '20 at 05:38
Added expected results. I thought it was obvious from the question and the hierarchy of the XML data. There is one Passenger, two flight legs, and three flight numbers. The first flight leg has one flight number, and the second flight leg has two flight numbers. — NealWalters, May 26 '20 at 13:42

score 1 · Accepted Answer · answered May 26 '20 at 13:52

In the second apply, you want to be applying to the nodes from XmlData2.xmlDoc2. The way you have it written, it looks for nodes from the root again, which will apply to all Flight elements in the XML.

DECLARE @xml XML='
<Reservation>
  <Name>Neal</Name>
    <Leg seq=''1''>
      <Flight>12</Flight>
    </Leg>
    <Leg seq=''2''>
      <Flight>34</Flight>
      <Flight>56</Flight>
    </Leg>
</Reservation>'
select @xml

DECLARE @xmlTable TABLE (
    xmlDoc Xml
);
Insert into @xmltable values (@xml)
--Select xmlDoc from @XmlTable 

Select xmlDoc.value('(//Name)[1]', 'varchar(30)') as Passenger,
       XmlData2.xmlDoc2.query('.') as XmlData2,
       XmlData2.xmlDoc2.value('./@seq', 'int') as LegSeq,
       XmlData3.xmlDoc3.query('.') as XmlData3,
       XmlData3.xmlDoc3.value('.', 'varchar(20)') as Flight
FROM @xmlTable as t
     CROSS APPLY 
        t.xmlDoc.nodes('//Leg') AS XmlData2(xmlDoc2)
     CROSS APPLY 
        XmlData2.xmlDoc2.nodes('Flight') AS XmlData3(xmlDoc3);

Ah, I should have thought of that. Just like a join, each cross apply would be normally be a different item. — NealWalters, May 26 '20 at 16:39

XML Column - three levels hierarchy - with Cross Apply

1 Answers1

Linked