SQL query update nvarchar with XML contents add missing attribute to node

Question

I have an nvarchar column with XML contents. Not sure if it needs to be cast to XML or not. All the XML content is formatted the same except a number of records need to be modified where the first node needs an attribute added to it:

<MyFirstNode SomeAttribute="value" SomeOtherAttribute="anothervalue">

update to:

<MyFirstNode SomeAttribute="value" AddThisAttribute="nicevalue" SomeOtherAttribute="anothervalue">

How can i update all the required nodes with the AddThisAttribute="nicevalue" ? All need the same attribute and value.

Answer 1

If this is actually going to be interpreted as XML, you don't neccessarily have to insert AddThisAttribute="nicevalue" in the middle of the list of attributes; one option is to do a simple

UPDATE myTable
SET XMLColumn = REPLACE(XMLColumn, '<MyFirstNode ', '<MyFirstNode AddThisAttribute="nicevalue" ')

This might be easier than trying to insert the value, especially if SomeAttribute and SomeOtherAttribute change in each row.

Alternately,

UPDATE myTable
SET XMLColumn = REPLACE(XMLColumn, ' SomeOtherAttribute=', ' AddThisAttribute="nicevalue"  SomeOtherAttribute=')

could work, though if "SomeOtherAttribute" appears in other node types other than MyFirstNode, this might make undesired changes.