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, Oct 28 '13 at 14:31
@wst I get exactly the same symptom whether I use the xmlnamespaces syntax, or the declare statement. — Cobus Kruger, Oct 28 '13 at 15:02
Did you try declaring the default namespace in your `select` statement? — wst, Oct 28 '13 at 17:08
@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, Oct 29 '13 at 07:01
From the perspective of "what data does this piece of XML contain", `` and `` 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, Oct 29 '13 at 08:42
@Damien_The_Unbeliever Absolutely, yes. However, when you don't control the code that will parse the XML, it's best not to deviate from the published spec that the recipient has worked from for quite some time and I don't want to complicate matters by delivering data in a different format from what was needed. This is looking more and more like I'll need to do my processing outside SQL. — Cobus Kruger, Oct 29 '13 at 08:48
See my example below. I tested in SSMS, and it returns the correct output. — wst, Oct 31 '13 at 14:17

wst · Answer 1 · 2013-10-30T14:16:00.103

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 Answers1