XML Column - using "with namespace" in an xmlColumn.value()

Question

I'm trying to create a lesson to demonstrate namespaces. I have an XML column in my database that contains a FlighReservations root, with multiple FlightReservation elements. Each FlightReservation is like a "row", and has various sub-elements which I want to display as columns. I can get this to work without using namespaces, and now I want to do it with the namespace prefixes.

I have a reproducible sample here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=608c9bf89fe6ada200216fde855557c7

This is the T-SQL:

DECLARE @xmlDoc XML = 
 '<ns0:FlightReservations xmlns:ns0="http://SchemaLesson.FlightReservations">
  <ns1:FlightReservation xmlns:ns1="http://SchemaLesson.FlightReservation">
    <DepartureAirportCode>DFW</DepartureAirportCode>"
  </ns1:FlightReservation>
  <ns1:FlightReservation xmlns:ns1="http://SchemaLesson.FlightReservation">
    <DepartureAirportCode>OKC</DepartureAirportCode>"
  </ns1:FlightReservation>
  </ns0:FlightReservations>
'
;

WITH XMLNAMESPACES ('http://SchemaLesson.FlightReservations' as ns0, 
                    'http://SchemaLesson.FlightReservation' as ns1, 
                    DEFAULT 'http://SchemaLesson.FlightReservation'
                    )
select 
    t2.xmlDoc2.query('.') AS result  , 
    t2.xmlDoc2.value('(.//*[local-name()="DepartureAirportCode"])[1]','varchar(5)') AS DepartureAirportCode1, 
    t2.xmlDoc2.value('(./ns1:FlightReservation/DepartureAirportCode)[1]', 'varchar(5)') AS DepartureAirportCode2, 
    t2.xmlDoc2.value('(.//ns1:FlightReservation/DepartureAirportCode)[1]', 'varchar(5)') AS DepartureAirportCode3, 
    t2.xmlDoc2.value('(.//DepartureAirportCode)[1]', 'varchar(5)') AS DepartureAirportCode4, 
    t2.xmlDoc2.value('(.//ns1:DepartureAirportCode)[1]', 'varchar(5)') AS DepartureAirportCode5 
    from @xmlDoc.nodes('//ns1:FlightReservation')  AS t2(xmlDoc2)

The from statement seems to be working as I am getting two rows of output with the result column set to this (with one or the other airport code):

<ns1:FlightReservation xmlns:ns1="http://SchemaLesson.FlightReservation">
  <DepartureAirportCode>DFW</DepartureAirportCode>"
</ns1:FlightReservation>

I'm able to test my xpaths separately using XpathTester.com: http://www.xpathtester.com/xpath/3a16744fb05e13264af788bb65210df5

The results shown in SQL 2019 are:

The only one that works is where I use the local-name function, and the purpose of my demo is to use the ns1 prefix. Maybe I have a typo, but I've been staring at it and trying things for a while, and cannot see it. Should the "with namespace" setup also work in the .value clause?

Please share desired output. What is the difference between DepartureAirportCode1 - DepartureAirportCode5? — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jun 11, 2020 at 0:48

Yitzhak Khabinsky · Accepted Answer · 2020-06-11 01:55:56Z

1

Please check the T-SQL below.

Your XML has just two namespaces, not three. I adjusted the .nodes() method XPath expression. Columns that have NULL values have questionable XPath in the .value() method.

SQL

DECLARE @xmlDoc XML = 
 '<ns0:FlightReservations xmlns:ns0="http://SchemaLesson.FlightReservations">
  <ns1:FlightReservation xmlns:ns1="http://SchemaLesson.FlightReservation">
    <DepartureAirportCode>DFW</DepartureAirportCode>"
  </ns1:FlightReservation>
  <ns1:FlightReservation xmlns:ns1="http://SchemaLesson.FlightReservation">
    <DepartureAirportCode>OKC</DepartureAirportCode>"
  </ns1:FlightReservation>
  </ns0:FlightReservations>';

WITH XMLNAMESPACES ('http://SchemaLesson.FlightReservations' as ns0
    , 'http://SchemaLesson.FlightReservation' as ns1)
select c.query('.') AS result  , 
    c.value('(.//*[local-name()="DepartureAirportCode"])[1]','varchar(5)') AS DepartureAirportCode1, 
    c.value('(DepartureAirportCode/text())[1]', 'varchar(5)') AS DepartureAirportCode2, 
    c.value('(.//ns1:FlightReservation/DepartureAirportCode)[1]', 'varchar(5)') AS DepartureAirportCode3, 
    c.value('(.//DepartureAirportCode)[1]', 'varchar(5)') AS DepartureAirportCode4, 
    c.value('(.//ns1:DepartureAirportCode)[1]', 'varchar(5)') AS DepartureAirportCode5 
from @xmlDoc.nodes('/ns0:FlightReservations/ns1:FlightReservation')  AS t(c);

Output

+----------------------------------------------------------------------------------------------------------------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
|                                                           result                                                           | DepartureAirportCode1 | DepartureAirportCode2 | DepartureAirportCode3 | DepartureAirportCode4 | DepartureAirportCode5 |
+----------------------------------------------------------------------------------------------------------------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
| <ns1:FlightReservation xmlns:ns1="http://SchemaLesson.FlightReservation"><DepartureAirportCode>DFW</DepartureAirportCode>" |                       |                       |                       |                       |                       |
|   </ns1:FlightReservation>                                                                                                 | DFW                   | DFW                   | NULL                  | DFW                   | NULL                  |
| <ns1:FlightReservation xmlns:ns1="http://SchemaLesson.FlightReservation"><DepartureAirportCode>OKC</DepartureAirportCode>" |                       |                       |                       |                       |                       |
|   </ns1:FlightReservation>                                                                                                 | OKC                   | OKC                   | NULL                  | OKC                   | NULL                  |
+----------------------------------------------------------------------------------------------------------------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+

edited Jun 11, 2020 at 1:55

answered Jun 11, 2020 at 0:52

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

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

3 Comments

NealWalters Over a year ago

Thanks. Yes, it was the extra "Default" namespace that was the issue.

NealWalters Over a year ago

I don't understand why (DepartureAirportCode/text())[1] works for DepartureAirportCode2. This doesn't work in XPath Tester on what is in the "result" column. Why not (./ns1:FlightReservation/DepartureAirportCode)[1]? I thought you had to specify one "." at the beginning when using the result of the "cross apply" or "nodes()"

Yitzhak Khabinsky Over a year ago

@NealWalters, (1) XPath Tester, we don't know what engine is behind it. (2) no need in ".", no need in ns1:FlightReservation. The context is already below that element.

Collectives™ on Stack Overflow

XML Column - using "with namespace" in an xmlColumn.value()

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related