Parsing XML with namespaces in SQL Server

sql xquery-sql
Guzmán Fernández picture Guzmán Fernández · Aug 12, 2013 · Viewed 9.1k times · Source

I am having a hard time trying to parse an XML that has some namespaces defined:

<TravelItineraryReadRS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="2.2.0">    
<TravelItinerary xmlns="http://webservices.sabre.com/sabreXML/2011/10">
<CustomerInfo>          
<PersonName WithInfant="false" NameNumber="01.01" RPH="1">              
<GivenName>JEFF S</GivenName>"

The XMl is stored in an XML type column named response, and I want to get the GivenName value, for which I use the below query:

;WITH XMLNAMESPACES (DEFAULT 'http://webservices.sabre.com/sabreXML/2011/10')
select 
response.value('(/TravelItineraryReadRS/TravelItinerary/CustomerInfo/PersonName[1]/GivenName)[1]', 'nvarchar(50)') AS Name
from dbo.RezMonitorXMLdataTest where locator = 'GUBXRV'

but instead of getting JEFF S as a result I get NULL. I think this might be related to the namespaces used. Does anyone know how could I get the GivenName value?

Thanks in advance,

Guzmán

Answer

marc_s picture marc_s · Aug 12, 2013

Since your top-level node <TravelItineraryReadRS> is not part of that XML namespace, you cannot use the DEFAULT qualifier. Instead, you have to define a XML namespace prefix, and include that in your XQuery:

;WITH XMLNAMESPACES ('http://webservices.sabre.com/sabreXML/2011/10' AS ns)
SELECT
    XmLContent.value('(/TravelItineraryReadRS/ns:TravelItinerary/ns:CustomerInfo/ns:PersonName[1]/ns:GivenName)[1]', 'nvarchar(50)') AS Name
FROM 
    dbo.RezMonitorXMLdataTest 
WHERE
    locator = 'GUBXRV'

Related questions

Updating XML attribute in SQL Server XML column

I am trying to update a node of in my XML which is stored in a SQL Server XML column, the line below works if my XML is in a XML element but now I somehow need to change it …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More