title: Update multiple XML elements from SQL Variables
body: <p>I need to update a value in an XML element with the contents of a TSQL variable. The added complication is that there can be multiple elements with the same name and all elements need to get updated. Here is a sample. Note that customer 1000000 has two customer_firstname elements.</p>
  
  <pre><code>CREATE TABLE Customer_Test
  (
      [customer_data] [xml] NULL
  ) 
  
  -- populate statements
  insert into Customer_Test (customer_data) values ('&lt;Customer Note="two customer_firstnames"&gt;&lt;customer_id&gt;1000000&lt;/customer_id&gt;&lt;customer_firstname&gt;Mary&lt;/customer_firstname&gt;&lt;customer_firstname&gt;Jane&lt;/customer_firstname&gt;&lt;customer_lastname&gt;Smith&lt;/customer_lastname&gt;&lt;/Customer&gt;');
  insert into Customer_Test (customer_data) values ('&lt;Customer Note="normal, no problem"&gt;&lt;customer_id&gt;1000001&lt;/customer_id&gt;&lt;customer_firstname&gt;Joe&lt;/customer_firstname&gt;&lt;customer_lastname&gt;Bloggs&lt;/customer_lastname&gt;&lt;/Customer&gt;');
  </code></pre>
  
  <p>The code below works just fine on xml structured like that in the customer_ID = '1000001' record but only the first customer_firstname element gets updated for situations like customer_ID = '1000000' </p>
  
  <pre><code>DECLARE @newName varchar(10) = 'xxx'
  DECLARE @IDValue varchar(10) = '1000000'
  UPDATE  [Customer_Test] 
  SET  customer_data.modify('replace value of (Customer/customer_firstname/text())[1] with sql:variable("@newName")') 
  WHERE  customer_data.value('(/Customer/customer_id)[1]','varchar(50)') = @IDValue
  </code></pre>
  
  <p>I am really stuck on this - I need all values of the customer_firstname element to be set to the same value if they are present. I half suspect a CROSS APPLY is required but all my attempts to code one would not compile.</p>
  
  <p>I would very much value any advice which might be provided. Thanks in advance.</p>