title: SQL Server XML-DML how to replace element value with value of relative xpath
body: <p>I have an xml column with the following data:</p>
  
  <pre><code>&lt;Policy&gt;
    &lt;Name&gt;
      &lt;Id&gt;adf&lt;/Id&gt;
      &lt;First&gt;Alan&lt;/First&gt;
      &lt;Last&gt;Turing&lt;/Last&gt;
    &lt;/Name&gt;
    &lt;Name&gt;
      &lt;Id&gt;asdf&lt;/Id&gt;
      &lt;Business&gt;Vandelay Industries&lt;/Business&gt;
    &lt;/Name&gt;
    &lt;Name&gt;
      &lt;Id&gt;asdf&lt;/Id&gt;
      &lt;First&gt;Dennis&lt;/First&gt;
      &lt;Last&gt;Ritchie&lt;/Last&gt;
    &lt;/Name&gt;
  &lt;/Policy&gt;
  </code></pre>
  
  <p>How would I write an XML-DML query that will replace the value of the Id element for each Name across all rows so it contains the value of First, Last, and Business. For example:</p>
  
  <pre><code>&lt;Policy&gt;
    &lt;Name&gt;
      &lt;Id&gt;AlanTuring&lt;/Id&gt;
      &lt;First&gt;Alan&lt;/First&gt;
      &lt;Last&gt;Turing&lt;/Last&gt;
    &lt;/Name&gt;
    &lt;Name&gt;
      &lt;Id&gt;Vandelay Industries&lt;/Id&gt;
      &lt;Business&gt;Vandelay Industries&lt;/Business&gt;
    &lt;/Name&gt;
    &lt;Name&gt;
      &lt;Id&gt;DennisRitchie&lt;/Id&gt;
      &lt;First&gt;Dennis&lt;/First&gt;
      &lt;Last&gt;Ritchie&lt;/Last&gt;
    &lt;/Name&gt;
  &lt;/Policy&gt;
  </code></pre>
  
  <p>Here's my failed attempt:</p>
  
  <pre><code>update policy set data.modify('
    replace value of (//Name/Id/text())[1] 
    with concat(
      (//Name/First/text())[1], 
      (//Name/Last/text())[1], 
      (//Name/Business/text())[1])') 
  </code></pre>
  
  <p>Which produces the following:</p>
  
  <pre><code>&lt;Policy&gt;
    &lt;Name&gt;
      &lt;Id&gt;AlanTuringVandelay Industries&lt;/Id&gt;
      &lt;First&gt;Alan&lt;/First&gt;
      &lt;Last&gt;Turing&lt;/Last&gt;
    &lt;/Name&gt;
    &lt;Name&gt;
      &lt;Id&gt;asdf&lt;/Id&gt;
      &lt;Business&gt;Vandelay Industries&lt;/Business&gt;
    &lt;/Name&gt;
    &lt;Name&gt;
      &lt;Id&gt;asdf&lt;/Id&gt;
      &lt;First&gt;Dennis&lt;/First&gt;
      &lt;Last&gt;Ritchie&lt;/Last&gt;
    &lt;/Name&gt;
  &lt;/Policy&gt;
  </code></pre>