I had a table with a large XML column containing hundreds of rows. I needed to update the value of a single node in some of those records. Here are the steps I used to do it and the errors I encountered along the way. Hopefully this will save someone else some time. I cannot use my actual code for a number of reasons so I will attempt to simplify it for this example.
Assume you have a XML format like this:
<Sample>
<NodeOne>Value1</NodeOne>
<NodeTwo>Value2</NodeTwo>
<NodeThree>OldValue</NodeThree>
</Sample>
We want to replace ‘OldValue’ in NodeThree with ‘NewValue’.
Attempt 1 (Invalid):
DECLARE @newValue varchar(50)
SELECT @newValue = 'NewValue'
UPDATE [Product]
SET ProductXml.modify('replace value of (/Sample/NodeThree)[1] with '+ @newValue)
Since this script would be run a number of times with a different parameter I needed a way to change the ‘NewValue’ without rewriting the script. I was hoping to use String concatenation but it does not work.
Msg 8172, Level 16, State 1, Line 5
The argument 1 of the xml data type method “modify” must be a string literal.
Attempt 2 (Invalid):
DECLARE @newValue varchar(50)
SELECT @newValue = 'NewValue'
UPDATE [Product]
SET ProductXml.modify('replace value of (/Sample/NodeThree)[1] with sql:variable("@newValue")')
To fix the above error I had to use the sql:variable syntax. as you can see this simple replaces the string concatenation with sql:variable(“@newValue”). I thought I was home free but there was one more problem.
Msg 2356, Level 16, State 1, Line 6
XQuery [Product.ProductXml.modify()]: The target of ‘replace value of’ must be a non-metadata attribute or an element with simple typed content, found ‘element(NodeThree,xdt:untyped) ?’
Attempt 3 (Valid):
DECLARE @newValue varchar(50)
SELECT @newValue = 'NewValue'
UPDATE [Product]
SET ProductXml.modify('replace value of (/Sample/NodeThree/text())[1] with sql:variable("@newValue")')
What I really wanted to do was replace the text of the NodeThree element. To do that I had to add /text() to the end of the XPath. This script will replace the text of the specified element with whatever value is in the @newValue parameter. Thanks to this thread for the answer.
Disclaimer:
As far as I know this only works on un-typed XML columns. If you have a typed column I believe you need to add a couple things to this statement but hopefully this will point you in the right direction.
During testing I wrapped my entire script in a BEGIN TRANSACTION/ROLLBACK TRANSACTION so I could see the results of my script without doing any damage. You should do at least that before attempting any updates as the XPath is generally tricky.
Why I am right! 