title: Update SQL server xml column using XQUERY?
body: <p>I have a simple xml in a XML column</p>
  <pre><code>&lt;Bands&gt;
      &lt;Band&gt;
          &lt;Name&gt;beatles&lt;/Name&gt;
          &lt;Num&gt;4&lt;/Num&gt;
          &lt;Score&gt;5&lt;/Score&gt;
      &lt;/Band&gt;
      &lt;Band&gt;
          &lt;Name&gt;doors&lt;/Name&gt;
          &lt;Num&gt;4&lt;/Num&gt;
          &lt;Score&gt;3&lt;/Score&gt;
      &lt;/Band&gt;
  &lt;/Bands&gt;
  </code></pre>
  <p>I have managed to update the column with :</p>
  <pre><code>   -----just update the name to the id)----
     UPDATE tbl1
     SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
     with sql:column(&quot;id&quot;)')
  </code></pre>
  <p>All fine.</p>
  <p><strong>Question #1</strong></p>
  <p>How can I use this query to udpate the value to <code>id+&quot;lalala&quot;</code>:</p>
  <pre><code>   UPDATE tbl1
     SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
     with sql:column(&quot;id&quot;) + &quot;lalala&quot;')
  </code></pre>
  <p><em>Error  = XQuery [tbl1.myXml.modify()]: The argument of '+' must be of a single numeric primitive type</em></p>
  <p><strong>Question #1</strong></p>
  <p>Let's say I Don't want to update first record (<code>[1]</code>)  , But I want to <code>udpate</code> (the same update as above) only where <code>score&gt;4</code>.</p>
  <p>I can write ofcourse in the xpath :</p>
  <p><code>replace value of (/Bands/Band[Score&gt;4]/Name/text())[1]</code></p>
  <p>But I dont want to do it in the Xpath. Isn't there a Normal way of doing this with a <code>Where</code> clause ?</p>
  <p>something like :</p>
  <pre><code>   UPDATE tbl1
     SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
     with sql:column(&quot;id&quot;)  where [...score&gt;4...]')
  </code></pre>
  <p><a href="https://data.stackexchange.com/stackoverflow/query/edit/87215" rel="nofollow noreferrer"><em><strong>here is the online sql</strong></em></a></p>