title: Update XML column with nodes from another XML on SQL Server
body: <p>I have two tables with 1-M relationship, both containing XML columns.  I have to update an XML in second table's column from another node from first table's XML's node.  After struggling with the syntax to get it work, I have finally been able to get it to work.  However, it is still not working.  I see the rows getting updated in the messages tab but when I select the values from second table, nothing is updated.  Here is a sample of my TSQL code:</p>
  
  <pre><code>DECLARE @Table1 TABLE (
      Id BIGINT PRIMARY KEY
      ,Document XML
      ,NewValue NVARCHAR(max)
      )
  
  INSERT INTO @Table1
  SELECT TOP 10 a.Id
      ,a.Document
      ,a.Document.value('(/root/metadata/name)[1]', 'nvarchar(500)') + 'blahblah'
  FROM dbo.ParentTable a
  ORDER BY a.Id DESC
  
  DECLARE @Table2 TABLE (
      Id BIGINT PRIMARY KEY
      ,ParentId BIGINT
      ,OriginalXml XML
      ,ModifiedXml XML
      ,ValueType NVARCHAR(255)
      )
  
  INSERT INTO @Table2
  SELECT sm.Id
      ,sm.ParentId
      ,sm.MapXml
      ,sm.MapXml
      ,sm.ValueType
  FROM dbo.ChildTable sm
  
  PRINT 'before update'
  
  UPDATE sm
  SET ModifiedXml.modify('replace value of (//url/loc/text())[1] with sql:column("NewValue")')
  FROM @Table2 sm
  INNER JOIN @Table1 a ON a.Id = sm.ParentId
  WHERE sm.ValueType = 'web';
  
  UPDATE sm
  SET ModifiedXml.modify('replace value of (/html/url/text())[1] with sql:column("NewValue")')
  FROM @Table2 sm
  INNER JOIN @Table1 a ON a.Id = sm.ParentId
  WHERE sm.ValueType = 'html';
  
  PRINT 'after update'
  
  SELECT *
  FROM @Table2
  </code></pre>
  
  <p>In case it is not clear, here is the XML schema for parent table:</p>
  
  <pre><code>&lt;root&gt;
      &lt;metadata&gt;
          &lt;title&gt;&lt;/title&gt;
          &lt;header&gt;&lt;/header&gt;
          &lt;nodeN&gt;&lt;/nodeN&gt;
      &lt;/metadata&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>And here are two possible schema for child table:</p>
  
  <pre><code>&lt;url&gt;
      &lt;loc&gt;somevalue&lt;/loc&gt;
  &lt;/url&gt;
  
  &lt;html&gt;
      &lt;url&gt;somevlaue&lt;/url&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>The problems I had been facing previously were that I had my modified XML column declared as XML.  Also, I was not getting the child nodes from the parent table during select but instead trying to get the values during update time.</p>
  
  <p><em>Update</em></p>
  
  <p>Here is the updated SQL.  This for some reason is working.  The only reason I had not posted my original XML schema is because they are too long.  I will double check the XPaths in my original TSQL.  </p>
  
  <pre><code>DECLARE @Table1 TABLE (
      Id BIGINT PRIMARY KEY
      ,Document XML
      ,NewValue NVARCHAR(max)
      )
  
  INSERT INTO @Table1 (
      Id
      ,Document
      )
  VALUES (
      1
      ,'&lt;root&gt;&lt;metadata&gt;&lt;title&gt;title 1&lt;/title&gt;&lt;header&gt;header 1&lt;/header&gt;&lt;/metadata&gt;&lt;/root&gt;'
      )
  
  INSERT INTO @Table1 (
      Id
      ,Document
      )
  VALUES (
      2
      ,'&lt;root&gt;&lt;metadata&gt;&lt;title&gt;title 2&lt;/title&gt;&lt;header&gt;header 2&lt;/header&gt;&lt;/metadata&gt;&lt;/root&gt;'
      )
  
  INSERT INTO @Table1 (
      Id
      ,Document
      )
  VALUES (
      3
      ,'&lt;root&gt;&lt;metadata&gt;&lt;title&gt;title 3&lt;/title&gt;&lt;header&gt;header 3&lt;/header&gt;&lt;/metadata&gt;&lt;/root&gt;'
      )
  
  INSERT INTO @Table1 (
      Id
      ,Document
      )
  VALUES (
      4
      ,'&lt;root&gt;&lt;metadata&gt;&lt;title&gt;title 4&lt;/title&gt;&lt;header&gt;header 4&lt;/header&gt;&lt;/metadata&gt;&lt;/root&gt;'
      )
  
  UPDATE @Table1
  SET NewValue = Document.value('(/root/metadata/title)[1]', 'nvarchar(max)')
  
  DECLARE @Table2 TABLE (
      Id BIGINT PRIMARY KEY
      ,ParentId BIGINT
      ,OriginalXml XML
      ,ModifiedXml XML
      ,ValueType NVARCHAR(255)
      )
  
  INSERT INTO @Table2
  VALUES (
      1
      ,1
      ,'&lt;url&gt;&lt;loc&gt;old title 1&lt;/loc&gt;&lt;/url&gt;'
      ,'&lt;url&gt;&lt;loc&gt;old title 1&lt;/loc&gt;&lt;/url&gt;'
      ,'web'
      )
  
  INSERT INTO @Table2
  VALUES (
      2
      ,3
      ,'&lt;html&gt;&lt;url&gt;old title 3&lt;/url&gt;&lt;/html&gt;'
      ,'&lt;html&gt;&lt;url&gt;old title 3&lt;/url&gt;&lt;/html&gt;'
      ,'html'
      )
  
  PRINT 'before update'
  
  UPDATE sm
  SET ModifiedXml.modify('replace value of (//url/loc/text())[1] with sql:column("NewValue")')
  FROM @Table2 sm
  INNER JOIN @Table1 a ON a.Id = sm.ParentId
  WHERE sm.ValueType = 'web';
  
  UPDATE sm
  SET ModifiedXml.modify('replace value of (/html/url/text())[1] with sql:column("NewValue")')
  FROM @Table2 sm
  INNER JOIN @Table1 a ON a.Id = sm.ParentId
  WHERE sm.ValueType = 'html';
  
  PRINT 'after update'
  
  SELECT *
  FROM @Table2
  </code></pre>