XML 列 - 三级层次结构 - 具有交叉应用

我之前的问题解决了

预期结果是:

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

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) 2019 年 9 月 24 日 13:48:23 版权所有 (C) 2019 Microsoft Corporation Developer Edition(64 位)，Windows Server 2019 Standard 10.0(内部版本 17763:)

解决方案

在第二个应用中，您希望应用到 XmlData2.xmlDoc2 中的节点.按照您编写的方式，它会再次从根查找节点，这将应用于 XML 中的所有 Flight 元素.

DECLARE @xml XML='<预订><姓名>尼尔</姓名><Leg seq=''1''><航班>12</航班></腿><Leg seq=''2''><航班>34</航班><航班>56</航班></腿></预订>'选择@xml声明@xmlTable 表(xml文档);插入到@xmltable 值 (@xml)--从@XmlTable中选择xmlDoc选择 xmlDoc.value('(//Name)[1]', 'varchar(30)') 作为乘客，XmlData2.xmlDoc2.query('.') 作为 XmlData2，XmlData2.xmlDoc2.value('./@seq', 'int') 作为 LegSeq，XmlData3.xmlDoc3.query('.') 作为 XmlData3，XmlData3.xmlDoc3.value('.', 'varchar(20)') as FlightFROM @xmlTable 作为 t交叉申请t.xmlDoc.nodes('//Leg') AS XmlData2(xmlDoc2)交叉申请XmlData2.xmlDoc2.nodes('Flight') AS XmlData3(xmlDoc3);

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

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: )

解决方案

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);