XML 列 - 三级层次结构 - 具有交叉应用-数据库问题

XML Column - three levels hierarchy - with Cross Apply(XML 列 - 三级层次结构 - 具有交叉应用)

本文介绍了XML 列 - 三级层次结构 - 具有交叉应用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我之前的问题解决了

预期结果是:

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

解决方案

在第二个应用中，您希望应用到 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

解决方案

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 列 - 三级层次结构 - 具有交叉应用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！