TSQL XML Manipulation

How to change XML data via TSQL

Modifying XML data via TQL can be done using the following .modify commands...

To insert a new node/attribute...

UPDATE MyTable SET MyXml.modify('insert <item>new</item> as first into (/root)[1]')
UPDATE MyTable SET MyXml.modify('insert <item>new</item> as last into (/root)[1]')
UPDATE MyTable SET MyXml.modify('insert <item>new</item> after (/root/item[1])[1]')
UPDATE MyTable SET MyXml.modify('insert <item>new</item> before (/root/item[1])[1]')
UPDATE MyTable SET MyXml.modify('insert attribute attr {"new"} into (/root/item[1])[1]')

To change text/attribute in a node...

UPDATE MyTable SET MyXml.modify('replace value of (/root/item[1]/text())[1] with "new"')
UPDATE MyTable SET MyXml.modify('replace value of (/root/item[1]/@attr)[1] with "new"')

To delete a node/attribute/text...

UPDATE MyTable SET MyXml.modify('delete /root/item[1]')
UPDATE MyTable SET MyXml.modify('delete /root/item[1]/@attr')
UPDATE MyTable SET MyXml.modify('delete /root/item[1]/text()')