title: Comparing XML values in database works for changes but not additions
body: <p>I'm trying to compare XML data in order to determine what has been changed or added. If a singular record is changed, or more than one change happens in the same order, it returns the expected result. However if a new record is added, it doesn't work as expected. </p>
  
  <p>The problem I have is that the data could come from many places so there are no consistent table names, IDs, or otherwise within the data. However the XML will always have an <code>&lt;original&gt;</code> and <code>&lt;changed&gt;</code> node, which will contain the result of a query, like this:</p>
  
  <pre><code>@xmlOriginal =SELECT * FROM Table WHERE ID=@ID FOR XML AUTO, ELEMENTS XSINIL, ROOT('Original'))
  --INSERT query goes here
  @xmlChanged = SELECT * FROM TABLE WHERE ID=@ID FOR XML AUTO, ELEMENTS XSINIL, ROOT('Changed'))
  SET @xml = (SELECT @xmlOriginal, @xmlChanged for XML Path('Update'))
  </code></pre>
  
  <p>Is there a way to intelligently figure out what's changed/added? I just need to know original vs new values, regardless of if there was an original value or not.</p>
  
  <p>I've created a simple example below which begins with one row, then adds another. When I run my SQL to see the changes, it shows the wrong information. To make it clear I have included actual &amp; desired outcomes as well.</p>
  
  <pre><code>DECLARE @Audit TABLE (
      id int not null,
      Details xml 
  )
  
  INSERT INTO @Audit (ID,details) 
  Values (1, 
  '&lt;Update&gt;
    &lt;Original xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
      &lt;dbo.MyTable&gt;
        &lt;ID&gt;E99C0245-1A06-EA11-A836-00155DB6D822&lt;/ID&gt;
        &lt;Amount&gt;1234.00&lt;/Amount&gt;
      &lt;/dbo.MyTable&gt;
    &lt;/Original&gt;
    &lt;Changed xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
      &lt;dbo.MyTable&gt;
        &lt;ID&gt;E99C0245-1A06-EA11-A836-00155DB6D822&lt;/ID&gt;
        &lt;Amount&gt;1234.00&lt;/Amount&gt;
      &lt;/dbo.MyTable&gt;
      &lt;dbo.MyTable&gt;
      &lt;ID&gt;D74B6DED-1B06-EA11-A836-00155DB6D822&lt;/ID&gt;
        &lt;Amount&gt;555.00&lt;/Amount&gt;  
      &lt;/dbo.MyTable&gt;
    &lt;/Changed&gt;
  &lt;/Update&gt;')
  
  DECLARE @XML XML = (SELECT Details FROM @Audit WHERE ID = 1)
  
  ;WITH 
  XMLOriginal AS
      (
        SELECT T.N.value('local-name(.)', 'nvarchar(100)') as Field,
               T.N.value('.', 'nvarchar(1000)') as VALUE
        FROM @XML.nodes('Update/Original/*/*') as T(N)
      ),
  XMLChanged as
      (
        SELECT T.N.value('local-name(.)', 'nvarchar(100)') as Field,
               T.N.value('.', 'nvarchar(1000)') as VALUE
        FROM @XML.nodes('Update/Changed/*/*') as T(N)
      )
  
      SELECT   
          COALESCE(XMLOriginal.Field, XMLChanged.Field) as Field 
          ,XMLOriginal.VALUE as Original
          ,XMLChanged.VALUE as Changed
  FROM 
      XMLOriginal
  FULL OUTER JOIN XMLChanged
      ON XMLOriginal.Field = XMLChanged.Field
  LEFT OUTER JOIN @Audit L 
          ON L.ID = 1
      WHERE COALESCE(XMLOriginal.VALUE, '') &lt;&gt; COALESCE(XMLChanged.VALUE, '')   
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>+--------+--------------------------------------+--------------------------------------+
  | FIELD  | ORIGINAL                             | CHANGED                              |
  +--------+--------------------------------------+--------------------------------------+
  | Amount | 1234.00                              | 555.00                               |
  +--------+--------------------------------------+--------------------------------------+
  | ID     | E99C0245-1A06-EA11-A836-00155DB6D822 | D74B6DED-1B06-EA11-A836-00155DB6D822 |
  +--------+--------------------------------------+--------------------------------------+
  </code></pre>
  
  <p>Desired output:</p>
  
  <pre><code>+--------+--------------------------------------+--------------------------------------+
  | FIELD  | ORIGINAL                             | CHANGED                              |
  +--------+--------------------------------------+--------------------------------------+
  | Amount | 1234.00                              | 1234.00                              |
  +--------+--------------------------------------+--------------------------------------+
  | ID     | E99C0245-1A06-EA11-A836-00155DB6D822 | E99C0245-1A06-EA11-A836-00155DB6D822 |
  +--------+--------------------------------------+--------------------------------------+
  | Amount |  NULL                                | 555.00                               |
  +--------+--------------------------------------+--------------------------------------+
  | ID     |  NULL                                | D74B6DED-1B06-EA11-A836-00155DB6D822 |
  +--------+--------------------------------------+--------------------------------------+
  </code></pre>