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