SQL Server creates an XML namespace when default is specified

Question

Take this simple example:

declare @myXml xml
set @myXML = '
<root>
    <line id="1"/>
    <line id="2"/>
    <line id="3"/>
</root>'
select t.c.query('.')
from @myXml.nodes('/root/line') t(c)

As expected, I get back three rows, looking like this:

<line id="1" />

However, when the XML declares its namespace (even just the default xmlns), you also need to specify that namespace in the SQL, or your result set winds up empty. I know of two ways: a declare statement within the nodes() method call, or a with xmlnamespaces statement. Let's use the latter:

declare @myXml xml
set @myXML = '
<root xmlns="urn:somename">
    <line id="1"/>
    <line id="2"/>
    <line id="3"/>
</root>';

with xmlnamespaces(default 'urn:somename')
select t.c.query('.')
from @myXml.nodes('/root/line') t(c)

While I now get results, there is a definite weirdness in the results. The specified namespace is added as "p1" instead of the default. So my output looks like this:

<p1:line xmlns:p1="urn:somename" id="1" />

In this Technet article, the section B. Declaring a default namespace shows what I'm trying to achieve, but I get the result shown in D. Construction using default namespaces. Since my example doesn't look very much like the latter, I don't understand why I'm getting these prefixes.

Update: For the sake of completeness, this gives exactly the same symptom as the with xmlnamespaces syntax:

select t.c.query('.')
from @myXml.nodes('declare default element namespace "urn:somename";/root/line') t(c)

I think you will need to declare the default element namespace in your XQuery statement, as the article suggests in B. — wst
@wst I get exactly the same symptom whether I use the xmlnamespaces syntax, or the declare statement. — Cobus Kruger
Did you try declaring the default namespace in your select statement? — wst
@wst In the select statement? I don't know what you mean by that. I have tried both the "with xmlnamespaces" syntax shown above and adding the declaration in the nodes() call and the two deliver identical results. — Cobus Kruger
From the perspective of "what data does this piece of XML contain", <p1:line xmlns:p1="urn:somename" id="1" /> and <p1 id="1" /> inside a default namespace of urn:somename contain exactly the same data. Worrying about the exact string format is usually a sign of brittleness elsewhere (such as some other piece of code using hand-rolled parsing rather than using an appropriate XML library) — Damien_The_Unbeliever

wst wst · Accepted Answer · 2013-10-29T15:33:59

Declare the default element namespace in the select when you initially query the XML, and all elements will use the default namespace declaration instead of a prefix:

declare @myXml xml
set @myXML = '
<root xmlns="urn:somename">
    <line id="1"/>
    <line id="2"/>
    <line id="3"/>
</root>';    
with xmlnamespaces(default 'urn:somename')
select t.c.query('
  declare default element namespace "urn:somename";
  .')
from @myXml.nodes('/root/line') t(c)

=>

<line xmlns="urn:somename" id="1" />
<line xmlns="urn:somename" id="2" />
<line xmlns="urn:somename" id="3" />

SQL Server creates an XML namespace when default is specified

1 Answers