How to modify multiple nodes using SQL XQuery in MS SQL 2005

Question

In an table I have the following.

ParameterID (int)    ParameterValue (XML)
------------    --------------
1               <USER><User ID="1" Name="Billy"/><USER>
                <USER><User ID="2" Name="Billy"/><USER>
                <MANAGER><User ID="1" Name="Billy"/><MANAGER>
2               <USER><User ID="1" Name="John"/><USER>
                <USER><User ID="2" Name="Billy"/><USER>
                <MANAGER><User ID="1" Name="Billy"/><MANAGER>
3               <USER><User ID="1" Name="David"/><USER>
                <USER><User ID="2" Name="Billy"/><USER>
                <MANAGER><User ID="1" Name="Billy"/><MANAGER>

How do I modify all instances of Billy to be Peter?

I tried

-- Update the table         
UPDATE  @tbXML
SET     ParameterValue.modify('replace value of (//User/@Name[.="Billy"])[1] with "Peter"')

But only the first Billy in the row was updated.

Running the update multiple times:

For Row 1: 
1st Time = 1 <USER><User ID="1" Name="Peter"/><USER> <USER><User ID="2" Name="Billy"/><USER> <MANAGER><User ID="1" Name="Billy"/><MANAGER> 

2nd Time 1 <USER><User ID="1" Name="Peter"/><USER> <USER><User ID="2" Name="Peter"/><USER> <MANAGER><User ID="1" Name="Billy"/><MANAGER> 

3rd Time 1 <USER><User ID="1" Name="Peter"/><USER> <USER><User ID="2" Name="Peter"/><USER> <MANAGER><User ID="1" Name="Peter"/><MANAGER>

marc_s · Accepted Answer · 2009-08-05 11:37:15Z

1

The "simple" approach would be to cast the XML column to VARCHAR(MAX) and just simply do a REPLACE on it:

UPDATE
  YourTable
SET
  ParameterValue = CAST(REPLACE(CAST(ParameterValue AS VARCHAR(MAX)), '
                                Billy', 'Peter') AS XML)
WHERE
  ....

It almost seems like you cannot do an update of multiple XML node values in a single UPDATE statement, as Richard Szalay explains here:

Unfortunately, it appears that the implementation is horribly limited in that it cannot make an arbitrary number of modifications to the same value in a single update.

So I guess you'll either have to use the "dumb" VARCHAR(MAX) approach mentioned above, or do the update in a loop (WHILE you still find a node with "billy", UPDATE that node to read "Peter" instead).

Marc

edited Aug 5, 2009 at 11:37

answered Aug 5, 2009 at 11:23

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gribbler Over a year ago

I was hoping to use the XQuery approach incase we add further attributes to the XML, if I had something like <User><User Name="Billy" NickName="Billy"/></User> then i might not want to rename the nickname attribute.

marc_s Over a year ago

I'm trying to figure that out - what if you run your update statement multiple times? Does it update the rows one by one?

Gribbler Over a year ago

For Row 1: 1st Time = 1 <USER><User ID="1" Name="Peter"/><USER> <USER><User ID="2" Name="Billy"/><USER> <MANAGER><User ID="1" Name="Billy"/><MANAGER> 2nd Time 1 <USER><User ID="1" Name="Peter"/><USER> <USER><User ID="2" Name="Peter"/><USER> <MANAGER><User ID="1" Name="Billy"/><MANAGER> 3rd Time 1 <USER><User ID="1" Name="Peter"/><USER> <USER><User ID="2" Name="Peter"/><USER> <MANAGER><User ID="1" Name="Peter"/><MANAGER>

marc_s Over a year ago

So running the update multiple times also solves the problem - not perfect and not very slick and neat, but it works, right?

Gribbler Over a year ago

Yeah it works, I'll have to use that somehow.. grr! Thanks for the suggestion!

Gribbler · Accepted Answer · 2009-08-06 14:08:51Z

0

I ended up using a cursor to do the update

Used the update in marcs answer in the cursor in a while loop

answered Aug 6, 2009 at 14:08

Gribbler

4342 gold badges7 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to modify multiple nodes using SQL XQuery in MS SQL 2005

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related