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.
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'
14 People are following this question.
