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

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.

From stackoverflow

• That's not possible with the modify-function. It only works on a single node.

  You can manipulate it as string, although that is definitely ugly and possibly wrong in some cases, depending on the actual structure of your XML.

  Like this:

  declare @xml as xml
  set @xml = '<root>
   <node>
    <node1>
     <targetNode>
     </targetNode>
    </node1>
    <node1>
     <targetNode>
     </targetNode>
    </node1>
    <node1>
     <targetNode>
     </targetNode>
    </node1>
   </node>
  </root>
  '
  
  set @xml = replace(cast(@xml as nvarchar(max)), '<targetNode/>', '<targetNode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />')
  select @xml
  

• you can do this in the select, that you are using to create your xml, using the XSINILL parameter.

  http://msdn.microsoft.com/en-us/library/ms178079.aspx

  (here is a very rough example)

  --create 2 tables and put some data in them
  create table node
  (
     id int identity(1,1) primary key,
     node int
  )
  GO
  create table node1
  (
     id int identity(1,1) primary key,
     nodeid int foreign key references node(id),
     targetnode int
  )
  GO
  
  insert into node
  select 1
  GO 5
  
  insert into node1
  select 1,2
  union 
  select 2,null
  union 
  select 3,2
  union 
  select 4,null
  --
  
  --select statement to generate the xml
  SELECT TOP(1)
     (SELECT
        (  SELECT targetnode
           FROM    node1
           WHERE   nodeid = node.id 
           FOR XML AUTO,
           ELEMENTS XSINIL,
           TYPE
        )
     FROM    node FOR XML AUTO,
     ELEMENTS,
     TYPE
     )
  FROM   node FOR XML RAW('root'),
         ELEMENTS