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

madStone_l 2018-06-22 09:11:54
两个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中的元素会存在变化,不能固定判断。当两个变量之间的元素相同的。
...全文
213 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
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
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2018-06-22 09:11
社区公告
暂无公告