SQL Server insert XML node is adding namespace

Question

I have an existing XML column that I'd like to append a node to, but the node is being added with an unwanted namespace.

DECLARE @x XML =
'<Test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="abc">
  <Setting key="display" display="ABC" group="service" enabled="true">
    <Value xsi:type="xsd:string">ABC</Value>
  </Setting>
</Test>'

CREATE TABLE #C ( x XML )

INSERT INTO #C
SELECT @X

declare @name varchar(50) = 'ABC'
UPDATE #C
SET X.modify('insert <Setting key="about" display="About" group="service" enabled="true">
           <Value xsi:type="xsd:string">string of text about {sql:variable("@name")} here.</Value>
         </Setting> as last into (/Test[1])')

select * from #C

results in the new setting being added as...

<Setting key="about" display="About" group="service" enabled="true">
    <Value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:string">string of text about ABC here.</Value>
</Setting>

but what I really want is

    <Setting key="about" display="About" group="service" enabled="true">
        <Value xsi:type="xsd:string">string of text about ABC here.</Value>
    </Setting>

The result is correct. Why do you expect something different? — Remus Rusanu, May 02 '18 at 19:24
Read [`WITH XMLNAMESPACES`](https://learn.microsoft.com/en-us/sql/t-sql/xml/with-xmlnamespaces?view=sql-server-2017) — Remus Rusanu, May 02 '18 at 19:34

score 2 · Accepted Answer · answered May 02 '18 at 21:46

The way SQL-Server's XML engine deals with namespaces is - in some cases - quite annoying... It is important to say, that the above is not wrong... Repeating a namespace declaration is perfectly okay, but it's bloating the result, makes it difficult for humans to read and understand the XML and it can fail in (to) strict validators.

The reason is, that such XML is always complete. You can place it anywhere in the XML.

In your case you'd get the result the way you want it, by telling the engine in advance about the namespace:

implicit declaration

UPDATE #C
SET X.modify('declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
              insert <Setting key="about" display="About" group="service" enabled="true">
                         <Value xsi:type="xsd:string">string of text about {sql:variable("@name")} here.</Value>
                     </Setting> 
              as last into (/Test[1])');

or WITH XMLNAMESPACES():

WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi)
UPDATE #C
SET X.modify('insert <Setting key="about" display="About" group="service" enabled="true">
                         <Value xsi:type="xsd:string">string of text about {sql:variable("@name")} here.</Value>
                     </Setting> 
              as last into (/Test[1])');

SQL Server insert XML node is adding namespace

1 Answers1