比较 SQL 中的 XML 数据 [英] Compare Xml data in SQL

问题描述

我有两个具有相同 NVARCHAR 字段的表，它们确实包含 XML 数据.在某些情况下，这个真正的 XML 字段确实与其他表中的一行相同，但属性顺序不同，因此字符串比较不会返回正确的结果！！！

I have two tables with same NVARCHAR field that really contains XML data. in some cases this really-XML-field is really same as one row in other table but differs in attributes order and therefor string comparison does not return the correct result!!!

为了确定相同的 XML 字段，我需要进行如下比较:

and to determining the same XML fields ,I need to have a comparison like:

  cast('<root><book b="" c="" a=""/></root>' as XML) 
= cast('<root><book a="" b="" c=""/></root>' as XML)

但我收到了这个错误消息:

but I get this Err Msg:

XML 数据类型无法比较或排序，除非使用IS NULL 运算符.

The XML data type cannot be compared or sorted, except when using the IS NULL operator.

那么确定相同的 XML 而不将它们重新转换为 NVARCHAR 的最佳解决方案是什么?

then what is the best solution to determine the same XML without re-casting them to NVARCHAR?

推荐答案

为什么要投射它?只需将它们插入临时表中的 XML 列，然后运行 ​​Xquery 将它们与另一个表进行比较.包括比较的例子.有很多方法可以针对 XML 运行查询以获取相同的行 - 查询的确切编写方式将取决于偏好、要求等.我使用了一个简单的 group by/count，但是可以使用自连接，WHERE EXISTS 针对正在搜索重复项的列，您可以命名.

Why cast it at all? Just plug them into an XML column in a temp table and run Xquery to compare them to the other table. Included example of the comparison. There are many, many ways to run the query against the XML to get the rows that are the same - exactly how that query is written is going to depend on preference, requirements, etc. I went with a simple group by/count, but a self join could be used, WHERE EXISTS against the columns that are being searched for duplicates, you name it.

CREATE TABLE #Test (SomeXML NVARCHAR(MAX))
CREATE TABLE #XML (SomeXML XML)

INSERT #Test (SomeXML)
VALUES('<root><book b="b" c="c" a="a"/></root>')
    ,('<root><book a="a" b="b" c="c"/></root>')

INSERT #XML (SomeXML)

SELECT SomeXML FROM #Test;

WITH XMLCompare (a,b,c)
AS
(
SELECT 
    x.c.value('@a[1]','char(1)') AS a
    ,x.c.value('@b[1]','char(1)') AS b 
    ,x.c.value('@c[1]','char(1)') AS c  
FROM #XML
CROSS APPLY SomeXMl.nodes('/root/book') X(C)
)

SELECT 
    a
    ,b
    ,c
FROM XMLCompare as a
GROUP BY
    a
    ,b
    ,c
HAVING COUNT(*) >1

这篇关于比较 SQL 中的 XML 数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！