22,209
社区成员
发帖
与我相关
我的任务
分享
declare @xml varchar(max)
declare @x xml='
<Data Code="81494050004589840055" CorpOrderID="9900124924 " Actor="" ActDate="2016-03-17" CorpProductID="R055" CorpBatchNo="16020132" ProduceDate="2016-02-01" ToCorpID="000026481266"/>
<Data Code="81494050004589840055" CorpOrderID="9900124924 " Actor="" ActDate="2016-03-17" CorpProductID="R055" CorpBatchNo="16020132" ProduceDate="2016-02-01" ToCorpID="000026481266"/>
<Data Code="81494050004591106796" CorpOrderID="9900124924 " Actor="" ActDate="2016-03-17" CorpProductID="R055" CorpBatchNo="16020132" ProduceDate="2016-02-01" ToCorpID="000026481266"/>
'
select @xml=isnull(@xml,'')+t.x from (
select convert(varchar(max),t.c.query('.') ) as x,ROW_NUMBER()over(partition by convert(varchar,t.c.query('data(@Code)')) order by getdate()) as rn
from @x.nodes('Data')t(c)
) as t where t.rn=1
select @xml
/*
<Data Code="81494050004589840055" CorpOrderID="9900124924 " Actor="" ActDate="2016-03-17" CorpProductID="R055" CorpBatchNo="16020132" ProduceDate="2016-02-01" ToCorpID="000026481266"/><Data Code="81494050004591106796" CorpOrderID="9900124924 " Actor="" ActDate="2016-03-17" CorpProductID="R055" CorpBatchNo="16020132" ProduceDate="2016-02-01" ToCorpID="000026481266"/>
*/
declare @x xml='<Data Code="81494050004589840055" CorpOrderID="9900124924 " Actor="" ActDate="2016-03-17" CorpProductID="R055" CorpBatchNo="16020132" ProduceDate="2016-02-01" ToCorpID="000026481266"/>
<Data Code="81494050004591106796" CorpOrderID="9900124924 " Actor="" ActDate="2016-03-17" CorpProductID="R055" CorpBatchNo="16020132" ProduceDate="2016-02-01" ToCorpID="000026481266"/>
'
select distinct t.c.value('@Code','varchar(100)') from @x.nodes('Data')t(c)