SQL Server query XML xpath empty namespace

Question

I encountered a strange behavior on SQL Server 2008 R2 when performing a Xpath query on a XML field with nodes which have an empty namespace.

This query doesn't return results:

[xml_field].query('/RootNode/NodeWithEmptyNamespace')

This query returns results:

[xml_field].query('/dft:RootNode/NodeWithEmptyNamespace')

For clarification this query also returns results so no prefix is mandatory for root node (and probably default namespace):

[xml_field].query('/RootNode')

According to the XML namespace default documation when empty namespace is defined the namespace is none.

The XML in the database is the following:

<RootNode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org">
  <otherNode>Dummy data</otherNode>
  <NodeWithEmptyNamespace xmlns="">Other dummy data</NodeWithEmptyNamespace>
</RootNode>

The full query:

WITH XMLNAMESPACES ('http://tempuri.org' as dft)
SELECT TOP 150 [ID],
       [xml_field].query('/dft:RootNode/NodeWithEmptyNamespace')
  FROM [database];

Does anyone have an explanation for this behavior or is this a bug?

The <RootNode> has a non-empty XML namespace - so you have to define and use it - I don't understand what your question really is ..... — marc_s
My question is not clear, both /RootNode and /dft:RootNode return the RootNode, so the prefix is not required. — user823959

Martin Honnen Martin Honnen · Accepted Answer · 2013-01-08T17:26:35

It is not clear what your problem is. With the XML sample you have posted the RootNode element and the otherNode element are in the namespace http://tempuri.org while the NodeWithEmptyNamespace element is in no namespace (as the xmlns="" puts it there). With XPath a path or step NodeWithEmptyNamespace selects elements of that name in no namespace and RootNode would select an element of that name in no namespace too, only in your input there is no such element. So your path /dft:RootNode/NodeWithEmptyNamespace is doing the right thing, it selects the element with local name RootNode in the namespace tied to the dft prefix (i.e. http://tempuri.org) and its child element with local name NodeWithEmptyNamespace in no namespace.

SQL Server query XML xpath empty namespace

1 Answers