Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server creates an XML namespace when default is specified

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)
like image 665
Cobus Kruger Avatar asked Oct 03 '22 14:10

Cobus Kruger


1 Answers

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" />
like image 134
wst Avatar answered Oct 12 '22 11:10

wst