title: How to merge 2 XML variables in SQL Server 2008
body: <p>Lets assume I have 2 XML variables in SQL Server 2008 with the following XML</p>
  
  <pre><code>DECLARE @FIRST XML = '&lt;DBPerson&gt;
                            &lt;firstname&gt;John&lt;/firstname&gt;
                            &lt;lastname&gt;Bob&lt;/lastname&gt;
                        &lt;/DBPerson&gt;',
          @Second XML = '&lt;FromUI&gt;
                             &lt;lastname&gt;New Bob&lt;/lastname&gt;
                             &lt;age&gt;39&lt;/age&gt;
                         &lt;/FromUI&gt;';
  </code></pre>
  
  <p>I want the following output:</p>
  
  <pre><code>&lt;DBPerson&gt;
       &lt;firstname&gt;John&lt;/firstname&gt;
       &lt;lastname&gt;New Bob&lt;/lastname&gt;
       &lt;age&gt;39&lt;/age&gt;
  &lt;/DBPerson&gt;
  </code></pre>
  
  <p>Basically I want to merge contents of 2 XML variables into one where the variable <code>@Second</code> should hold precedence (if a node exists in both <code>@First</code> &amp; <code>@Second</code>, the node inside <code>@Second</code> should be considered).</p>
  
  <p>The approach I have taken is to first get the list of all unique elements within both the root elements like this:</p>
  
  <pre><code>WITH ALLFields AS
  (
      SELECT 
            x.y.value('local-name(.)','varchar(50)') As Element   
      FROM @Second.nodes('FromUI/*') AS x(y)
      UNION
      SELECT 
            x.y.value('local-name(.)','varchar(50)') As Element   
      FROM @FIRST.nodes('DBPerson/*') AS x(y)
  )
  SELECT * FROM ALLFields AF
  </code></pre>
  
  <p>But I am clueless how to proceed from here on. I know I have to use <code>sql:column</code> somewhere to build a table first to get just node names and their values (based on <code>AllFields</code>) and then I can use <code>FOR XML PATH('DBPerson')</code> to form the final xml but a bit unsure of the usage of <code>sql:column</code></p>
  
  <p>Any help highly appreciated.</p>
  
  <p><strong>UPDATE</strong>:
  I have boiled it down to the following query:</p>
  
  <pre><code>DECLARE @FIRST XML = '&lt;DBPerson&gt;&lt;firstname&gt;John&lt;/firstname&gt;&lt;lastname&gt;Bob&lt;/lastname&gt;&lt;/DBPerson&gt;',
          @Second XML = '&lt;FromUI&gt;&lt;lastname&gt;New Bob&lt;/lastname&gt;&lt;age&gt;39&lt;/age&gt;&lt;/FromUI&gt;';
  
  
  WITH ALLFields AS
  (
      SELECT 
            x.y.value('local-name(.)','varchar(50)') As Element   
      FROM @Second.nodes('FromUI/*') AS x(y)
      UNION
      SELECT 
            x.y.value('local-name(.)','varchar(50)') As Element   
      FROM @FIRST.nodes('DBPerson/*') AS x(y)
  ), Filtered AS
  (
      SELECT 
            Element
          , @FIRST.value('(DBPerson/*[local-name()=sql:column("Element")])[1]','varchar(max)') AS F
          , @Second.value('(FromUI/*[local-name()=sql:column("Element")])[1]','varchar(max)') AS S
      FROM ALLFields AF
  ), FinalValues AS
  (
      SELECT
          Element 
          , CASE 
                  WHEN S IS NULL THEN F
                  ELSE S
              END AS V
      FROM Filtered
  )
  SELECT * FROM FinalValues
  </code></pre>
  
  <p>This query gives me a table with all the elements in one column and the data for the elements in another column. Now how do i generate my final XML like this:</p>
  
  <pre><code>&lt;DBPerson&gt;&lt;firstname&gt;John&lt;/firstname&gt;&lt;lastname&gt;New Bob&lt;/lastname&gt;&lt;age&gt;39&lt;/age&gt;&lt;/DBPerson&gt;
  </code></pre>