Search code examples
sqlsql-serverxmlt-sqlsqlxml

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

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.

Solution

  • 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.