Inserting an attribute in multiple XML Nodes using XML.modify() in SQL 2005

Question

I have an @XML document created from a single select statement.

<root>
 <node>
  <node1>
   <targetNode>
   </targetNode>
  </node1>
  <node1>
   <targetNode>
   </targetNode>
  </node1>
  <node1>
   <targetNode>
   </targetNode>
  </node1>
 </node>
 <node>
  ......
 </node>
</root>

I want to insert the xsi:nil as an attribute of 'targetNode' for this document.

@XML.modify( 'insert attribute xsi:nil {"true"} into (root/node/node1/targetNode) [1]') 

The above will insert the attribute into the first occurance of the targetNode in the @XML document. The insert statement however will only work on a single node. Is there any way I can insert this attribute into all instances of targetNode in the @XML document.

Answer 1

I found a simple and elegant solution in DML operations on multiple nodes http://blogs.msdn.com/b/denisruc/archive/2005/09/19/471562.aspx

The idea is to count how many nodes and modify them one by one:

DECLARE @iCount int
SET @iCount = @var.value('count(root/node/node1/targetNode)','int')

DECLARE @i int
SET @i = 1

WHILE (@i <= @iCount)
BEGIN
   @xml.modify('insert attribute xsi:nil {"true"} into (root/node/node1/targetNode)[sql:variable("@i")][1]')
   SET @i = @i + 1
END