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>