Compare two sets of XML data using XQuery in SQL Server
Solution 1
;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
Solution 2
I don't have the exact output you wanted - but at least you get a good comparison of old and new values:
;WITH OldData AS
(
SELECT
@XML1.value('(/NewDataSet/Employee/EmpID)[1]', 'int') AS 'EmpID',
@XML1.value('(/NewDataSet/Employee/Name)[1]', 'varchar(50)') AS 'Name',
@XML1.value('(/NewDataSet/Employee/DOB)[1]', 'datetime') AS 'DOB',
@XML1.value('(/NewDataSet/Employee/DeptID)[1]', 'varchar(50)') AS 'DeptID',
@XML1.value('(/NewDataSet/Employee/Salary)[1]', 'varchar(25)') AS 'Salary'
),
NewData AS
(
SELECT
@XML2.value('(/NewDataSet/Employee/EmpID)[1]', 'int') AS 'EmpID',
@XML2.value('(/NewDataSet/Employee/Name)[1]', 'varchar(50)') AS 'Name',
@XML2.value('(/NewDataSet/Employee/DOB)[1]', 'datetime') AS 'DOB',
@XML2.value('(/NewDataSet/Employee/DeptID)[1]', 'varchar(50)') AS 'DeptID',
@XML2.value('(/NewDataSet/Employee/Salary)[1]', 'varchar(25)') AS 'Salary'
)
SELECT
'Old values', od.*
FROM OldData od
UNION
SELECT 'New values', nd.*
FROM NewData nd
Gives you an output of:
EmpID Name DOB DeptID Salary
Old values 1005 keith 1981-12-02 00:00:00.000 ACC001 10,500
New values 1006 keith 1981-05-02 00:00:00.000 ACC002 10,900
SQL Server is great for storing and manipulating data - but presentation like this should be done in a front-end application (like an ASP.NET application) - not in T-SQL....
Solution 3
I am too late here !!! However I found that if employees XML as shown above has multiple records then the JOIN query with CTE returns incorrect results.
I have below XML input
DECLARE @XML1 XML
DECLARE @XML2 XML
SET @XML1 =
'<NewDataSet>
<Employees>
<Employee>
<Name> keith </Name>
<EmpID> 1005 </EmpID>
<DOB>12/02/1981</DOB>
<DeptID>ACC001</DeptID>
<Salary>10,500</Salary>
</Employee>
<Employee>
<Name> keith </Name>
<EmpID> 1004 </EmpID>
<DOB>12/02/1981</DOB>
<DeptID>ACC001</DeptID>
<Salary>10,500</Salary>
</Employee>
</Employees>
</NewDataSet>'
SET @XML2 =
'<NewDataSet>
<Employees>
<Employee>
<Name> keith </Name>
<EmpID> 1005 </EmpID>
<DOB>12/02/1981</DOB>
<DeptID>ACC001</DeptID>
<Salary>10,500</Salary>
</Employee>
<Employee>
<Name> keith </Name>
<EmpID> 1004 </EmpID>
<DOB>12/02/1981</DOB>
<DeptID>ACC001</DeptID>
<Salary>10,501</Salary>
</Employee>
<Employee>
<Name> keith1 </Name>
<EmpID> 10040 </EmpID>
<DOB>12/02/1981</DOB>
<DeptID>ACC001</DeptID>
<Salary>10,501</Salary>
</Employee>
</Employees>
</NewDataSet>'
I will use below query to find the difference
select T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
T.N.value('.', 'nvarchar(100)') as Value
from @XML2.nodes('/NewDataSet/Employees/Employee/*') as T(N)
EXCEPT
select T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
T.N.value('.', 'nvarchar(100)') as Value
from @XML1.nodes('/NewDataSet/Employees/Employee/*') as T(N)
Hope this helps !!!
Related videos on Youtube
Thomas
i am developer. i am working with .Net technology (v1.1 & v2.0) last 4 year. i like this forum for fast & good response and that is why i joined this forum. my friends profile id Mou :- http://stackoverflow.com/users/728750/user728750?tab=questions and Keith :- http://stackoverflow.com/users/750398/keith-costa thanks
Updated on June 04, 2022Comments
-
Thomas about 2 years
Suppose I store employee data in a
xml
column in my log table. Sometimes data is also updated in thexml
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 sqlOld 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
-
Thomas over 12 yearsur effort was good but u hard code the field name which i dont want. thanks
-
marc_s over 12 years+1 excellent work - I kept studying around how to achieve this and just didn't see the forest for the trees!
-
Thomas over 12 yearswhat to change in ur code to show the data how marc_s data is showing but i dont want to hard code the field name like marc_s. can u plzz help. thanks
-
Mikael Eriksson over 12 yearsYou need to use pivot and when you want a variable number of columns you need to do it dynamically. Have a look at dynamic pivot here. simple-talk.com/community/blogs/andras/archive/2007/09/14/… store away the result from this query in a table variable and then do the dynamic pivot stuff from the article on the table variable.
-
Question_Guy about 10 years@Mikael Where does the 'T.N.' stuff come from?
-
Mikael Eriksson about 10 years@Question_Guy The nodes function shreds the XML to a table.
T
is the table alias andN
is the column name. ` from @XML1.nodes('/NewDataSet/Employee/*') as T(N)` It does not have toT(N)
, you decide what alias and column name you want to use.