如何比较mssql中的两个xml文件

I have 2 files of type :

我有两个类型的文件:

@xmlAuthors1 XML =N'
       <Author name="John" surname="Clinton" />
       <Author name="Bill" surname="Skobov" />
       <Author name="John" surname="Lokwood" />;

@xmlAuthors2 XML =N'
       <Author name="Bill" surname="Skobov" />
       <Author name="John" surname="Clinton" />
       <Author name="John" surname="Lokwood" />;

how do find out that they are equal? compare each line of the file 1 with each line from of the file 2

如何发现它们是相等的?将文件1的每一行与文件2中的每一行进行比较

3 个解决方案

#1

For example you can compare them like:

例如，你可以比较它们:

DECLARE @xmlAuthors1 XML = N'
               <Author name="John" surname="Clinton" />
               <Author name="Bill" surname="Skobov" />
               <Author name="John" surname="Lokwood" />',
        @xmlAuthors2 XML =N'
               <Author name="Bill" surname="Skobov" />
               <Author name="Johns" surname="Clinton" />
               <Author name="John" surname="Lokwood" />'


SELECT *
FROM (
    SELECT  CAST(x1.t.query('.') as nvarchar(max)) as x1,
            CAST(x2.t.query('.') as nvarchar(max)) as x2
    FROM @xmlAuthors1.nodes('/*') as x1(t)
    FULL OUTER JOIN @xmlAuthors2.nodes('/*') as x2(t)
        ON CAST(x1.t.query('.') as nvarchar(max)) = CAST(x2.t.query('.') as nvarchar(max))
) as x
WHERE x1 is null or x2 is null

If there are equal there will be no output

如果相等，就没有输出。

#2

It depends what equal means to you. I would compare data as follows:

这取决于平等对你意味着什么。我将如下数据进行比较:

DECLARE @xmlAuthors1 XML =N'
       <Author name="John" surname="Clinton" />
       <Author name="Bill" surname="Skobov" />
       <Author name="John" surname="Lokwood" />
       <Author name="John" surname="Blurred" />';

DECLARE @xmlAuthors2 XML =N'
       <Author name="Bill" surname="Skobov" />
       <Author name="John" surname="Clinton" />
       <Author name="John" surname="Lokwood" />';

WITH Xml1 AS
(
    SELECT
        T.A.value('@name', 'varchar(20)') Name,
        T.A.value('@surname', 'varchar(20)') Surname
    FROM @xmlAuthors1.nodes('/Author') T(A)
), Xml2 AS
(
    SELECT
        T.A.value('@name', 'varchar(20)') Name,
        T.A.value('@surname', 'varchar(20)') Surname
    FROM @xmlAuthors2.nodes('/Author') T(A)
)
SELECT 'Unique in 1' [Description], * FROM (SELECT * FROM Xml1 EXCEPT SELECT * FROM Xml2) Q1
UNION ALL
SELECT 'Unique in 2' [Description], * FROM (SELECT * FROM Xml2 EXCEPT SELECT * FROM Xml1) Q2

This particular example yields:

这个特殊示例收益率:

Description   Name    Surname
-----------   ----    -------
Unique in 1   John    Blurred

#3

This was my approach:

这是我的方法:

There is a nice trick with .query(.data()).value() to retrieve all inner text as space separated string. This makes it easy to compare on string level...

使用.query(.data() .value()检索所有内部文本作为空格分隔的字符串有一个很好的技巧。这使得在字符串级别上进行比较很容易……

I changed your input to inlcude some more test cases:

我将你的输入改为inlcude一些测试用例:

DECLARE @xmlAuthors1 XML = N'
               <Author name="John" surname="Clinton" />
               <Author name="Bill" surname="Different" />
               <Author name="John" surname="Lokwood" />
               <Author name="Test" surname="it" />',
        @xmlAuthors2 XML =N'
               <Author name="Bill" surname="Skobov" />
               <Author name="John" surname="Clinton" />
               <Author name="John" surname="Lokwood" />
               <Author name="One" surname="More" />';

SELECT ISNULL(Author1,Author2) AS Author
      ,CASE WHEN Author1 IS NULL THEN 'Exists in 2' ELSE 'Exists in 1' END AS [Source]
FROM
(
    SELECT B.query('data(@*)').value('.','varchar(max)') AS Author1
    FROM @xmlAuthors1.nodes('/Author') AS A(B)
) AS tbl1
FULL OUTER JOIN
( 
    SELECT B.query('data(@*)').value('.','varchar(max)') As Author2
    FROM @xmlAuthors2.nodes('/Author') AS A(B)
)AS tbl2 ON Author1=Author2
WHERE Author1 IS NULL OR Author2 IS NULL

If you want to find those which are equal in both you might use this

如果你想找到两个都相等的，你可以用这个

SELECT B.query('data(@*)').value('.','varchar(max)') AS EqualAuthor
FROM @xmlAuthors1.nodes('/Author') AS A(B)
INTERSECT
SELECT B.query('data(@*)').value('.','varchar(max)') 
FROM @xmlAuthors2.nodes('/Author') AS A(B)

#1