[SOLVED] SQL XML Column - Cross Apply to Produce Multiple Rows based on XML 1 to many

SQL XML Column - Cross Apply to Produce Multiple Rows based on XML 1 to many

I've been studying this post, but I'm still not understanding the "cross apply" with XML data. Also this post.

I have an Xml column that contains a passenger, and the multiple flights and flight-legs on his ticket.

This is what I have working after much fiddling:

Select xmlDoc.value('(//Passenger[1]/text())[1]', 'varchar(100)') as Passenger,
       XmlData2.xmlDoc2.query('//FlightLeg') as xmlDoc2b
FROM xmlData as t 
CROSS APPLY 
    t.xmlDoc.nodes('//FlightLeg') AS XmlData2(xmlDoc2)
where xmlSchema = 'Reservation'

This is the current result. The good news is that there are three rows which correspond to the number of FlightLegs.

The data in each result is the same:

<FlightLeg seq="1">
  <FlightNumber>1849</FlightNumber>
  <DepartureAirport>MDW</DepartureAirport>
  <ArrivalAirport>STL</ArrivalAirport>
  <DepartureDateTime>2019-11-02T19:20:00</DepartureDateTime>
  <ArrivalDateTime>2019-11-02T20:25:00</ArrivalDateTime>
</FlightLeg>
<FlightLeg seq="2">
  <FlightNumber>2105</FlightNumber>
  <DepartureAirport>STL</DepartureAirport>
  <ArrivalAirport>OKC</ArrivalAirport>
  <DepartureDateTime>2019-11-02T21:25:00</DepartureDateTime>
  <ArrivalDateTime>2019-11-02T22:50:00</ArrivalDateTime>
</FlightLeg>
<FlightLeg seq="1">
  <FlightNumber>4565</FlightNumber>
  <DepartureAirport>OKC</DepartureAirport>
  <ArrivalAirport>MDW</ArrivalAirport>
  <DepartureDateTime>2019-11-04T11:10:00</DepartureDateTime>
  <ArrivalDateTime>2019-11-04T13:05:00</ArrivalDateTime>
</FlightLeg>

What I'm aiming for is each one for row 1 to have the first FlightLeg, row 2 the second, etc... Then after that I will pull out specific XML elements into columns.

Easier sample to reproduce:

DECLARE @xml XML='<Reservation><Name>Neal</Name><Flight>12</Flight><Flight>34</Flight><Flight>56</Flight></Reservation>';
DECLARE @xmlTable TABLE (
    xmlDoc Xml
);
Insert into @xmltable values (@xml)
Select xmlDoc from @XmlTable 

Select xmlDoc.value('(//Name[1]/text())[1]', 'varchar(100)') as Passenger,
       XmlData2.xmlDoc2.query('//Flight') as xmlDoc2b
FROM @xmlTable as t
CROSS APPLY 
    t.xmlDoc.nodes('//Flight') AS XmlData2(xmlDoc2)

I could see using some subscript here:

XmlData2.xmlDoc2.query('//Flight[@xxxx]') as xmlDoc2b

but can't use RowNumber, would have to reset on each row from the table.

Now I'm trying to make my SQL look like the sample I found here:

DECLARE @xml XML='<Reservation><Name>Neal</Name><Flight>12</Flight><Flight>34</Flight><Flight>56</Flight></Reservation>';
DECLARE @xmlTable TABLE (
    xmlDoc Xml
);
Insert into @xmltable values (@xml)
--Select xmlDoc from @XmlTable 

-- This was running, but same data on all three rows 
Select xmlDoc.value('(//Name[1]/text())[1]', 'varchar(100)') as Passenger,
       XmlData2.xmlDoc2.query('//Flight') as xmlDoc2b

FROM @xmlTable as t
CROSS APPLY 
    t.xmlDoc.nodes('//Flight') AS XmlData2(xmlDoc2)


-- Trying to make above look like blog sample below 
Select xmlDoc.value('(//Name[1]/text())[1]', 'varchar(100)') as Passenger,
       My_XML.FlightTest.query('//Flight') as FlightLegTest
FROM (SELECT xmlDoc 
      FROM @xmlTable as T(My_XML))
      CROSS APPLY My_XML.nodes('//Flight') AS My_XML (FlightTest) 
/* Sample from https://www.mssqltips.com/sqlservertip/5707/simple-way-to-import-xml-data-into-sql-server-with-tsql/ */ 
SELECT
   MY_XML.Customer.query('Name').value('.', 'VARCHAR(50)')
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'c:\XMLClass\IntroSamples\BulkLoadCustomers1.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('Customers/Customer') AS MY_XML (Customer);

I get error: Incorrect syntax near '(' where the line# points to this row: FROM @xmlTable as T(My_XML))

So far I can't figure out why we put "as x(y)", in other words a second value in parentheses; I'm still researching that.

Solution

CROSS APPLY 
    t.xmlDoc.nodes('//Flight') AS XmlData2(xmlDoc2)

Produces three rows where the context node is already set to the three Flights in turn. You just need to use . to access it.

For example XmlData2.xmlDoc2.query('.') or XmlData2.xmlDoc2.value('.', 'int') as in this db <> fiddle example