CODEWITHSUNDEEP
sql-serverxmlxpathsql-server-2008-r2
user823959
user823959

Reputation: 783

SQL Server query XML xpath empty namespace

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?

Upvotes: 1

Views: 3050

Answers (1)

Martin Honnen
Martin Honnen

Reputation: 167516

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.

Upvotes: 4

Related Questions