question

Michiel281 avatar image
Michiel281 asked

Loop on XML in SQL Server

Hello, 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: 5 A255 UnitName5 Blue
sqlxmlxquerywhile
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have a missing /Flight tag in your example.
1 Like 1 ·
Kev Riley avatar image
Kev Riley answered
Trying to dynamically manage a varying number of columns can be quite difficult. If the data genuinely can occur a number of times, then I would suggest handling the data in the right way, which is as rows rather than columns. Using the Xquery query() method, you can recurse over the repeating data and extract the values. DECLARE @Data XML SET @Data = ' FlightNr0 AircraftType0 UnitName5 Blue UnitName6 Red ' ;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, PositionOrders.PositionOrder.query('.').value('(PositionOrder/@Unit)[1]','varchar(30)') as PositionOrder_Unit, PositionOrders.PositionOrder.query('.').value('(PositionOrder/UnitName)[1]','varchar(30)') as PositionOrder_UnitName, PositionOrders.PositionOrder.query('.').value('(PositionOrder/CardColor)[1]','varchar(30)') as PositionOrder_CardColor from @Data.nodes('/BlockOrderMessage/FlightOrder/Flight/PositionOrders/PositionOrder') as PositionOrders(PositionOrder)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

And there's 50K. Well done @Kev
0 Likes 0 ·
Michiel281 avatar image
Michiel281 answered
Thanks for helping me!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.