title: T-SQL XML : how to update value in one node if there are more nodes with identical names?
body: <p>I'm just starting to learn how to work with XML data format and I've already got stuck on updating some data. I would really, really appreciate some help with the issue 'cause i have absolutely no idea how to deal with such problem.</p>
  <p>Code to generate some sample data:</p>
  <pre><code>IF OBJECT_iD('tempdb..#beforeXML') is NOT NULL 
      DROP TABLE #beforeXML 
  
  CREATE TABLE #beforeXML 
  (
      ID int NOT NULL,    
      SomeXMLData XML NOT NULL
  )
  
  INSERT INTO #beforeXML (ID, SomeXMLData)
  VALUES 
      (1, '&lt;Parameters&gt;&lt;Parameter&gt;&lt;Key&gt;ABC&lt;/Key&gt;&lt;Value&gt;1, 2, 4&lt;/Value&gt;&lt;/Parameter&gt;&lt;Parameter&gt;&lt;Key&gt;XYZ&lt;/Key&gt;&lt;Value&gt;A&lt;/Value&gt;&lt;/Parameter&gt;&lt;/Parameters&gt;'),
      (2, '&lt;Parameters&gt;&lt;Parameter&gt;&lt;Key&gt;KLM&lt;/Key&gt;&lt;Value&gt;true&lt;/Value&gt;&lt;/Parameter&gt;&lt;Parameter&gt;&lt;Key&gt;ABC&lt;/Key&gt;&lt;Value&gt;1, 2, 4, 5&lt;/Value&gt;&lt;/Parameter&gt;&lt;/Parameters&gt;'),
      (3, '&lt;Parameters&gt;&lt;Parameter&gt;&lt;Key&gt;KLM&lt;/Key&gt;&lt;Value&gt;false&lt;/Value&gt;&lt;/Parameter&gt;&lt;Parameter&gt;&lt;Key&gt;ABC&lt;/Key&gt;&lt;Value&gt;1, 2, 3, 6&lt;/Value&gt;&lt;/Parameter&gt;&lt;Parameter&gt;&lt;Key&gt;XYZ&lt;/Key&gt;&lt;Value&gt;A, C&lt;/Value&gt;&lt;/Parameter&gt;&lt;/Parameters&gt;'),
      (4, '&lt;Parameters&gt;&lt;Parameter&gt;&lt;Key&gt;XYZ&lt;/Key&gt;&lt;Value&gt;A&lt;/Value&gt;&lt;/Parameter&gt;&lt;Parameter&gt;&lt;Key&gt;ABC&lt;/Key&gt;&lt;Value&gt;1, 2, 3, 5&lt;/Value&gt;&lt;/Parameter&gt;&lt;/Parameters&gt;'),
      (5, '&lt;Parameters&gt;&lt;Parameter&gt;&lt;Key&gt;XYZ&lt;/Key&gt;&lt;Value&gt;B&lt;/Value&gt;&lt;/Parameter&gt;&lt;Parameter&gt;&lt;Key&gt;KLM&lt;/Key&gt;&lt;Value&gt;true&lt;/Value&gt;&lt;/Parameter&gt;&lt;/Parameters&gt;')
  
  SELECT * FROM #beforeXML
  </code></pre>
  <p>Now comes the hard part...</p>
  <p>I need to update &quot;Value&quot; node's value where &quot;Key&quot; node's value = &quot;ABC&quot; within the same &quot;Parameter&quot; node.</p>
  <p>As you can see I have several &quot;Parameter&quot; nodes inside my xml, these nodes don't have specific order or attributes I could use to distinguish them and determine which ones should I update. There are some rows that don't have such node, and there are some rows that already have digits 3 or/and 5 inside &quot;Value&quot; node, so I need to add only in case one or both (3 or/and 5) is missing.</p>
  <p>The result I would like to obtain:</p>
  <pre><code>IF OBJECT_iD('tempdb..#afterXML') IS NOT NULL 
      DROP TABLE #afterXML 
  
  CREATE TABLE #afterXML 
  (
      ID int NOT NULL,    
      SomeXMLData XML NOT NULL
  )
  
  INSERT INTO #afterXML (ID, SomeXMLData)
  VALUES 
      -- added both 3, 5
      (1, '&lt;Parameters&gt;&lt;Parameter&gt;&lt;Key&gt;ABC&lt;/Key&gt;&lt;Value&gt;1, 2, 3, 4, 5&lt;/Value&gt;&lt;/Parameter&gt;&lt;Parameter&gt;&lt;Key&gt;XYZ&lt;/Key&gt;&lt;Value&gt;A&lt;/Value&gt;&lt;/Parameter&gt;&lt;/Parameters&gt;'),  
      -- added only 3
      (2, '&lt;Parameters&gt;&lt;Parameter&gt;&lt;Key&gt;KLM&lt;/Key&gt;&lt;Value&gt;true&lt;/Value&gt;&lt;/Parameter&gt;
  &lt;Parameter&gt;&lt;Key&gt;ABC&lt;/Key&gt;&lt;Value&gt;1, 2, 3, 4, 5&lt;/Value&gt;&lt;/Parameter&gt;&lt;/Parameters&gt;'),    
      -- added only 5
      (3, '&lt;Parameters&gt;&lt;Parameter&gt;&lt;Key&gt;KLM&lt;/Key&gt;&lt;Value&gt;false&lt;/Value&gt;&lt;/Parameter&gt;&lt;Parameter&gt;&lt;Key&gt;ABC&lt;/Key&gt;&lt;Value&gt;1, 2, 3, 5, 6&lt;/Value&gt;&lt;/Parameter&gt;&lt;Parameter&gt;&lt;Key&gt;XYZ&lt;/Key&gt;&lt;Value&gt;A, C&lt;/Value&gt;&lt;/Parameter&gt;&lt;/Parameters&gt;'),
      -- no change
      (4, '&lt;Parameters&gt;&lt;Parameter&gt;&lt;Key&gt;XYZ&lt;/Key&gt;&lt;Value&gt;A&lt;/Value&gt;&lt;/Parameter&gt;&lt;Parameter&gt;&lt;Key&gt;ABC&lt;/Key&gt;&lt;Value&gt;1, 2, 3, 5&lt;/Value&gt;&lt;/Parameter&gt;&lt;/Parameters&gt;'), 
      -- no change
      (5, '&lt;Parameters&gt;&lt;Parameter&gt;&lt;Key&gt;XYZ&lt;/Key&gt;&lt;Value&gt;B&lt;/Value&gt;&lt;/Parameter&gt;&lt;Parameter&gt;&lt;Key&gt;KLM&lt;/Key&gt;&lt;Value&gt;true&lt;/Value&gt;&lt;/Parameter&gt;&lt;/Parameters&gt;')  
  
  SELECT * FROM #afterXML
  </code></pre>
  <p>I've managed to extract values from that particular &quot;Value&quot; node for each row, check what digits are missing and prepare data for update</p>
  <p>So I have <code>temp_table</code> with data like this:</p>
  <pre><code>IF OBJECT_iD('tempdb..#temp_table') IS NOT NULL 
      DROP TABLE #temp_table
  
  CREATE TABLE #temp_table 
  (
      ID int NOT NULL,    
      NewSetOfValues varchar(100) NOT NULL
  )
  
  INSERT INTO #temp_table (ID, NewSetOfValues)
  VALUES 
      (1, '1, 2, 3, 4, 5'),
      (2, '1, 2, 3, 4, 5'),
      (3, '1, 2, 3, 5, 6')
   
  SELECT * FROM #temp_table
  </code></pre>
  <p>but that's where I've got stuck.</p>
  <p>I have absolutely no idea how to construct proper modify method syntax to update only specific &quot;Value&quot; node within presented xml structure... :(</p>
  <p>Is there some easy way to deal with such update?</p>
  <p>Thank you in advance for any help.</p>