sql - SQL Server 中的 XML 循环

Question

我必须查询 XML 以提取数据并将其放入列中。这非常有效。但是，我想包含一个循环，因为 XML 中的结构如下:

<BlockOrderMessage>           
    <FlightOrder>
        <Flight>
           <FlightNr>5</FlightNr>
           <AircraftType>A255</AircraftType>
        </Flight>
        <PositionOrders>
            <PositionOrder Unit="Unit 5">
               <UnitName>UnitName5</UnitName>
               <CardColor>Blue</CardColor>
            </PositionOrder>
            <PositionOrder Unit='Unit 6">
               <UnitName>UnitName6</UnitName>
               <CardColor>Red</CardColor>
            </PositionOrder>
        </PositionOrders>
    </FlightOrder>
</BlockOrderMessage>   

总是只有一个 ，但可以有更多...现在，我可以使用以下代码生成列(当知道 的数量时):

DECLARE @Data XML
SET @Data =
'<?xml version="1.0" encoding="UTF-8"?>
 <BlockOrderMessage xmlns="http://www...."
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.... file:/C:/Users/.....xsd">
     <FlightOrder>
        <Flight>
            <FlightNr>FlightNr0</FlightNr>
            <AircraftType>AircraftType0</AircraftType>
        <PositionOrders>
            <PositionOrder Unit="Unit 5">
                <UnitName>UnitName5</UnitName>
                <CardColor>Blue</CardColor>
            </PositionOrder>
            <PositionOrder Unit="Unit 6">
                <UnitName>UnitName6</UnitName>
                <CardColor>Red</CardColor>
            </PositionOrder>
        </PositionOrders>
    </FlightOrder>
   </BlockOrderMessage>'

;WITH XMLNAMESPACES (DEFAULT 'http://www....')
SELECT @Data.value('(/BlockOrderMessage/FlightOrder/Flight/FlightNr)[1]','VARCHAR(20)') AS 'FlightNr',
       @Data.value('(/BlockOrderMessage/FlightOrder/Flight/AircraftType)[1]','VARCHAR(20)') AS 'AircraftType',
       @Data.value('(/BlockOrderMessage/FlightOrder/PositionOrders/PositionOrder/@Unit)[1]','VARCHAR(30)') AS 'PosOrder1_Unit ',
       @Data.value('(/BlockOrderMessage/FlightOrder/PositionOrders/PositionOrder/UnitName)[1]','VARCHAR(30)') AS 'PosOrder1_UnitName',
       @Data.value('(/BlockOrderMessage/FlightOrder/PositionOrders/PositionOrder/CardColor)[1]','VARCHAR(30)') AS 'PosOrder1_CardColor',
       @Data.value('(/BlockOrderMessage/FlightOrder/PositionOrders/PositionOrder[2]/@Unit)[1]','VARCHAR(30)') AS 'PosOrder2_Unit',
       @Data.value('(/BlockOrderMessage/FlightOrder/PositionOrders/PositionOrder[2]/UnitName)[1]','VARCHAR(30)') AS 'PosOrder2_UnitName',
       @Data.value('(/BlockOrderMessage/FlightOrder/PositionOrders/PositionOrder[2]/CardColor)[1]','VARCHAR(30)') AS 'PosOrder2_CardColor'

但我想为 PositionOrder 位插入一个循环，我尝试了以下代码(只有最后一部分，因为其余部分保持不变):

;WITH XMLNAMESPACES (DEFAULT 'http://www....')
SELECT @Data.value('(/BlockOrderMessage/FlightOrder/Flight/FlightNr)[1]','VARCHAR(20)') AS 'FlightNr',
       @Data.value('(/BlockOrderMessage/FlightOrder/Flight/AircraftType)[1]','VARCHAR(20)') AS 'AircraftType'
DECLARE @counter INT
SET @counter = 1
WHILE (@Data.value('(/BlockOrderMessage/FlightOrder/PositionOrders/PositionOrder/@Unit)[1]') IS NOT NULL)
BEGIN
;WITH XMLNAMESPACES (DEFAULT 'http://www....') 
SELECT @Data.value('(/BlockOrderMessage/FlightOrder/PositionOrders/PositionOrder/@Unit)[1]','VARCHAR(30)') AS 'PosOrder_@counter_Unit ',
       @Data.value('(/BlockOrderMessage/FlightOrder/PositionOrders/PositionOrder/UnitName)[1]','VARCHAR(30)') AS 'PosOrder_@counter_UnitName',
       @Data.value('(/BlockOrderMessage/FlightOrder/PositionOrders/PositionOrder/CardColor)[1]','VARCHAR(30)') AS 'PosOrder_@counter_CardColor'
SET @counter = @counter +1
END
GO

现在，我在这个循环和我的查询结果中遇到了以下问题:

1. PositionOrder 的查询结果全部为 NULL，我在没有循环的情况下不是这种情况。
2. 我得到两个表作为查询结果，但我希望它们在一个表中。我尝试这样做，所以我只使用一个 SELECT 语句，但我无法获得正确的代码。
3. 我希望列的名称显示计数器编号:因此，如果我们在第二个循环中，我们希望列的名称为 PosOrder_2_...，现在它显示 PosOrder_@counter_...<

有人知道我做错了什么或者我应该如何解决这个问题吗？

提前致谢!

Answer 1

你在找这样的东西吗？

DECLARE @Flights XML = '<BlockOrderMessage>           
    <FlightOrder>
        <Flight>
           <FlightNr>5</FlightNr>
           <AircraftType>A255</AircraftType>
        </Flight>
        <PositionOrders>
            <PositionOrder Unit="Unit 5">
               <UnitName>UnitName5</UnitName>
               <CardColor>Blue</CardColor>
            </PositionOrder>
            <PositionOrder Unit="Unit 6">
               <UnitName>UnitName6</UnitName>
               <CardColor>Red</CardColor>
            </PositionOrder>
        </PositionOrders>
    </FlightOrder>
</BlockOrderMessage>'

SELECT
    FlightNr = FltOrder.value('(Flight/FlightNr)[1]', 'int'),
    AircraftType = FltOrder.value('(Flight/AircraftType)[1]', 'varchaR(100)'),
    PosOrderUnit = PosOrder.value('@Unit', 'varchar(50)'),
    PosOrderUnitName = PosOrder.value('(UnitName)[1]', 'varchar(50)'),
    PosOrderCardColor = PosOrder.value('(CardColor)[1]', 'varchar(50)')
FROM
    @Flights.nodes('/BlockOrderMessage/FlightOrder') AS XTbl(FltOrder)
CROSS APPLY
    FltOrder.nodes('PositionOrders/PositionOrder') AS XTbl2(PosOrder)

这会产生类似这样的输出:

基本上，它从 <BlockOrderMessage> / <FlightOrder> 中获取“基本”数据节点并在第 1 列和第 2 列中显示它们，然后它交叉应用所有子节点 <PositionOrders> / <PositionOrder>里面<FlightOrder>节点并从这些子节点(任意数量)中提取剩余信息。