T-SQL：当长度>时如何比较两个XML类型的变量变量字符（最大）？

Question

仅使用 SQL Server 2008 R2（这将位于存储过程中），如何确定 XML 类型的两个变量是否等效？

这就是我想做的：

DECLARE @XmlA   XML
DECLARE @XmlB   XML

SET @XmlA = '[Really long Xml value]'
SET @XmlB = '[Really long Xml value]'

IF @XmlA = @XmlB
    SELECT 'Matching Xml!'

但你可能知道，它会返回：

消息 305，级别 16，状态 1，第 7 行 XML 数据类型不能为 比较或排序，除非使用 IS NULL 运算符。

我可以转换为 VarChar(MAX) 并进行比较，但这仅比较前 2MB。还有别的办法吗？

Answer 1

比较两个 XML 文档的方法有很多种，很大程度上取决于您想要容忍哪种差异：您肯定需要容忍编码、属性顺序、无关紧要的空格、数字字符引用和属性分隔符的使用方面的差异，并且您可能还应该容忍注释、命名空间前缀和 CDATA 使用方面的差异。因此，将两个 XML 文档作为字符串进行比较绝对不是一个好主意 - 除非您首先调用 XML 规范化。

对于许多目的，XQuery deep-equals() 函数做了正确的事情（并且或多或少相当于比较两个 XML 文档的规范形式）。我对 Microsoft 的 SQL Server XQuery 实现了解不够，无法告诉您如何从 SQL 级别调用它。

Answer 2

检查这个 SQL 函数：

CREATE FUNCTION [dbo].[CompareXml]
(
    @xml1 XML,
    @xml2 XML
)
RETURNS INT
AS 
BEGIN
    DECLARE @ret INT
    SELECT @ret = 0


    -- -------------------------------------------------------------
    -- If one of the arguments is NULL then we assume that they are
    -- not equal. 
    -- -------------------------------------------------------------
    IF @xml1 IS NULL OR @xml2 IS NULL 
    BEGIN
        RETURN 1
    END

    -- -------------------------------------------------------------
    -- Match the name of the elements 
    -- -------------------------------------------------------------
    IF  (SELECT @xml1.value('(local-name((/*)[1]))','VARCHAR(MAX)')) 
        <> 
        (SELECT @xml2.value('(local-name((/*)[1]))','VARCHAR(MAX)'))
    BEGIN
        RETURN 1
    END

     ---------------------------------------------------------------
     --Match the value of the elements
     ---------------------------------------------------------------
    IF((@xml1.query('count(/*)').value('.','INT') = 1) AND (@xml2.query('count(/*)').value('.','INT') = 1))
    BEGIN
    DECLARE @elValue1 VARCHAR(MAX), @elValue2 VARCHAR(MAX)

    SELECT
        @elValue1 = @xml1.value('((/*)[1])','VARCHAR(MAX)'),
        @elValue2 = @xml2.value('((/*)[1])','VARCHAR(MAX)')

    IF  @elValue1 <> @elValue2
    BEGIN
        RETURN 1
    END
    END

    -- -------------------------------------------------------------
    -- Match the number of attributes 
    -- -------------------------------------------------------------
    DECLARE @attCnt1 INT, @attCnt2 INT
    SELECT
        @attCnt1 = @xml1.query('count(/*/@*)').value('.','INT'),
        @attCnt2 = @xml2.query('count(/*/@*)').value('.','INT')

    IF  @attCnt1 <> @attCnt2 BEGIN
        RETURN 1
    END


    -- -------------------------------------------------------------
    -- Match the attributes of attributes 
    -- Here we need to run a loop over each attribute in the 
    -- first XML element and see if the same attribut exists
    -- in the second element. If the attribute exists, we
    -- need to check if the value is the same.
    -- -------------------------------------------------------------
    DECLARE @cnt INT, @cnt2 INT
    DECLARE @attName VARCHAR(MAX)
    DECLARE @attValue VARCHAR(MAX)

    SELECT @cnt = 1

    WHILE @cnt <= @attCnt1 
    BEGIN
        SELECT @attName = NULL, @attValue = NULL
        SELECT
            @attName = @xml1.value(
                'local-name((/*/@*[sql:variable("@cnt")])[1])', 
                'varchar(MAX)'),
            @attValue = @xml1.value(
                '(/*/@*[sql:variable("@cnt")])[1]', 
                'varchar(MAX)')

        -- check if the attribute exists in the other XML document
        IF @xml2.exist(
                '(/*/@*[local-name()=sql:variable("@attName")])[1]'
            ) = 0
        BEGIN
            RETURN 1
        END

        IF  @xml2.value(
                '(/*/@*[local-name()=sql:variable("@attName")])[1]', 
                'varchar(MAX)')
            <>
            @attValue
        BEGIN
            RETURN 1
        END

        SELECT @cnt = @cnt + 1
    END

    -- -------------------------------------------------------------
    -- Match the number of child elements 
    -- -------------------------------------------------------------
    DECLARE @elCnt1 INT, @elCnt2 INT
    SELECT
        @elCnt1 = @xml1.query('count(/*/*)').value('.','INT'),
        @elCnt2 = @xml2.query('count(/*/*)').value('.','INT')


    IF  @elCnt1 <> @elCnt2
    BEGIN
        RETURN 1
    END


    -- -------------------------------------------------------------
    -- Start recursion for each child element
    -- -------------------------------------------------------------
    SELECT @cnt = 1
    SELECT @cnt2 = 1
    DECLARE @x1 XML, @x2 XML
    DECLARE @noMatch INT

    WHILE @cnt <= @elCnt1 
    BEGIN

        SELECT @x1 = @xml1.query('/*/*[sql:variable("@cnt")]')
    --RETURN CONVERT(VARCHAR(MAX),@x1)
    WHILE @cnt2 <= @elCnt2
    BEGIN
        SELECT @x2 = @xml2.query('/*/*[sql:variable("@cnt2")]')
        SELECT @noMatch = dbo.CompareXml( @x1, @x2 )
        IF @noMatch = 0 BREAK
        SELECT @cnt2 = @cnt2 + 1
    END

    SELECT @cnt2 = 1

        IF @noMatch = 1
        BEGIN
            RETURN 1
        END

        SELECT @cnt = @cnt + 1
    END

    RETURN @ret
