question

Michiel281 avatar image
Michiel281 asked

Query To Read Data From XML

There's already a few similar questions on here, but they don't seem to work for me. I need to query an XML file to extract data from it, so we can use the result of the query for other purposes. I have searched on the net for a method to do this, and it seems the value () method is the recommended method. I tried this with the following code (I shortened the XML to the part we use in this example): DECLARE @x XML SET @x = ' 01:01:01.001 2006-05-04 N FlightNr0 2006-05-04 01:01:01.001 AircraftType0 AircraftSeatConfiguration0 ' SELECT @x.value('(/BlockOrderMessage/Blocktype)[1]','VARCHAR(1)') AS 'BlockType', @x.value('(/BlockOrderMessage/FlightOrders/FlightOrder/Flight/FlightNr)[1]','VARCHAR(20)') AS 'FlightNr', @x.value('(/BlockOrderMessage/FlightOrders/FlightOrder/Flight/AircraftType)[1]','VARCHAR(20)') AS 'AircraftType' The result of my query are three columns (so far so good), but the values are NULL... Does anyone know how I should do this? Thanks in advance.
sqlqueryxmlxquery
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.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
You have to take into account the namespace (xmlns) so either alias it : ;with xmlnamespaces('http://...' as ns1) select @x.value('(/ns1:BlockOrderMessage/ns1:BlockType)[1]','VARCHAR(1)') AS 'BlockType', @x.value('(/ns1:BlockOrderMessage/ns1:FlightOrders/ns1:FlightOrder/ns1:Flight/ns1:FlightNr)[1]','VARCHAR(20)') AS 'FlightNr', @x.value('(/ns1:BlockOrderMessage/ns1:FlightOrders/ns1:FlightOrder/ns1:Flight/ns1:AircraftType)[1]','VARCHAR(20)') AS 'AircraftType' or use the wildcard that ignores any particular namespace select @x.value('(/*:BlockOrderMessage/*:BlockType)[1]','VARCHAR(1)') AS 'BlockType', @x.value('(/*:BlockOrderMessage/*:FlightOrders/*:FlightOrder/*:Flight/*:FlightNr)[1]','VARCHAR(20)') AS 'FlightNr', @x.value('(/*:BlockOrderMessage/*:FlightOrders/*:FlightOrder/*:Flight/*:AircraftType)[1]','VARCHAR(20)') AS 'AircraftType' Also in your example, you had a bug in that you referenced 'Blocktype' and not 'BlockType' - be careful XML is case sensitive! The sledgehammer way is to set the default namespace static context ;with xmlnamespaces(default 'http://...') select @x.value('(/BlockOrderMessage/BlockType)[1]','VARCHAR(1)') AS 'BlockType', @x.value('(/BlockOrderMessage/FlightOrders/FlightOrder/Flight/FlightNr)[1]','VARCHAR(20)') AS 'FlightNr', @x.value('(/BlockOrderMessage/FlightOrders/FlightOrder/Flight/AircraftType)[1]','VARCHAR(20)') AS 'AircraftType'
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.

Thank you very much, worked right away!
0 Likes 0 ·

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.