在SQL Server中使用XQuery比较两组XML数据

Question

假设我在日志表中使用xml列存储员工数据。有时候，也会从存储过程中更新xml列中的数据。

以下是示例样本。

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输出的旧值和新值。

Old Value             New Value
---------             ---------
1005                  1006
12/02/1981            05/02/1981
ACC001                ACC002
10,500                10,900

我只需要像上面那样展示差异。因此，请指导我如何使用XQuery比较两个XML数据，并仅以SQL Server中的上述方式显示差异。请用代码片段指导我。谢谢。

- Thomas

Answer 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, '')    

结果：

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

- Mikael Eriksson

---

+1 优秀的工作 - 我一直在研究如何实现这个目标，但就是看不到整体的局面！ - marc_s

请问如何更改您的代码以显示数据，就像marc_s的数据一样，但我不想像marc_s那样硬编码字段名称。能否请您帮忙？谢谢。 - Thomas

你需要使用pivot，当你想要一个可变数量的列时，你需要动态地进行操作。在这里查看动态pivot。http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx将此查询的结果存储在一个表变量中，然后对该表变量执行文章中的动态pivot操作。 - Mikael Eriksson

@Mikael，“T.N.”这个东西是从哪里来的？ - Question_Guy

@Question_Guy，nodes函数将XML解析成表格。T是表格别名，N是列名。from @XML1.nodes('/NewDataSet/Employee/*') as T(N)不一定要使用T(N)，您可以自行决定要使用的别名和列名。 - Mikael Eriksson

Answer 2

我没有你想要的精确输出 - 但至少你可以得到旧值和新值的良好比较：

;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

给你一个输出结果：

            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非常适合存储和操作数据，但像这样的演示应该在前端应用程序（如ASP.NET应用程序）中完成，而不是在T-SQL中完成...

- marc_s

---

你的努力很好，但是你硬编码了字段名称，而我不想要。谢谢。 - Thomas

Answer 3

我是太晚了!!! 但是我发现，如果员工XML如上所示有多个记录，则带有CTE的JOIN查询会返回不正确的结果。

我有以下XML输入。

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

我将使用以下查询来查找差异。

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)

希望这能帮到你！

- Akshay