T-SQL: How to compare two XML type variables with length> VarChar (MAX)?

Question

Using only SQL Server 2008 R2 (this will be stored in proc), how can I determine if two variables of type XML are equivalent?

Here is what I want to do:

DECLARE @XmlA   XML
DECLARE @XmlB   XML

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

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

But, as you probably know, it returns:

Msg 305, Level 16, State 1, Line 7 The XML data type cannot be compared or sorted, unless the IS NULL statement is used.

I can convert to VarChar(MAX)and compare, but it only compares the first 2 MB. Is there another way?

Answer 1

, XML-, , , , CAN , . , , ... , , ?

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 root, . ,

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

Answer 3

XML-, , : , , , , , , , CDATA. , XML- - - XML.

XQuery deep-equals() ( XML). Microsoft SQL Server XQuery, , SQL.

Answer 4

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 5

SQL CLR, XNode.DeepEquals Method:

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 EXAMPLE):

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