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?

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: )

Answer 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                  |
+----------------------------------------------------------------------------------------------------------------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+