Comparing XML values in database works for changes but not additions

Question

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.

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 <original> and <changed> node, which will contain the result of a query, like this:

@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'))

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.

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 & desired outcomes as well.

DECLARE @Audit TABLE (
    id int not null,
    Details xml 
)

INSERT INTO @Audit (ID,details) 
Values (1, 
'<Update>
  <Original xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <dbo.MyTable>
      <ID>E99C0245-1A06-EA11-A836-00155DB6D822</ID>
      <Amount>1234.00</Amount>
    </dbo.MyTable>
  </Original>
  <Changed xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <dbo.MyTable>
      <ID>E99C0245-1A06-EA11-A836-00155DB6D822</ID>
      <Amount>1234.00</Amount>
    </dbo.MyTable>
    <dbo.MyTable>
    <ID>D74B6DED-1B06-EA11-A836-00155DB6D822</ID>
      <Amount>555.00</Amount>  
    </dbo.MyTable>
  </Changed>
</Update>')

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, '') <> COALESCE(XMLChanged.VALUE, '')

Output:

+--------+--------------------------------------+--------------------------------------+
| FIELD  | ORIGINAL                             | CHANGED                              |
+--------+--------------------------------------+--------------------------------------+
| Amount | 1234.00                              | 555.00                               |
+--------+--------------------------------------+--------------------------------------+
| ID     | E99C0245-1A06-EA11-A836-00155DB6D822 | D74B6DED-1B06-EA11-A836-00155DB6D822 |
+--------+--------------------------------------+--------------------------------------+

Desired output:

+--------+--------------------------------------+--------------------------------------+
| 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 |
+--------+--------------------------------------+--------------------------------------+

score 1 · Accepted Answer · answered Nov 13 '19 at 14:37

I think you are close -- in my experience it is very hard to use full outer joins and get helpful results -- I like to only use left joins and use a set of known keys to join to both sides... Like this (given your CTEs)

SELECT BASE.Field,
       O.VALUE AS Original_Value
       C.VALUE AS Changed_Value
FROM (SELECT  Field FROM XMLOriginal 
      UNION 
      SELECT Field FROM XMLChanged) AS BASE
LEFT JOIN XMLOriginal AS O ON BASE.Field = O.Field
LEFT JOIN XMLChanged AS  C ON BASE.Field = C.Field

Comparing XML values in database works for changes but not additions

1 Answers1