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
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)
Thanks for helping me!
14 People are following this question.
