Why I am right!

January 2, 2008

Updating XML columns in SQL Server 2005

Filed under: Microsoft, SQL, SQL Server 2005, Technology — Shaun @ 7:33 pm

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.

Advertisements

Blog at WordPress.com.