END

这是源

---

函数无法比较 XML 片段，例如，当没有单个根元素时，例如：

SELECT dbo.CompareXml('<data/>', '<data/><data234/>') 

为了解决此问题，当将 XML 传递给函数时，您必须将 XML 包装在 root 元素中或编辑函数来执行此操作。例如：

SELECT dbo.CompareXml('<r><data/></r>', '<r><data/><data234/></r>')  

Answer 3

您可以将字段转换为 varbinary(max)，对它们进行散列并比较散列。但是您肯定会错过 XML 是否等效但不相同

要计算哈希，您可以使用 CLR 函数：

using System;
using System.Data.SqlTypes;
using System.IO;

namespace ClrHelpers
{
    public partial class UserDefinedFunctions {
        [Microsoft.SqlServer.Server.SqlFunction]
        public static Guid HashMD5(SqlBytes data) {
            System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
            md5.Initialize();
            int len = 0;
            byte[] b = new byte[8192];
            Stream s = data.Stream;
            do {
                len = s.Read(b, 0, 8192);
                md5.TransformBlock(b, 0, len, b, 0);
            } while(len > 0);
            md5.TransformFinalBlock(b, 0, 0);
            Guid g = new Guid(md5.Hash);
            return g;
        }
    };
}

或 sql 函数：

CREATE FUNCTION dbo.GetMyLongHash(@data VARBINARY(MAX))
RETURNS VARBINARY(MAX)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    DECLARE @res VARBINARY(MAX) = 0x
    DECLARE @position INT = 1, @len INT = DATALENGTH(@data)

    WHILE 1 = 1
    BEGIN
        SET @res = @res + HASHBYTES('MD5', SUBSTRING(@data, @position, 8000))
        SET @position = @position+8000
        IF @Position > @len 
          BREAK
    END
    WHILE DATALENGTH(@res) > 16 SET @res= dbo.GetMyLongHash(@res)
    RETURN @res
END

Answer 4

如果您可以使用 SQL CLR，我建议使用 XNode.DeepEquals方法：

