Updating multiple XML nodes using T-SQL [duplicate

Question

This question already has answers here:

Closed 8 years ago.

Possible Duplicate:
TSQL 2005, XML DML - Update Two Values at once?

Say I have a database table with an XML column.

For each row, the XML is structured the same (or similarly) but the content of the tags is different.

The structure looks something like this:

<Parent1>
  <Parent2>
    <Child>
       test 1
    </Child>
  </Parent2>
  <Parent2>
    <Child>
       test 2
    </Child>
  </Parent2>
</Parent1>

I want to do some processing on each 'Child' node in the structure and update them with the results.

For example, I want to replace the word 'test' with the word 'something'.

So I don't want both child nodes to end up containing 'something 1'.

Rather, I want the first child node to contain 'something 1' and the second child node to contain 'something 2'.

I could write the following, to target a single node at a time:

DECLARE @replacement NVARCHAR(4000) = 'something 1'
UPDATE MyTable
SET MyXMLField.modify('replace value of (Parent1[1]/Parent2[1]/Child[1]/text())[1] with sql:variable("@replacement")')
WHERE Id = 1

But is it possible to write a T-SQL statement that will process multiple nodes, matched by an XPath, and update each of them individually, according to, say, the result of a function?

Answer 1

You have to do it in a loop, one node at a time.

declare @MyTable table(Id int, MyXMLField xml)
insert into @MyTable values
(1, '<Parent1>
       <Parent2>
         <Child>test 1</Child>
       </Parent2>
       <Parent2>
         <Child>test 2</Child>
       </Parent2>
     </Parent1>')

declare @replacement nvarchar(4000) = 'something 1'
declare @Parent2Count int
select @Parent2Count = MyXMLField.value('count(/Parent1/Parent2)', 'int')
from @MyTable
where Id = 1

declare @Node int
set @Node = 1

while @Node <= @Parent2Count
begin
  update @MyTable set
    MyXMLField.modify('replace value of 
                          (/Parent1/Parent2[sql:variable("@Node")]/Child/text())[1] 
                          with sql:variable("@replacement")')
  where Id = 1    

  set @Node = @Node + 1
end