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:


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.


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.



  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: