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'
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:
But: The code above should work...
