Identifying New Tags Between 2 XML In SQL

I'm using SQL to find the differences between 2 XMLS, for example, if i declare XML1 and XML2 as:

Solution 1:

The problem has two levels:

1. You concatenate values which can be NULL. Concatenating strings with + sign should always be aware of this and use ISNULL()-function, because one tiny NULL will let the result be NULL in total...
2. You compare from left to right and from right to left in one go.

In the following I show you a code which will create a function for easy reuse solving point 1)

In the final call you see, that I just call the function twice with the parameters swaped. Some differences will occur twice. If you want to get rid of them, you'll need high logic!

CREATE FUNCTION dbo.FindDifferencesInAttributes
(
     @XML1 XML
    ,@XML2 XML
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @RetVal VARCHAR(MAX)='';
    WITH ContarAtributos AS
    (
        SELECT LEN(CAST(@XML1 AS VARCHAR(MAX)))-LEN(REPLACE(CAST(@XML1 AS VARCHAR(MAX)),'=','')) AS X
    )
    , E1(N) AS(SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)) --10 ^ 1
    , E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b) -- 10 ^ 2 = 100 rows
    , CteTally AS
    (
        SELECT TOP((SELECT X FROM ContarAtributos)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS Nmbr
        FROM E2
    )
    ,AttributNames AS
    (
        SELECT Nmbr,@XML1.value('local-name((*/@*[sql:column("Nmbr")])[1])', 'varchar(max)') AS AttributName
        FROM CteTally
    )
    SELECT @RetVal=
    (
        SELECT '' + CASE WHEN ISNULL(@XML1.value('(*/@*[fn:local-name()=sql:column("AttributName")])[1]','varchar(max)'),'')
        <> ISNULL(@XML2.value('(*/@*[fn:local-name()=sql:column("AttributName")])[1]','varchar(max)') ,'')
        THEN AttributName + ',' + ISNULL(@XML1.value('(*/@*[fn:local-name()=sql:column("AttributName")])[1]','varchar(max)'),'#missing') + ',' + ISNULL(@XML2.value('(*/@*[fn:local-name()=sql:column("AttributName")])[1]','varchar(max)'),'#missing') + ';' ELSE '' END 
        FROM AttributNames
        FOR XML PATH('') 
    )

    RETURN ISNULL(@RetVal,'');
END
GO

DECLARE @XML1 XML= '<opr_tmp_manifest man_status="2" man_number="5" man_name="Bob"/>';
DECLARE @XML2 XML= '<opr_tmp_manifest man_status="1" man_number="5"/>';

SELECT dbo.FindDifferencesInAttributes(@XML1,@XML2) + '   ///   ' + dbo.FindDifferencesInAttributes(@XML2,@XML1)

this is the result:

man_status,2,1;man_name,Bob,#missing;   ///   man_status,1,2;

Share