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

Create a free website or blog at WordPress.com.