确定2 XML在SQL之间的新标签 [英] Identifying new tags between 2 XML in SQL

问题描述

我使用SQL找到2 XMLS之间的差异，例如，如果我声明XML1和XML2为：

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:

"MAN_STATUS,2,1;"

所以响应的格式为name_of_tag，OLD_VALUE，NEW_VALUE
用2分歧另一实例中，如果给定的：

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"/>

响应：

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

那么多的差异得到由分号隔开

So multiple differences get separated by a semicolon

问题是，如果任何两个XML的有不同的标记，它不会显示出来的差异，我不知道如何与我目前的code做到这一点，因此考虑到如果标签被添加或删除。

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.

这是我目前的code：

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

编辑：
例如，如果给定的：

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"/>

我想有：man_status，2,1; man_name，鲍勃，

I'd like to have: "man_status,2,1;man_name,Bob,"

如果给定的：

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

我想有：man_status，2,1; man_name ,,鲍勃

I'd like to have: "man_status,2,1;man_name,,Bob"

推荐答案

这个问题有两个层次：

1. 您串联它可以是NULL值。连接字符串与 + 标志应该始终意识到这一点，并使用 ISNULL（）功能全，因为一个小小的 NULL 将让结果是 NULL 总...


2. 您从左至右和从右比较一气呵成到左。

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.

在下面我告诉你一个code，这将创建一个易于重用的函数求解1点）

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

在最后叫你看，我只是调用该函数两次swaped的参数。有些差异将出现两次。如果你想摆脱他们，你需要高逻辑！

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)

这是结果：

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

这篇关于确定2 XML在SQL之间的新标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！