Request XML data types with xmlns node attributes

Question

I have the following SQL query:

DECLARE @XMLDOC XML SET @XMLDOC = '<Feed><Product><Name>Foo</Name></Product></Feed>' SELECT xuvalue('Name[1]', 'varchar(100)') as Name from @XMLDOC.nodes('/Feed/Product') x(u) 

This returns:

 Name ---- Foo 

However, if my <Feed> node has an xmlns attribute, this does not return any results:

 DECLARE @XMLDOC XML SET @XMLDOC = '<Feed xmlns="bar"><Product><Name>Foo</Name></Product></Feed>' SELECT xuvalue('Name[1]', 'varchar(100)') as Name from @XMLDOC.nodes('/Feed/Product') x(u) 

Return:

 Name ---- 

This only happens if I have the xmlns attribute, everything else works fine.

Why is this and how can I modify my SQL query to return results regardless of attributes?

Answer 1

If your XML document has XML namespaces, then you need to consider them in your queries!

So, if your XML looks like your sample, you need to:

 -- define the default XML namespace to use ;WITH XMLNAMESPACES(DEFAULT 'bar') SELECT xuvalue('Name[1]', 'varchar(100)') as Name from @XMLDOC.nodes('/Feed/Product') x(u) 

Or, if you prefer to have explicit control over which XML namespace to use (for example, if you have several), use XML namespace prefixes:

 -- define the XML namespace ;WITH XMLNAMESPACES('bar' as b) SELECT xuvalue('b:Name[1]', 'varchar(100)') as Name from @XMLDOC.nodes('/b:Feed/b:Product') x(u) 

Answer 2

Like the XMLNAMESPACES solution, you can also use the XMLNAMESPACES cumbersome local-name syntax ...

 DECLARE @XMLDOC XML SET @XMLDOC = '<Feed xmlns="bar"><Product><Name>Foo</Name></Product></Feed>' SELECT xuvalue('*[local-name() = "Name"][1]', 'varchar(100)') as Name from @XMLDOC.nodes('/*[local-name() = "Feed"]/*[local-name() = "Product"]') x(u) 

Answer 3

You can define namespaces , for example:

 WITH XMLNAMESPACES ('bar' as b) SELECT xuvalue('b:Name[1]', 'varchar(100)') as Name FROM @XMLDOC.nodes('/b:Feed/b:Product') x(u)