有朋友问到一个问题:
一张表里有两个XML字段类型的字段,存放旧值,和更新后的值,
例如:
<OLDVALUE>
<H_Action>030</H_Action>
<D_Action>030</D_Action>
<OrderCompany>00220</OrderCompany>
<OrderNumber>10004035</OrderNumber>
<OrderType>SO</OrderType>
<LineNumber>10.100</LineNumber>
</OLDVALUE>
<NEWVALUE>
<H_Action>040</H_Action>
<D_Action>040</D_Action>
<OrderCompany>00220</OrderCompany>
<OrderNumber>10004035</OrderNumber>
<OrderType>SO</OrderType>
<LineNumber>10.100</LineNumber>
<LineType>CS</LineType>
<LoadNumber>8811</LoadNumber>
</NEWVALUE>
现在想通过一 个xquery比较出两个值的不同之处,大家有没有好的方法或建议?
其实,用Sql2005/2008的xQuery很简单。
(pkid int primary key ,
OLDVALUE xml,
NEWVALUE xml
)
go
select * from testXML
truncate table testxml
go
insert into testXML
select 1 , ' <H_Action>030</H_Action>
<D_Action>030</D_Action>
<OrderCompany>00220</OrderCompany>
<OrderNumber>10004035</OrderNumber>
<OrderType>SO</OrderType>
<LineNumber>10.100</LineNumber> '
, ' <H_Action>040</H_Action>
<D_Action>040</D_Action>
<OrderCompany>00220</OrderCompany>
<OrderNumber>10004035</OrderNumber>
<OrderType>SO</OrderType>
<LineNumber>10.100</LineNumber>
<LineType>CS</LineType>
<LoadNumber>8811</LoadNumber> '
go
比较OLDVALUE字段的H_Action元素的值与NewVALUE字段的H_Action元素的值不同的记录。
NEWVALUE.query( ' data(/H_Action) ' ) as NewH_Action
from testXML
where cast ( OLDVALUE.query( ' data(/H_Action) ' ) as nvarchar ( 100 )) <>
cast ( NEWVALUE.query( ' data(/H_Action) ' ) as nvarchar ( 100 ))
-- 结果:
-- OldH_Action NewH_Action
-- 030 040
更多XQuery查询,请看MSDN:
http://msdn.microsoft.com/zh-cn/library/ms190936%28SQL.90%29.aspx