SQL Server - Compare two xml data by xquery in sql server

Asked By Tridip Bhattacharjee on 06-Jan-12 01:53 PM
suppose i store employee data as xml in xml type column in my log table. some time data also update in xml type column from store 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 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 by xquery and show the difference only in the above fashion in sql server. please guide me with code snippet. thanks
[)ia6l0 iii replied to Tridip Bhattacharjee on 06-Jan-12 10:24 PM
Have you heard about "Change Data Capture"? Here is the http://msdn.microsoft.com/en-us/library/cc280386.aspx that talks abou this and much more. 

If you think that is too much too grab, or too much on your data infrastrucutre - you would have to write stored procedues to do lookup against a base table and a temporary insert table. Remember, to compare you need something like left and right. 

Here is one article at http://www.sqlservercentral.com/Forums/Topic504916-338-1.aspx. Scroll down towards the end of the post. There seems to be a solution. Hopefully, that is working and may help you proceed. 

Hope this information helps.

Sandeep Mittal replied to Tridip Bhattacharjee on 06-Jan-12 10:38 PM
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>'
 
 
;WITH CTE1 AS (
  SELECT  EmpID = Node.Data.value('(EmpID)[1]', 'VARCHAR(MAX)')
      , Name = Node.Data.value('(Name)[1]', 'VARCHAR(MAX)')
      , DOB = Node.Data.value('(DOB)[1]', 'VARCHAR(MAX)')
      , DeptID = Node.Data.value('(DeptID)[1]', 'VARCHAR(MAX)')
      , Salary = Node.Data.value('(Salary)[1]', 'VARCHAR(MAX)')
  FROM    @XML1.nodes('/NewDataSet/Employee') Node(Data)
), CTE2 AS (
  SELECT  EmpID = Node.Data.value('(EmpID)[1]', 'VARCHAR(MAX)')
      , Name = Node.Data.value('(Name)[1]', 'VARCHAR(MAX)')
      , DOB = Node.Data.value('(DOB)[1]', 'VARCHAR(MAX)')
      , DeptID = Node.Data.value('(DeptID)[1]', 'VARCHAR(MAX)')
      , Salary = Node.Data.value('(Salary)[1]', 'VARCHAR(MAX)')
  FROM    @XML2.nodes('/NewDataSet/Employee') Node(Data)
)
 
SELECT  OLD.VALUE, NEW.VALUE
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY KEYNAME) AS ID, VALUE
  FROM (  SELECT  * FROM CTE1
  ) AS T1 UNPIVOT (VALUE FOR KEYNAME IN (EmpID, Name, DOB, DeptID, Salary)) AS A
) OLD INNER JOIN (
  SELECT ROW_NUMBER() OVER (ORDER BY KEYNAME) AS ID, VALUE
  FROM (  SELECT  * FROM CTE2
  ) AS T1 UNPIVOT (VALUE FOR KEYNAME IN (EmpID, Name, DOB, DeptID, Salary)) AS A
) NEW ON OLD.ID = NEW.ID AND OLD.VALUE<>NEW.VALUE
kalpana aparnathi replied to Tridip Bhattacharjee on 07-Jan-12 06:43 AM
Try this code:

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, '')
Sandeep Mittal replied to kalpana aparnathi on 07-Jan-12 07:46 AM
Hi Tridip

My solution will definitely work but use Kalpana's solution. I believe she has given the best solution.
Tridip Bhattacharjee replied to kalpana aparnathi on 07-Jan-12 11:50 AM
good help.......great.