Compare two sets of XML data using XQuery in SQL Server

sql-server-2005, t-sql, xquery-sql

Solution

;with XML1 as
(
  select T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
         T.N.value('.', 'nvarchar(100)') as Value
  from @XML1.nodes('/NewDataSet/Employee/*') as T(N)
),
XML2 as
(
  select T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
         T.N.value('.', 'nvarchar(100)') as Value
  from @XML2.nodes('/NewDataSet/Employee/*') as T(N)
)
select coalesce(XML1.NodeName, XML2.NodeName) as NodeName, 
       XML1.Value as Value1, 
       XML2.Value as Value2
from XML1
  full outer join XML2
    on XML1.NodeName = XML2.NodeName
where coalesce(XML1.Value, '') <> coalesce(XML2.Value, '')

Result:

NodeName             Value1               Value2
-------------------- -------------------- --------------------
EmpID                1005                 1006
DOB                  12/02/1981           05/02/1981
DeptID               ACC001               ACC002
Salary               10,500               10,900

Problem

Suppose I store employee data in a `xml` column in my log table. Sometimes data is also updated in the `xml` column from a stored procedure. Here is the sample example ``` DECLARE @XML1 XML DECLARE @XML2 XML SET @XML1 = '<NewDataSet> <Employee> <EmpID>1005</EmpID> <Name> keith </Name> <DOB>12/02/1981</DOB> <DeptID>ACC001</DeptID> <Salary>10,500</Salary> </Employee> </NewDataSet>' SET @XML2 = '<NewDataSet> <Employee> <EmpID>1006</EmpID> <Name> keith </Name> <DOB>05/02/1981</DOB> <DeptID>ACC002</DeptID> <Salary>10,900</Salary> </Employee> </NewDataSet>' ``` There is some difference in two the `xml` data which I need to show like old value & new value as a output of sql ``` Old Value New Value --------- --------- 1005 1006 12/02/1981 05/02/1981 ACC001 ACC002 10,500 10,900 ``` I just need to show the difference like above. So please guide me how to compare two xml data using XQuery and show the difference only in the above fashion in SQL Server. Please guide me with code snippet. thanks

Original source