22,207
社区成员
发帖
与我相关
我的任务
分享Sql Server 对比两个XML变量的数据差异，冰天雪地跪求100分思路
两个XML变量格式如下：
其中 GID 为主键 GUID，OpTime为操作时间。
目前需要对比出差异，如上差异的GUID是：11CB0F35-1979-469A-8D88-000040479A13，原值是：2016-09-30T14:04:04.850，新值是：2018-01-05T13:58:38.400
并且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中的元素会存在变化，不能固定判断。当两个变量之间的元素相同的。
...全文
请发表友善的回复…
发表回复
madStone_l 2018-06-26
- 打赏
- 举报
XML完整格式如下：
一条XML数据中有67个属性，一共500条。@OrgXml 和 @NewXml 两个变量均为500条XML，一共1000条XML。
应受CSDN回复字符数限制，不能全部发送。可以粘贴复制，模拟500条数据
<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
- 打赏
- 举报
您好，感谢您的指导。您的这个方法完全没有问题，可以达到想要的效果。当时在解析对比500条XML数据的时候，查询超时。等待1分钟，仍然无法出现结果。
后来我尝试使用动态语句拼接执行的方式。可以达到想要的效果，性能也有所提升500条XML数据，能在5秒出结果。但是这个5秒还是太长，能控制在2秒以内就好。查看了实际执行计划，都是解析XML耗时，请大神指导还有优化空间吗？
madStone_l 2018-06-22
- 打赏
- 举报
兄弟，这种情况不行。其实我的业务是在多个表中创建触发器，然后在触发器中将 Inserted 和 Deleted 转换为两个Xml变量，然后调用公共的存储过程，对比这两个xml变量之间的值差异，从而实现记录表数据变化。
所有xml格式处理GID是主键，每个表都有，其他列都是不固定的，为方便大家理解可能是以下几种格式：
所有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.gidmadStone_l 2018-06-22
- 打赏
- 举报
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
