How to insert xml node in Sql Server without inserting empty namespace?

Question

This is an example of how my source xml looks

<Catalog xmlns="http://schemas.example.com/stuff/stuff">
  <String Key="Name" Tag="22a41320-bb66-41a9-8806-760d13679c6c">Document Title 1</String>
  <String Key="Name" Tag="023463cf-9237-45b6-ac3f-621b9b09f609">Title for document 2</String>
</Catalog>

I plan to loop through the String nodes and transform them into Document nodes (this only shows the first iteration of the loop). However, when I insert the new node, it inserts an empty namespace. This is the result I get:

<Catalog xmlns="http://schemas.example.com/stuff/stuff">
  <String Key="Name" Tag="023463cf-9237-45b6-ac3f-621b9b09f609">Title for document 2</String>
  <Document xmlns="" Key="Document Title 1" Handle="22a41320-bb66-41a9-8806-760d13679c6c" />
</Catalog>

Notice the empty namespace. I want to omit the namespace on the Document node entirely.

Here's the result I want

<Catalog xmlns="http://schemas.example.com/stuff/stuff">
  <String Key="Name" Tag="023463cf-9237-45b6-ac3f-621b9b09f609">Title for document 2</String>
  <Document Key="Document Title 1" Handle="22a41320-bb66-41a9-8806-760d13679c6c" />
</Catalog>

Here is a full query you can play with:

declare @temp xml, @newNode xml;

set @temp = cast(
'<Catalog xmlns="http://schemas.example.com/stuff/stuff">
  <String Key="Name" Tag="22a41320-bb66-41a9-8806-760d13679c6c">Document Title 1</String>
  <String Key="Name" Tag="023463cf-9237-45b6-ac3f-621b9b09f609">Title for document 2</String>
</Catalog>' as xml)

select 'before', @temp

set @newNode = CAST(
  '<Document Key="' + @temp.value('declare default element namespace "http://schemas.example.com/stuff/stuff"; (/Catalog/String/text())[1]', 'varchar(max)') + 
  '" Handle="' + @temp.value('declare default element namespace "http://schemas.example.com/stuff/stuff"; (/Catalog/String/@Tag)[1]', 'varchar(50)') + '"  />' 
as xml)

set @temp.modify('declare default element namespace "http://schemas.example.com/stuff/stuff"; insert sql:variable("@newNode") into (/Catalog)[1] ')
set @temp.modify('declare default element namespace "http://schemas.example.com/stuff/stuff"; delete (/Catalog/String)[1]')

select 'after', @temp

score 2 · Accepted Answer · answered Apr 29 '14 at 15:26

I tried various methods to get around this

using with xmlnamespaces: no change
using explicitly defined namespaces: no change
using the same namespace as the parent: results in the default namespace being inserted into the Document node
deleting the empty namespace with modify/delete: would not remove the xmlns attribute
inserting the values dynamically inside the modify/insert : "The argument 1 of the XML data type method "modify" must be a string literal."

Solution

So the last error got me thinking, it will insert the node I want with no namespace so long as it's a string literal.. So I did just that.

Insert empty node with empty attributes
Use modify/replace to fill in the values of the attributes after insertion

And here's the example of what it looks like

declare @temp xml

set @temp = cast(
'<Catalog xmlns="http://schemas.example.com/stuff/stuff">
  <String Key="Name" Tag="22a41320-bb66-41a9-8806-760d13679c6c">Document Title 1</String>
  <String Key="Name" Tag="023463cf-9237-45b6-ac3f-621b9b09f609">Title for document 2</String>
</Catalog>' as xml)

select 'before', @temp

while (@temp.value('declare default element namespace "http://schemas.example.com/stuff/stuff"; count(/Catalog/String)', 'int') > 0)
begin
SET @temp.modify('declare default element namespace "http://schemas.example.com/stuff/stuff"; insert <Document Key="" Handle="" /> into (/Catalog)[1] ')
SET @temp.modify('declare default element namespace "http://schemas.example.com/stuff/stuff"; replace value of (/Catalog/Document[@Handle=""]/@Handle)[1] with (/Catalog/String/@Tag)[1]')
SET @temp.modify('declare default element namespace "http://schemas.example.com/stuff/stuff"; replace value of (/Catalog/Document[@Key=""]/@Key)[1] with (/Catalog/String/text())[1]')
SET @temp.modify('declare default element namespace "http://schemas.example.com/stuff/stuff"; delete (/Catalog/String)[1]')
end

select 'after', @temp

score 0 · Answer 2 · answered Apr 28 '14 at 20:38

0

Instead of looping and using modify with INSERT and DELETE, why just not replace the desire nodes:

declare @temp xml, @newNode xml;

set @temp = cast(
'<Catalog xmlns="http://schemas.example.com/stuff/stuff">
  <String Key="Name" Tag="22a41320-bb66-41a9-8806-760d13679c6c">Document Title 1</String>
  <String Key="Name" Tag="023463cf-9237-45b6-ac3f-621b9b09f609">Title for document 2</String>
</Catalog>' as xml)

SELECT CAST(REPLACE(CAST(@temp AS NVARCHAR(MAX)), 'String', 'Catalog') AS XML)

answered Apr 28 '14 at 20:38

gotqn

42,737
46
157
243

There's more to it than changing the name of the node, I'm also changing the name of an attribute and creating a new attribute based on the text content of the String node. Plus, doing a text replace on xml is somewhat bad form. In my example, if the word "String" was part of the title, then it would also modify the title which we don't want. – Jason L. Apr 28 '14 at 20:42

score 0 · Answer 3 · answered Nov 08 '16 at 17:28

0

This replace works to remove the empty namespace attribute xmlns="" after you do the modify() insert

UPDATE dbo.TableName
SET TableXmlColumn = CONVERT(XML, REPLACE(CONVERT(NVARCHAR(MAX), TableXmlColumn), N'xmlns=""',''))

answered Nov 08 '16 at 17:28

iambdot

887
2
10
28

How to insert xml node in Sql Server without inserting empty namespace?

3 Answers3