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:

<opr_tmp_manifest man_status="2" man_number="5"/>
<opr_tmp_manifest man_status="1" man_number="5"/>

Then the response I get is:

  • SSRS served files named Resource.aspx, try to open, receive message file with same name already open
  • Computed column based on other computed column within insert procedure
  • SQL Server “Maximum Server Memory” - seems to have no effect on Virtual Size
  • Cannot bulk load because the file could not be opened. Operating System Error Code 3
  • MS SQL Date Only Without Time
  • SQL Server 2012 Import CSV to mapped Columns
  • "MAN_STATUS,2,1;"
    

    So the Format of the response is “name_of_tag,old_value,new_value”
    Another example with 2 differences, if given:

    <opr_tmp_manifest man_status="2" man_name="Bob" man_number="5"/>
    <opr_tmp_manifest man_status="1" man_name="John" man_number="5"/>
    

    Response:

    "MAN_STATUS,2,1;MAN_NAME,Bob,John"
    

    So multiple differences get separated by a semicolon

    The problem is, that if any of both XMLs have a different tag, it won’t show up the difference, I’m not sure how to do this with my current code so it takes into consideration if a tag was added or removed.

    This is my current Code:

    DECLARE @XML1 XML= '<opr_tmp_manifest man_status="2" man_name="Bob" man_number="5"/>';
            DECLARE @XML2 XML= '<opr_tmp_manifest man_status="1" man_name="John" man_number="5"/>';
    
        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
        (
            SELECT '' + CASE WHEN @XML1.value('(*/@*[fn:local-name()=sql:column("AttributName")])[1]','varchar(max)')
            <> @XML2.value('(*/@*[fn:local-name()=sql:column("AttributName")])[1]','varchar(max)') 
            THEN AttributName + ',' + @XML1.value('(*/@*[fn:local-name()=sql:column("AttributName")])[1]','varchar(max)') + ',' + @XML2.value('(*/@*[fn:local-name()=sql:column("AttributName")])[1]','varchar(max)') + ';' ELSE '' END 
            FROM AttributNames
            FOR XML PATH('') 
        ) AS diff
    

    EDIT:
    For Example if given:

    <opr_tmp_manifest man_status="2" man_number="5" man_name="Bob"/>
    <opr_tmp_manifest man_status="1" man_number="5"/>
    

    I’d like to have: “man_status,2,1;man_name,Bob,”

    And if given:

    <opr_tmp_manifest man_status="1" man_number="5"/>
    <opr_tmp_manifest man_status="2" man_number="5" man_name="Bob"/>
    

    I’d like to have: “man_status,2,1;man_name,,Bob”

  • Finding the differences only between 2 strings in SQL
  • One Solution collect form web for “Identifying new tags between 2 XML in SQL”

    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;
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.