Sql Server 对比两个XML变量的数据差异，冰天雪地跪求100分思路

2018-06-22 09:11:54 368 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL SERVER中XML查询：FOR XML指定EXPLICIT 在SQL SERVER中，XML查询可以指定RAW，AUTO，EXPLICIT，PATH。本文用一些实例介绍SQL SERVER中指定EXPLICIT的XML查询。阅读详情

两个XML变量格式如下：



DECLARE @OrgXml XML = '<dbo.WorkOrderStrokeTask GID="1E8FE45C-09BC-4C88-8370-00002677704E" OpTime="2016-09-30T14:04:04.850" />

<dbo.WorkOrderStrokeTask GID="217FF216-C96E-4F9D-A719-000032C8B11A" OpTime="2018-02-08T10:50:42.090"  />

<dbo.WorkOrderStrokeTask GID="11CB0F35-1979-469A-8D88-000040479A13" OpTime="2018-01-04T13:58:58.483"  />' ,

        @NewXml XML = '<dbo.WorkOrderStrokeTask GID="1E8FE45C-09BC-4C88-8370-00002677704E" OpTime="2016-09-30T14:04:04.850" />

<dbo.WorkOrderStrokeTask GID="217FF216-C96E-4F9D-A719-000032C8B11A" OpTime="2018-02-08T10:50:42.090"  />

<dbo.WorkOrderStrokeTask GID="11CB0F35-1979-469A-8D88-000040479A13" OpTime="2018-01-05T13:58:38.400"  />';

其中 GID 为主键 GUID，OpTime为操作时间。
目前需要对比出差异，如上差异的GUID是：11CB0F35-1979-469A-8D88-000040479A13，原值是：2016-09-30T14:04:04.850，新值是：2018-01-05T13:58:38.400

并且Xml中的元素会存在变化，不能固定判断。当两个变量之间的元素相同的。

...全文

【GNSS数据处理】赫尔默特(helmert)方差分量估计解析及代码实现一、背景 helmert方差分量估计是方差-协方差分量估计的一个特例，在近现代测量平差中，我们处理的数据有明显的变化，数据序列中包括粗差，数据类型不再单一等问题。当我们遇到数据类型两类及以上时，可以考虑使用赫尔默特方差分量估计，应用的前提条件是不同类别的数据需要相互独立，这样我们的权阵就是对角阵。二、引入我们先用王者荣耀这款游戏引入一下，王者可是撩妹利器，哈哈，就像你有两部手机（安卓和苹果），带妹的时候大概率会用苹果，因为游戏体验方面苹果要优于安卓，带妹的时候不掉帧，不卡顿，不闪退，要操作有操作，有皮肤阅读详情

madStone_l

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

madStone_l 2018-06-26

打赏
举报

XML完整格式如下：

<dbo.WorkOrderStrokeTask GID="1E8FE45C-09BC-4C88-8370-00002677704E" StrokeGID="73D53AE3-C0D9-4124-BA30-BE6067F65729" TaskNo="20160929331790" VehicleGID="8366865A-9D7D-4FA5-AF7F-3B0177A37F73" VehicleCode="粤T41603" VehicleSeatCount="51" Driver1GID="95C3D27F-BC55-4B5B-B481-A83971CA79FA" Driver1Name="梁子财" OperGID="5BC4548A-8CC6-4EAC-8F1C-29041AA519B3" OperCode="1121" OperName="李炳" OpTime="2016-09-30T14:04:04.850" ………………

一条XML数据中有67个属性，一共500条。@OrgXml 和 @NewXml 两个变量均为500条XML，一共1000条XML。

应受CSDN回复字符数限制，不能全部发送。可以粘贴复制，模拟500条数据

madStone_l 2018-06-26

打赏
举报

动态拼接的执行语句如下：



DECLARE @TSql NVARCHAR(MAX) = '' ,@CaseSql NVARCHAR(MAX) = '';



SELECT @TSql += N'[' + A.ElKey + N'] = T.C.value(''(@' + A.ElKey+ N')[1]'', ''VARCHAR(MAX)''),' ,

       @CaseSql += N'CASE WHEN  A.[' + A.ElKey + N'] <> ' + N'B.[' + A.ElKey+ N'] THEN  ''' + A.ElKey + N'：'' +  A.[' + A.ElKey+ N'] + ''→'' + B.[' + A.ElKey + N'] ELSE '''' END +'

FROM ( SELECT DISTINCT ElKey = T.C.value('local-name(.)', 'VARCHAR(MAX)') FROM   @OrgXml.nodes('/*/@*') T(C) ) AS A;



SET @TSql = LEFT(@TSql, LEN(@TSql) - 1);SET @CaseSql = LEFT(@CaseSql, LEN(@CaseSql) - 1);



SET @TSql = N'SELECT * FROM ( SELECT A.GID,ChangeStr = ' + @CaseSql+ N' FROM ( SELECT ' + @TSql+ N' FROM @OrgXml.nodes(''/*'') T(C) ) AS A INNER JOIN (SELECT '+ @TSql+ N' FROM @NewXml.nodes(''/*'') T(C) ) AS B ON A.GID = B.GID) AS C WHERE C.ChangeStr <> ''''';



