create Procedure [dbo].[UpdateDeliverQueueForXml]
@xml nvarchar(max)=N''--xml 字符串
--xml格式为
--<root>
--<DeliverQueue RowID=''1'' Status=''2'' Sendtime=''2009-05-26''/>
--</root>
as
set @xml=replace(@xml,'\','')
--将字符串插入XML中
DECLARE @a TABLE(data XML)
INSERT @a SELECT @xml
--将表变量a输出到一个临时结果集,并用来和原始表DeliverQueue join后更改原始表
--这里用到了带有from子句的update语句和cte类型
update DeliverQueue set DeliverQueue.Status = d.v,sendtime=d.j
from DeliverQueue inner join (
SELECT b.id k,c.id v,e.id j FROM @a a
CROSS APPLY
(
SELECT id = t.x.value('@RowID','int') FROM a.data.nodes('//DeliverQueue') AS t(x)
) b
CROSS APPLY
(
SELECT id = t2.x.value('@Status','int')
FROM a.data.nodes('//DeliverQueue') AS t2(x)
where t2.x.value('@RowID','int') = b.id
) c
CROSS APPLY
(
SELECT id = t3.x.value('@Sendtime','datetime')
FROM a.data.nodes('//DeliverQueue') AS t3(x)
where t3.x.value('@RowID','int') = b.id
) e
) as d
on DeliverQueue.RowID = d.k
--3、将表变量a输出到一个临时结果集,并用来和原始表#t join后更改原始表
--这里用到了带有from子句的update语句和cte类型
update #t set #t.v = d.v
from #t inner join (
SELECT b.id k,c.id v FROM @a a
CROSS APPLY
(
SELECT id = t.x.value('@k','varchar(100)') FROM a.data.nodes('//i') AS t(x)
) b