var xmlTree1 = new XElement("Root",
    new XAttribute("Att1", 1),
    new XAttribute("Att2", 2),
    new XElement("Child1", 1),
    new XElement("Child2", "some content")
);
var xmlTree2 = new XElement("Root",
    new XAttribute("Att1", 1),
    new XAttribute("Att2", 2),
    new XElement("Child1", 1),
    new XElement("Child2", "some content")
);
Console.WriteLine(XNode.DeepEquals(xmlTree1, xmlTree2));

如果你不能，你可以编写自己的函数（参见SQL FIDDLE 示例）：

CREATE function [dbo].[udf_XML_Is_Equal]
(
    @Data1 xml,
    @Data2 xml
)
returns bit
as
begin
    declare
        @i bigint, @cnt1 bigint, @cnt2 bigint,
        @Sub_Data1 xml, @Sub_Data2 xml,
        @Name varchar(max), @Value1 nvarchar(max), @Value2 nvarchar(max)

    if @Data1 is null or @Data2 is null
        return 1

    --=========================================================================================================
    -- If more than one root - recurse for each element
    --=========================================================================================================
    select
        @cnt1 = @Data1.query('count(/*)').value('.','int'),
        @cnt2 = @Data1.query('count(/*)').value('.','int')

    if @cnt1 <> @cnt2
        return 0        

    if @cnt1 > 1
    begin
        select @i = 1
        while @i <= @cnt1
        begin
            select
                @Sub_Data1 = @Data1.query('/*[sql:variable("@i")]'),
                @Sub_Data2 = @Data2.query('/*[sql:variable("@i")]')

            if dbo.udf_XML_Is_Equal_New(@Sub_Data1, @Sub_Data2) = 0
                return 0

            select @i = @i + 1
        end

        return 1
    end

    --=========================================================================================================
    -- Comparing root data
    --=========================================================================================================
    if @Data1.value('local-name(/*[1])','nvarchar(max)') <> @Data2.value('local-name(/*[1])','nvarchar(max)') 
        return 0

    if @Data1.value('/*[1]', 'nvarchar(max)') <> @Data2.value('/*[1]', 'nvarchar(max)')
        return 0

    --=========================================================================================================
    -- Comparing attributes
    --=========================================================================================================
    select
        @cnt1 = @Data1.query('count(/*[1]/@*)').value('.','int'),
        @cnt2 = @Data1.query('count(/*[1]/@*)').value('.','int')

    if @cnt1 <> @cnt2
        return 0

    if exists (
        select *
        from
        (
            select
                T.C.value('local-name(.)', 'nvarchar(max)') as Name,
                T.C.value('.', 'nvarchar(max)') as Value
            from @Data1.nodes('/*[1]/@*') as T(C)
        ) as D1
        full outer join
        (
            select
                T.C.value('local-name(.)', 'nvarchar(max)') as Name,
                T.C.value('.', 'nvarchar(max)') as Value
            from @Data2.nodes('/*[1]/@*') as T(C)
        ) as D2
        on D1.Name = D2.Name
        where
            not
            (
                D1.Value is null and D2.Value is null or
                D1.Value is not null and D2.Value is not null and D1.Value = D2.Value
            )
    )
        return 0


    --=========================================================================================================
    -- Recursively running for each child
    --=========================================================================================================
    select
        @cnt1 = @Data1.query('count(/*[1]/*)').value('.','int'),
        @cnt2 = @Data2.query('count(/*[1]/*)').value('.','int')

    if @cnt1 <> @cnt2
        return 0    

    select @i = 1
    while @i <= @cnt1        
    begin
        select
            @Sub_Data1 = @Data1.query('/*/*[sql:variable("@i")]'),
            @Sub_Data2 = @Data2.query('/*/*[sql:variable("@i")]')

        if dbo.udf_XML_Is_Equal(@Sub_Data1, @Sub_Data2) = 0
            return 0

        select @i = @i + 1
    end

    return 1
END

Answer 5

我偶然发现 这篇相当全面的文章，其中更详细地实际比较 2 个 XML 条目的内容以确定它们是否相同。这是有道理的，因为节点中属性的顺序可以不同，即使它们的值完全相同。我建议您仔细阅读它，甚至实现该功能，看看它是否适合您...我很快就尝试了它，它似乎对我有用？