title: Update XML node value in SQL Server
body: <p>I need to update the GroupID field to a 0.  I have figured out how to retrieve the value, I am now running into problems updating it.  </p>
  
  <p>Any help would ge great!</p>
  
  <pre><code>&lt;ProblemProfile&gt;
    &lt;GroupID&gt;-1&lt;/GroupID&gt;
    &lt;LayoutID&gt;1&lt;/LayoutID&gt;
    &lt;MyQueries&gt;false&lt;/MyQueries&gt;
  &lt;/ProblemProfile&gt;
  
  Declare @Result xml
  set @Result = convert(xml,(select ProfileXML from profiles where id = 23))
  
  SELECT x.value('.', 'int') ID
  FROM @Result.nodes('/ProblemProfile/GroupID') as R(x)
  </code></pre>
  
  <p><strong>Update</strong></p>
  
  <p>What I need to do now is update every single row's GroupID that has the value of 'foo'</p>
  
  <pre><code>declare @foo int
  set @foo = -1
  
  UPDATE  profiles
  SET  ProfileXML.modify('replace value of (/ProblemProfile/GroupID/text())[1] with "0"')
  WHERE  ProfileXML.value('(/ProblemProfile/GroupID)[1]', 'int') = @foo
  </code></pre>
  
  <p>This is only updating the first row that meets this criteria.  How would I update every row?</p>
  
  <p><strong><em>Update 2</em></strong>
  That statement works.  Turns out the database structure for the first node can be different.  A simple //GroupID...etc updated every row.   It is always the stupid little things that trip us up haha.</p>