title: How to update a SQL table column with XML data
body: <p>Table 1:</p>
  
  <pre><code>id      title               chtml
  0       Lopez, Michelle MD  &lt;root&gt;&lt;StartOne&gt;&lt;Value1&gt;Lopez, Michelle MD&lt;/Value1&gt;&lt;Value2&gt;Spanish&lt;/Value2&gt;&lt;Value3&gt;&lt;a title="49 west point" href="myloc.aspx?id=56" target="_blank"&gt;49 west point&lt;/a&gt;&lt;/Value3&gt;&lt;Value4&gt;908-783-0909&lt;/Value4&gt;&lt;Value5&gt;&lt;a title="CM" href="myspec.aspx?id=78" target="_blank"&gt;CM&lt;/a&gt;&lt;/Value5&gt;&lt;/StartOne&gt;&lt;/root&gt;
  1       Michael, Jogn, MD   &lt;root&gt;&lt;StartOne&gt;&lt;Value1&gt;Michael, Jogn, MD&lt;/Value1&gt;&lt;Value2&gt;English&lt;/Value2&gt;&lt;Value3&gt;&lt;a title="99 show drive" href="myloc.aspx?id=05" target="_blank"&gt;99 show drive&lt;/a&gt;&lt;/Value3&gt;&lt;Value4&gt;908-783-0909&lt;/Value4&gt;&lt;Value5&gt;&lt;a title="KM" href="myspec.aspx?id=40 target="_blank"&gt;KM&lt;/a&gt;&lt;/Value5&gt;&lt;/StartOne&gt;&lt;/root&gt;
  </code></pre>
  
  <p>The <code>chtml</code> is of type <code>ntext</code> for id <code>0</code>:</p>
  
  <pre><code>&lt;root&gt;
      &lt;StartOne&gt;
          &lt;Value1&gt;Lopez, Michelle MD&lt;/Value1&gt;
          &lt;Value2&gt;Spanish&lt;/Value2&gt;
          &lt;Value3&gt;
              &lt;a title="49 west point" href="myloc.aspx?id=56" target="_blank"&gt;49 west point&lt;/a&gt;
          &lt;/Value3&gt;
          &lt;Value4&gt;908-783-0909&lt;/Value4&gt;
          &lt;Value5&gt;
              &lt;a title="CM" href="myspec.aspx?id=78" target="_blank"&gt;CM&lt;/a&gt;
          &lt;/Value5&gt;
      &lt;/StartOne&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>The <code>chtml</code> is of type <code>ntext</code> for id <code>1</code>:</p>
  
  <pre><code>&lt;root&gt;
      &lt;StartOne&gt;
          &lt;Value1&gt;Michael, Jogn, MD&lt;/Value1&gt;
          &lt;Value2&gt;English&lt;/Value2&gt;
          &lt;Value3&gt;
              &lt;a title="99 show drive" href="myloc.aspx?id=05" target="_blank"&gt;99 show drive&lt;/a&gt;
          &lt;/Value3&gt;
          &lt;Value4&gt;908-783-0909&lt;/Value4&gt;
          &lt;Value5&gt;
              &lt;a title="KM" href="myspec.aspx?id=40 target="_blank"&gt;KM&lt;/a&gt;&lt;/Value5&gt;
          &lt;/Value5&gt;
      &lt;/StartOne&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p><code>Table 2</code> and <code>Table 3</code> has data which is used in the creation of <code>Table 1</code> for the link Url, title, and value:</p>
  
  <pre><code>Table 2:
  
      id              title
      ---------------------------------
      56              49 west point
      90              130 chester lane
      12              320 nolan street
      05              99 show drive
  
  Table 3:
  
      id              description
      ------------------------------
      78              CM
      39              IM
      40              KM
  </code></pre>
  
  <p><code>Table 4</code> has the updated data which will be used to update <code>Table 1</code> values:</p>
  
  <pre><code>Table 4:
  
      Name                    Value2              Value3                  Value4              Value5
      --------------------------------------------------------------------------------------------------------------
      Lopez, Michelle MD      English             130 chester lane        908-783-0909        KM
      Michael, Jogn, MD       Italian             320 nolan street        540-029-2090        IM
  </code></pre>
  
  <p>I am trying the following query to update one of the value:</p>
  
  <pre><code>declare @xml xml;
  select @xml = cast([content_html] as xml)
  from [myDB1].[dbo].[Table 1]
  
  set @xml.modify('
    replace value of (/root/StartOne/Value3/text())[1]
    with "&lt;a title="{Value3 from Table 4}" href="myloc.aspx?id={ID from Table 2 that matches the title with the title from Table 4}" target="_blank"&gt;{Value3 from Table 4}&lt;/a&gt;"
  ');
  
  -- How can I update the anchor link values of one table by querying another table data.
  -- How can I update multiple fields, for Example `Value3` and `Value5`?
  
  update [myDB1].[dbo].[Table 1]
  set [content_html] = cast(@xml as nvarchar(max))
  where [content_title] = 'Lopez, Michelle MD'
  </code></pre>
  
  <p>An example would be:</p>
  
  <pre><code>declare @xml xml;
  select @xml = cast([content_html] as xml)
  from [myDB1].[dbo].[Table 1]
  
  set @xml.modify('
    replace value of (/root/StartOne/Value3/text())[1]
    with "&lt;a title="130 chester lane" href="myloc.aspx?id=90" target="_blank"&gt;130 chester lane&lt;/a&gt;"
  ');
  
  update [myDB1].[dbo].[Table 1]
  set [content_html] = cast(@xml as nvarchar(max))
  where [content_title] = 'Lopez, Michelle MD'
  </code></pre>
  
  <p>Table 1 (after the above update):</p>
  
  <pre><code>id      title               chtml
  0       Lopez, Michelle MD  &lt;root&gt;&lt;StartOne&gt;&lt;Value1&gt;Lopez, Michelle MD&lt;/Value1&gt;&lt;Value2&gt;Spanish&lt;/Value2&gt;&lt;Value3&gt;&lt;a title="130 chester lane" href="myloc.aspx?id=90" target="_blank"&gt;130 chester lane&lt;/a&gt;&lt;/Value3&gt;&lt;Value4&gt;908-783-0909&lt;/Value4&gt;&lt;Value5&gt;&lt;a title="CM" href="myspec.aspx?id=78" target="_blank"&gt;CM&lt;/a&gt;&lt;/Value5&gt;&lt;/StartOne&gt;&lt;/root&gt;
  1       Michael, Jogn, MD   &lt;root&gt;&lt;StartOne&gt;&lt;Value1&gt;Michael, Jogn, MD&lt;/Value1&gt;&lt;Value2&gt;English&lt;/Value2&gt;&lt;Value3&gt;&lt;a title="99 show drive" href="myloc.aspx?id=05" target="_blank"&gt;99 show drive&lt;/a&gt;&lt;/Value3&gt;&lt;Value4&gt;908-783-0909&lt;/Value4&gt;&lt;Value5&gt;&lt;a title="KM" href="myspec.aspx?id=40 target="_blank"&gt;KM&lt;/a&gt;&lt;/Value5&gt;&lt;/StartOne&gt;&lt;/root&gt;
  </code></pre>
  
  <p>Please help me with the following:</p>
  
  <ul>
  <li>How can I update the anchor link values of one table by querying
  another table data?</li>
  <li>How can I update multiple fields, for Example <code>Value3</code> and <code>Value5</code>
  and then run the update statement?</li>
  </ul>