使用XML字段批量更新数据库

摘要：有时候我们想根据不同的where条件更新多条记录，一般就需要用多条语句，本文教你怎样用一次查询完成，但XML处理会让数据库进程CPU升高，请酌情使用。

-- 1、初始化临时表及原始数据
if   object_id ( ' tempdb..#t ' )  is   not   null
    drop   table  #t
if   object_id ( ' tempdb..#t2 ' )  is   not   null
    drop   table  #t2

create   table  #t(k  varchar ( 100 ),v  int )
insert   into  #t  values ( ' a ' , 1 )
insert   into  #t  values ( ' b ' , 2 )
select   *   from  #t
-- 输出如下
-- k    v
-- a-1
-- b-2

-- 2、准备批量更新的XML
DECLARE   @a   TABLE (data XML)
INSERT   @a   SELECT   '
<root>
    <i k= '' a ''  v= '' 3 ''  />
    <i k= '' b ''  v= '' 4 ''  />
</root> '



-- 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
     CROSS  APPLY
    (
         SELECT  id  =  t2.x.value( ' @v ' , ' int ' ) 
         FROM  a.data.nodes( ' //i ' )  AS  t2(x)
         where  t2.x.value( ' @k ' , ' varchar(100) ' )  =  b.id
    ) c
)  as  d
on  #t.k  =  d.k
-- 其中cte d的结果集应该如下
-- k    v
-- a-3
-- b-4

-- 4、验证原始表是否已经修改
select   *   from  #t
-- 输出如下
-- k    v
-- a-3
-- b-4