[SOLVED] Insert XML element with attribute into SQL XML column

Insert XML element with attribute into SQL XML column

I have the following XML data stored in [MyTable].Column1 which is an XML type column.

I want to insert a new element to it:

I'm using the following SQL, it executed successfully but when I query the same column value again, I do not see my new XML element. Do I miss certain syntax for inserting an element with attributes?

UPDATE [MyTable] 
SET Column1.modify('insert <Setting Name="H" Value="0"/> as last into (/SettingValues)[1]')
WHERE ID = 'xxxxx'

Solution

Your code - on the first sight - should work. But you obviously modified it to fit to this forum.

DECLARE @myTable TABLE(ID VARCHAR(100),Column1 XML)
INSERT INTO @myTable VALUES
 ('111'
  ,'<SettingValues>
     <Setting Name="A-name" Value="A-value"/>
   </SettingValues>')
, ('222'
  ,'<SettingValues>
     <Setting Name="A-name" Value="A-value"/>
   </SettingValues>');

UPDATE @MyTable 
SET Column1.modify('insert <Setting Name="H" Value="0"/> as last into (/SettingValues)[1]')
WHERE ID = '222';

SELECT * 
FROM @myTable

This works as expected.

ID  Column1
111 <SettingValues><Setting Name="A-name" Value="A-value" /></SettingValues>
222 <SettingValues><Setting Name="A-name" Value="A-value" /><Setting Name="H" Value="0" /></SettingValues>

After execution you see "1 row affected".

Some ideas:

The filter is not fullfilled
The given XML declares namespaces but never uses them... This is a bit odd. Have you reduced the namespaces and your original XML includes a default namespace (or the elements are prefixed somehow)?
You are checking against the wrong target (other server, other schema, other table...

But: The code above should work...