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

Question

In the 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 change 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 line was updated.

Performing the update several 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>

Answer 1

A “simple” approach would be to put the XML column in VARCHAR (MAX) and just do REPLACE on it:

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

It looks like you cannot update multiple XML node values ​​in a single UPDATE statement, as Richard Salay explains here

, , , .

, , "" VARCHAR (MAX), , (WHILE node "billy", UPDATE, node "" ).

Answer 2

marcs while