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

Question

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.

Answer 1

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)

Answer 2

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?

Answer 3

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>