Sql – Querying XML data types which have xmlns node attributes

sqlsql-serversql-server-2008xmlxml-namespaces

I have the following SQL query:

DECLARE @XMLDOC XML
SET @XMLDOC = '<Feed><Product><Name>Foo</Name></Product></Feed>'

SELECT  x.u.value('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, then this doesn't return any results:

DECLARE @XMLDOC XML
SET @XMLDOC = '<Feed xmlns="bar"><Product><Name>Foo</Name></Product></Feed>'

SELECT  x.u.value('Name[1]', 'varchar(100)') as Name
from @XMLDOC.nodes('/Feed/Product') x(u)

Returns:

Name
----

This only happens if I have an xmlns attribute, anything else works fine.

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

Best Solution

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

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

-- define the default XML namespace to use
;WITH XMLNAMESPACES(DEFAULT 'bar')
SELECT   
    x.u.value('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 (e.g. if you have multiple), use XML namespace prefixes:

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