title: How to update XML node using XML DML in SQL Server
body: <p>I want to update a XML variable in SQL Server 2012.</p>
  
  <p>Sample XML:</p>
  
  <pre><code>&lt;ArrayOfFieldInfo&gt;
      &lt;FieldInfo&gt;
          &lt;Name&gt;abc&lt;/Name&gt;
          &lt;FriendlyName&gt;friend2&lt;/FriendlyName&gt;
          &lt;Description&gt;Custom added field&lt;/Description&gt;
          &lt;FieldGroupName&gt;Custom Fields&lt;/FieldGroupName&gt;
      &lt;/FieldInfo&gt;
      &lt;FieldInfo&gt;
          &lt;Name&gt;efg&lt;/Name&gt;
          &lt;FriendlyName&gt;friend1&lt;/FriendlyName&gt;
          &lt;Description&gt;Custom added field&lt;/Description&gt;
          &lt;FieldGroupName&gt;Custom Fields&lt;/FieldGroupName&gt;
      &lt;/FieldInfo&gt;
  &lt;/ArrayOfFieldInfo&gt;
  </code></pre>
  
  <p>I want to update node <code>&lt;FriendlyName&gt;</code> with new value 'friend3' where <code>&lt;Name&gt;</code> node value is 'efg'.</p>
  
  <p>I have tried following code but it doesn't work.</p>
  
  <pre><code>DECLARE @xmlTable table (xmlfield xml)
  
  INSERT INTO @xmlTable (xmlfield) 
  VALUES (
      '&lt;ArrayOfFieldInfo&gt;
          &lt;FieldInfo&gt;
              &lt;Name&gt;abc&lt;/Name&gt;
              &lt;FriendlyName&gt;friend2&lt;/FriendlyName&gt;
              &lt;Description&gt;Custom added field&lt;/Description&gt;
              &lt;FieldGroupName&gt;Custom Fields&lt;/FieldGroupName&gt;
          &lt;/FieldInfo&gt;
          &lt;FieldInfo&gt;
              &lt;Name&gt;efg&lt;/Name&gt;
              &lt;FriendlyName&gt;friend1&lt;/FriendlyName&gt;
              &lt;Description&gt;Custom added field&lt;/Description&gt;
              &lt;FieldGroupName&gt;Custom Fields&lt;/FieldGroupName&gt;
          &lt;/FieldInfo&gt;
      &lt;/ArrayOfFieldInfo&gt;'
  )
  
  
  UPDATE @xmlTable
  SET xmlfield.modify('replace value of (/ArrayOfFieldInfo/FieldInfo/FriendlyName/text())[1] with "friend3"')
  WHERE xmlfield.value('(/ArrayOfFieldInfo/FieldInfo/Name/text())[1]', 'nvarchar(50)') = 'efg'
  </code></pre>