問題描述

我之前的問題解決了

預期結果是:

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

這篇關于XML 列 - 三級層次結構 - 具有交叉應用的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！