title: Updating multiple XML nodes using T-SQL
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/7327440/tsql-2005-xml-dml-update-two-values-at-once">TSQL 2005, XML DML - Update Two Values at once?</a>  </p>
  </blockquote>
  
  
  
  <p>Say I have a database table with an XML column.</p>
  
  <p>For each row, the XML is structured the same (or similarly) but the content of the tags is different.</p>
  
  <p>The structure looks something like this:</p>
  
  <pre><code>&lt;Parent1&gt;
    &lt;Parent2&gt;
      &lt;Child&gt;
         test 1
      &lt;/Child&gt;
    &lt;/Parent2&gt;
    &lt;Parent2&gt;
      &lt;Child&gt;
         test 2
      &lt;/Child&gt;
    &lt;/Parent2&gt;
  &lt;/Parent1&gt;
  </code></pre>
  
  <p>I want to do some processing on each 'Child' node in the structure and update them with the results.</p>
  
  <p>For example, I want to replace the word 'test' with the word 'something'.</p>
  
  <p>So I don't want both child nodes to end up containing 'something 1'.</p>
  
  <p>Rather, I want the first child node to contain 'something 1' and the second child node to contain 'something 2'.</p>
  
  <p>I could write the following, to target a <em>single</em> node at a time:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But is it possible to write a T-SQL statement that will process <em>multiple</em> nodes, matched by an XPath, and update each of them individually, according to, say, the result of a function?</p>