在 SQL Server 中使用 XQuery 比较两组 XML 数据 [英] Compare two sets of XML data using XQuery in SQL Server
本文介绍了在 SQL Server 中使用 XQuery 比较两组 XML 数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我将员工数据存储在日志表的 xml
列中.有时,数据也会从存储过程的 xml
列中更新.
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.
这是示例示例
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>'
在两个 xml
数据中存在一些差异,我需要像旧值一样显示这些数据新值作为 sql 的输出
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
我只需要像上面一样显示差异.所以请指导我如何使用 XQuery 比较两个 xml 数据,并在 SQL Server 中仅以上述方式显示差异.请用代码片段指导我.谢谢
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, '')
结果:
NodeName Value1 Value2
-------------------- -------------------- --------------------
EmpID 1005 1006
DOB 12/02/1981 05/02/1981
DeptID ACC001 ACC002
Salary 10,500 10,900
这篇关于在 SQL Server 中使用 XQuery 比较两组 XML 数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文