I have a Table with an XML column, I want to update the xml to insert attribute or to change the attribute value if the attribute already exists.
Let's say the starting xml is: < d />
Inserting:
UPDATE Table
set XmlCol.modify('insert attribute att {"1"} into /d[1]')
Changing:
UPDATE Table
set XmlCol.modify('replace value of /d[1]/@att with "1"')
insert will fail if the attribute already exists, replace will fail if the attribute doesn't exists. I have tried to use 'if' but I don't think it can work, there error I get: "XQuery [modify()]: Syntax error near 'attribute', expected 'else'."
IF attempt
UPDATE Table
set XmlCol.modify('if empty(/d[1]/@att)
then insert attribute att {"1"} into /d[1]
else replace value of /d[1]/@att with "1"')
Currently I select the xml into a variable and then modify it using T-SQL and then updating the column with new xml, this requires me to lock the row in a transaction and is probably more expensive for the DB.