Loop on XML in SQL Server

Question

I have to query an XML to extract the data and put it into columns. This works perfectly. However, I want to include a loop because the structure in the XML is as follows:

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

There's always only one , but there can be more ... Now, I can generate the column (when knowing the amount of ) using following code:

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'

But I want to insert a loop for the PositionOrder bit, I tried the following code (only the last part, since the rest stays the same):

;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

Now, I have the following problems with this loop and the result of my query:

The results of the query for the PositionOrder are all NULL, this wasn't the case when I did it without loops.
I get two tables as query result, but I want them in one table. I tried to do it so I only use one SELECT statement, but I can't get the code right.
I want the name of the column to show the counter number: So if we are in the second loop, we want the name of the column to be PosOrder_2_..., now it shows PosOrder_@counter_...

Does anybody know what I am doing wrong or how I should fix this problem?

Thanks in advance!

marc_s · Accepted Answer · 2014-03-03 10:09:10Z

6

Are you looking for something like this?

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)

This would produce an output something like this:

enter image description here

Basically, it grabs the "base" data from the <BlockOrderMessage> / <FlightOrder> node and displays those in columns 1 & 2, and then it cross applies all subnodes <PositionOrders> / <PositionOrder> inside that <FlightOrder> node and extracts the remaining information from those subnodes (any number of them).

answered Mar 3, 2014 at 10:09

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michiel281 Over a year ago

The CROSS APPLY method is indeed a big improvement on my code, thanks for that! This is very useful. Is it possible to get it into one record? So like this:" FlightNr | AircraftType | PosOrder1_Unit | PosOrder1_UnitName | PosOrder1_CardColor | PosOrder2_Unit | ... " because we want to send one record as result. I want to insert the number of my 'loop' in the name of the column.

Collectives™ on Stack Overflow

Loop on XML in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related