Search code examples
sqlsql-serverxmlxml-namespaces

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.

Solution

  • 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)