EXEC sys.sp_executesql @statement = @TSql , @params = N'@OrgXml XML,@NewXml XML' , @OrgXml = @OrgXml ,@NewXml = @NewXml;

madStone_l 2018-06-26

打赏
举报

引用 6 楼 zjcxc 的回复:

如果每个列都要单独比较差异，那么可以考虑这样写：

select 差异 = CASE 

        WHEN A.gid IS NULL THEN '@OrgXml 中不存在'

        WHEN B.gid IS NULL THEN '@NewXml 中不存在'

        WHEN A.value <> B.value THEN 'Value 不同'

        ELSE ''

    END

    ,*

from(

SELECT

	T.C.value('../@GID', 'uniqueidentifier') as gid,

	T.c.value('local-name(.)', 'sysname') as name,

	T.C.value('.', 'nvarchar(max)') as value

FROM   @OrgXml.nodes('/*/@*[local-name() != "GID"]') T(C)

 ) a full join(

 SELECT

	T.C.value('../@GID', 'uniqueidentifier') as gid,

	T.c.value('local-name(.)', 'sysname') as name,

	T.C.value('.', 'nvarchar(max)') as value

FROM   @NewXml.nodes('/*/@*[local-name() != "GID"]') T(C)

) b on a.gid=b.gid and a.name = b.name

您好，感谢您的指导。您的这个方法完全没有问题，可以达到想要的效果。当时在解析对比500条XML数据的时候，查询超时。等待1分钟，仍然无法出现结果。

后来我尝试使用动态语句拼接执行的方式。可以达到想要的效果，性能也有所提升500条XML数据，能在5秒出结果。但是这个5秒还是太长，能控制在2秒以内就好。查看了实际执行计划，都是解析XML耗时，请大神指导还有优化空间吗？

madStone_l 2018-06-22

打赏
举报

引用 3 楼 zjcxc 的回复:

select 差异 = CASE 

		WHEN A.gid IS NULL THEN '@OrgXml 中不存在'

		WHEN B.gid IS NULL THEN '@NewXml 中不存在'

		WHEN A.ClassTime <> B.ClassTime THEN 'ClassTime 不同'

		ELSE ''

	END

	,*

from(

SELECT T.C.value('@GID', 'uniqueidentifier') as gid,

       T.C.value('@ClassTime', 'time') as ClassTime 

FROM   @OrgXml.nodes('/*') T(C)

 ) a full join(

SELECT T.C.value('@GID', 'uniqueidentifier') as gid,

       T.C.value('@ClassTime', 'time') as ClassTime 

FROM   @NewXml.nodes('/*') T(C)

) b on a.gid=b.gid

兄弟，这种情况不行。其实我的业务是在多个表中创建触发器，然后在触发器中将 Inserted 和 Deleted 转换为两个Xml变量，然后调用公共的存储过程，对比这两个xml变量之间的值差异，从而实现记录表数据变化。
所有xml格式处理GID是主键，每个表都有，其他列都是不固定的，为方便大家理解可能是以下几种格式：



<dbo.LineClassTicketDetail GID="9087C33B-FE9F-46FF-A586-00000360C53B" OpTime="2018-05-25T15:23:51" ClassDate="2018-05-25T00:00:00" ClassTime="18:15" />



<dbo.Line GID="1C76A3D2-2EB7-4A80-9C76-000B1D0B84E0" LineCode="2004" LineName="英德大镇--罗冲围" />

zjcxc 2018-06-22

打赏
举报

select 差异 = CASE 

		WHEN A.gid IS NULL THEN '@OrgXml 中不存在'

		WHEN B.gid IS NULL THEN '@NewXml 中不存在'

		WHEN A.ClassTime <> B.ClassTime THEN 'ClassTime 不同'

		ELSE ''

	END

	,*

from(

SELECT T.C.value('@GID', 'uniqueidentifier') as gid,

       T.C.value('@ClassTime', 'time') as ClassTime 

FROM   @OrgXml.nodes('/*') T(C)

 ) a full join(

SELECT T.C.value('@GID', 'uniqueidentifier') as gid,

       T.C.value('@ClassTime', 'time') as ClassTime 

FROM   @NewXml.nodes('/*') T(C)

) b on a.gid=b.gid

madStone_l 2018-06-22

打赏
举报

引用 1 楼 madStone_l 的回复:

并且Xml中的元素会存在变化，不能固定判断。当两个变量之间的元素相同的。相当于下次两个XML变量格式可能会是如下这种：



DECLARE @OrgXml XML = '<dbo.LineDayClass GID="05FF8AFC-5FE1-4ED1-9BDE-000001B1AC37" ClassTime="10:30" />

