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

17 Comments »

  1. How to change the value of NodeTwo and NodeThree at the same time?

    Comment by Karthikeyan — January 13, 2008 @ 12:11 pm

  2. I wasn’t able to find any way to update multiple nodes.

    My example above was for a one time update so I didn’t have to worry about performance. If it were me I would just run the above script once for each node.

    Comment by Shaun — January 13, 2008 @ 4:41 pm

  3. […] can now update the timestamp value by taking the first 19 characters and appending ‘Z’ using UPDATE .modify. The result is calculated using XQuery […]

    Pingback by Handling XSD dateTime in SQL Server 2005 « devioblog — November 27, 2008 @ 7:18 pm

  4. Thanx for a very clear and hands on explanation!

    Comment by Klas Minnbergh — May 28, 2010 @ 7:07 pm

    • How do you create a xml from a query where lets say

      Value = Value + NewValue

      declare @id int, @xml_text varchar(8000) select
      @xml_text = ”
      exec sp_xml_preparedocument @id OUTPUT, @xml_text Select * from OPENXML(@ID, ‘UPDATE_TABLE/TABLE’)
      WITH TABLEexec sp_xml_removedocument @id

      Comment by Krokenoster — June 17, 2010 @ 6:36 pm

  5. Thank you for sharing your knowledge.

    I have small addition to share.
    say if you “NodeThree” have childs and you need to get to it you can do this:

    UPDATE [Product]
    SET ProductXml.modify(‘replace value of (/Sample/NodeThree/SubNodeThree[sql:variable(“@variable”)]/Name/text())[1] with sql:variable(“@newName”)’)

    Comment by Ben — August 13, 2010 @ 3:33 am

  6. good work , saved me a lots of time 🙂

    Comment by karan — February 4, 2011 @ 3:17 am

  7. Thanks for the simple example! I hadn’t done this before, and it only took 5 minutes to make my modifications after reading this.

    Comment by Dan — May 26, 2011 @ 12:11 am

  8. Good one and useful.

    Comment by shan — November 29, 2011 @ 12:08 am

  9. Thanks for this – however when I run it I get a “Cannot call methods on nvarchar(max)” error. Is there a cast I can make? In the example above I couldn’t tell what the schema definition was on the ProductXml column.

    Comment by Tom — December 19, 2011 @ 2:01 am

    • It is probably because your source column is of type nvarchar(max) and not xml. You need to CAST it before. Not sure how to do it for a modify method but for value method, it would look like this:
      SELECT CAST(ProductXml AS xml).value(‘(/Sample/NodeThree)[1]’, varchar(max))

      Comment by Phil — January 23, 2012 @ 9:57 pm

      • Sorry, forgot to single quote the data type:
        SELECT CAST(ProductXml AS xml).value(‘(/Sample/NodeThree)[1]‘, ‘varchar(max)’)

        Comment by Phil — January 23, 2012 @ 10:00 pm

  10. Hi, Thanks for this,

    but i am getting “Cannot call methods on text”.Can anyone help me out from this?

    Comment by DP — January 9, 2012 @ 5:29 pm

  11. its great with good explanation with 3 Attempts..it worked pretty fast..keep it up. Thanks alot.

    Comment by Madhu — April 21, 2012 @ 1:43 am

  12. Thanks very much, this helped a lot

    Comment by Sudhir — May 25, 2012 @ 1:50 pm

  13. Howdy just wanted to give you a brief heads up and let you know a
    few of the pictures aren’t loading correctly. I’m not sure why but I think its a linking issue.
    I’ve tried it in two different browsers and both show the same results.

    Comment by intermittent fasting weight — September 13, 2013 @ 8:24 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: