CODEWITHSUNDEEP
sqlsql-serverxmlxml-namespaces
DTRT
DTRT

Reputation: 11040

How to add or change an Xml namespace in SQL Server / Cannot use 'xmlns' in the name expression

I have a XML column that is not generated with a namespace, meaning no xmlns attribute. Unfortunately, I cannot fix the actual problem, meaning where the XML is created.

For example:

<root>Our Content</root>

I can modify the XML data before it's returned to a particular client that expects a namespace. What I want is pretty simple:

<root xmlns="http://OurNamespace">Our Content</root>

I tried something like:

.modify('insert attribute xmlns {"ournamespace"}...

But that errors with

Cannot use 'xmlns' in the name expression.

My questions are:

  1. Is there a technique around this particular error?
  2. Is there an alternative or better way to add/change a namespace on a SQL XML type?

This is in a SQL Server 2012 stored procedure.

Upvotes: 1

Views: 5821

Answers (4)

Litus
Litus

Reputation: 1

that is my solution (wildcards solution from Pass xmlnamespaces with default as variable in SQL):

declare @xml xml
select @xml = N'<table xmlns="http://www.w3schools.com/furniture">
   <name>name_one</name>
   <width>80</width>
   <length>120</length>
</table>'

select @xml

--it's OK
;WITH XMLNAMESPACES (DEFAULT 'http://www.w3schools.com/furniture')
select @xml.value('(table/name)[1]','nvarchar(10)')

--it's OK too 
select @xml.value('(*:table/*:name)[1]','nvarchar(10)')

--it's empty
select @xml.value('(table/name)[1]','nvarchar(10)')

Upvotes: 0

N_E
N_E

Reputation: 787

While i am still working on this, i am using

insert attribute xmlns {"http://www.ms.com"} into (/root)[1]

will throw the error

Cannot use 'xmlns' in the name expression of computed attribute constructor.

Reason for that is described in this article: Adding-xmlns-to-root-element

To Summarize it, xmlns shouldn't be changed in an existing XML construction because in the case of the below possible implementation from Microsoft Forum: SQL XML Namespace Issue suggesting that all existing child elements will have to changed also.

  <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   xmlns:ns1="uri">
     <ns1:ProductID>316</ns1:ProductID>
     <ns1:Name>Blade</ns1:Name>
     <ns1:Color xsi:nil="true" />
  </row>

Upvotes: 1

TT.
TT.

Reputation: 16137

Best alternative I could come up with, select root subnodes and place it between <root xmlns="http://OurNameSpace">...</root>.

DECLARE @t TABLE(e XML);
INSERT INTO @t(e)VALUES('<root><el1>Our Content</el1></root>');
INSERT INTO @t(e)VALUES('<root><el2>Our Content</el2></root>');
SELECT 
    '<root xmlns="http://OurNameSpace">'+
    CAST(e.query('/root/*') AS NVARCHAR(MAX))+
    '</root>'
FROM @t;

Closest I got with XQuery is this:

SELECT e.query('<root xmlns="http://OurNameSpace">{*:root/*}</root>') 
FROM @t;

But that selects the first subelement (eg <el1>) with xmlns="" (<el1 xmlns="">). I didn't find a way to remove that. But maybe that is good enough for you?

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Maybe as simple as this?

DECLARE @xml XML='<root>Our Content</root>';

SELECT CAST( REPLACE(CAST(@xml AS NVARCHAR(MAX)),'<root>','<root xmlns="http://OurNamespace">') AS XML)

Upvotes: 1

Related Questions