<dbo.LineDayClass GID="3733F943-CEB2-45E1-9A9B-00005E00FE0F" ClassTime="14:00" />

<dbo.LineDayClass GID="5F083963-AD3F-4B0D-A06A-0000B569E94A" ClassTime="16:55" />' ,

        @NewXml XML = '<dbo.LineDayClass GID="05FF8AFC-5FE1-4ED1-9BDE-000001B1AC37" ClassTime="10:30" />

<dbo.LineDayClass GID="3733F943-CEB2-45E1-9A9B-00005E00FE0F" ClassTime="14:00" />

<dbo.LineDayClass GID="5F083963-AD3F-4B0D-A06A-0000B569E94A" ClassTime="17:55" />';

我能想到的方法如下：



SELECT T.C.value('.', 'VARCHAR(MAX)') ,

       T.C.value('local-name(.)', 'VARCHAR(MAX)') 

FROM   @OrgXml.nodes('/*/@*') T(C);



SELECT T.C.value('.', 'VARCHAR(MAX)') ,

       T.C.value('local-name(.)', 'VARCHAR(MAX)')

FROM   @NewXml.nodes('/*/@*') T(C);

当不知道怎么对比这两个表的数据。求大神指导

madStone_l 2018-06-22

打赏
举报

并且Xml中的元素会存在变化，不能固定判断。当两个变量之间的元素相同的。相当于下次两个XML变量格式可能会是如下这种：



DECLARE @OrgXml XML = '<dbo.LineDayClass GID="05FF8AFC-5FE1-4ED1-9BDE-000001B1AC37" ClassTime="10:30" />

<dbo.LineDayClass GID="3733F943-CEB2-45E1-9A9B-00005E00FE0F" ClassTime="14:00" />

<dbo.LineDayClass GID="5F083963-AD3F-4B0D-A06A-0000B569E94A" ClassTime="16:55" />' ,

        @NewXml XML = '<dbo.LineDayClass GID="05FF8AFC-5FE1-4ED1-9BDE-000001B1AC37" ClassTime="10:30" />

<dbo.LineDayClass GID="3733F943-CEB2-45E1-9A9B-00005E00FE0F" ClassTime="14:00" />

<dbo.LineDayClass GID="5F083963-AD3F-4B0D-A06A-0000B569E94A" ClassTime="17:55" />';

我能想到的方法如下：



SELECT T.C.value('.', 'VARCHAR(MAX)') ,

       T.C.value('local-name(.)', 'VARCHAR(MAX)') 

FROM   @OrgXml.nodes('/*/@*') T(C);



SELECT T.C.value('.', 'VARCHAR(MAX)') ,

       T.C.value('local-name(.)', 'VARCHAR(MAX)')

FROM   @NewXml.nodes('/*/@*') T(C);

当不知道怎么对比这两个表的数据。求大神指导

zjcxc 2018-06-22

打赏
举报

如果每个列都要单独比较差异，那么可以考虑这样写：

select 差异 = CASE 

        WHEN A.gid IS NULL THEN '@OrgXml 中不存在'

        WHEN B.gid IS NULL THEN '@NewXml 中不存在'

        WHEN A.value <> B.value THEN 'Value 不同'

        ELSE ''

    END

    ,*

from(

SELECT

	T.C.value('../@GID', 'uniqueidentifier') as gid,

	T.c.value('local-name(.)', 'sysname') as name,

	T.C.value('.', 'nvarchar(max)') as value

FROM   @OrgXml.nodes('/*/@*[local-name() != "GID"]') T(C)

 ) a full join(

 SELECT

	T.C.value('../@GID', 'uniqueidentifier') as gid,

	T.c.value('local-name(.)', 'sysname') as name,

	T.C.value('.', 'nvarchar(max)') as value

FROM   @NewXml.nodes('/*/@*[local-name() != "GID"]') T(C)

) b on a.gid=b.gid and a.name = b.name

zjcxc 2018-06-22

打赏
举报

select 差异 = CASE 

        WHEN A.gid IS NULL THEN '@OrgXml 中不存在'

        WHEN B.gid IS NULL THEN '@NewXml 中不存在'

        WHEN A.value <> B.value THEN 'ClassTime 不同'

        ELSE ''

    END

    ,*

from(

SELECT T.C.value('@GID', 'uniqueidentifier') as gid,

       CONVERT(nvarchar(max), T.c.query('<v>{./@*[local-name() != "GID"]}</v>')) as value 

FROM   @OrgXml.nodes('/*') T(C)

 ) a full join(

SELECT T.C.value('@GID', 'uniqueidentifier') as gid,

       CONVERT(nvarchar(max), T.c.query('<v>{./@*[local-name() != "GID"]}</v>')) as value 

FROM   @NewXml.nodes('/*') T(C)

) b on a.gid=b.gid

Sql Server 对比两个XML变量的数据差异，冰天雪地跪求100分思路

相关推荐