SQLServer中的XML数据进行insert、update、delete操作实现代码

SQL Server 2005/2008增加了对XML数据的支持，同时也新增了几种操作XML的方法，本文主要以SQL Server 2008为例介绍如何对XML数据进行insert、update、delete

declare @XMLVar XML; 
SET @XMLVar= ' 

 
 
 
Bill Zack 
49.99 
 
 
 
Andrew Brust 
39.93 
 
 
 
Stephen Forte 
59.99 
 
 
 

1.XML.Modify(Insert)语句介绍 


A.利用as first,at last,before,after四个参数将元素插入指定的位置 
 代码如下:

set @XMLVar.modify( 
'insert  as first into (/catalog[1]/book[1])') 


set @XMLVar.modify( 
'insert  as last into (/catalog[1]/book[1])') 


set @XMLVar.modify( 
'insert  before (/catalog[1]/book[1]/author[1])') 

set @XMLVar.modify( 
'insert  after (/catalog[1]/book[1]/author[1])') 
SELECT @XMLVar.query('/catalog[1]/book[1]'); 
 
结果集为： 
 代码如下:

1:  
2:  
3:  
4:  
5: Bill Zack 
6:  
7: 49.99 
8:  
9:  
 


B.将多个元素插入文档中 
 代码如下:

--方法一：利用变量进行插入 
DECLARE @newFeatures xml; 
SET @newFeatures = N'; one element second element' 
SET @XMLVar.modify(' ) 
insert sql:variable("@newFeatures") 
into (/catalog[1]/book[1])' 

--方法二：直接插入 
set @XMLVar.modify(') 
insert (one element,second element) 
into (/catalog[1]/book[1]/author[1])' 
SELECT @XMLVar.query('/catalog[1]/book[1]'); 
 
结果集为： 
 代码如下:

1:  
2:  
3: Bill Zack 
4: one element 
5: second element 
6:  
7: 49.99 
8: one element 
9: second element 
10:  
 

C.将属性插入文档中 
 代码如下:

--使用变量插入 
declare @var nvarchar(10) = '变量插入' 
set @XMLVar.modify( 
'insert (attribute var {sql:variable("@var")})) 
into (/catalog[1]/book[1])' 


--直接插入 
set @XMLVar.modify( 
'insert (attribute name {"直接插入"})) 
into (/catalog[1]/book[1]/title[1])' 


--多值插入 
set @XMLVar.modify( 
'insert (attribute Id {"多值插入1"},attribute name {"多值插入2"}) ) 
into (/catalog[1]/book[1]/author[1])' 
SELECT @XMLVar.query('/catalog[1]/book[1]'); 
 
结果集为： 
 代码如下:

1:  
2:  
3: Bill Zack 
4: 49.99 
5:  
 


D.插入文本节点 
 代码如下:

set @XMLVar.modify( 
'insert text{"at first"} as first) 
into (/catalog[1]/book[1])' 
SELECT @XMLVar.query('/catalog[1]/book[1]'); 
 
结果集为： 
 代码如下:

1:  
2: at first 
3:  
4: Bill Zack 
5: 49.99 
6:  
 
注意：插入本文同样具体 as first，as last，before，after四种选项，可以参考A中的使用方法 



E.插入注释节点 
 代码如下:

set @XMLVar.modify( 
N'insert  
before (/catalog[1]/book[1]/title[1])' ) 
SELECT @XMLVar.query('/catalog[1]/book[1]'); 
 
结果集为： 
1:  
2:  
3:  
4: Bill Zack 
5: 49.99 
6:  
注意插入注释节点同样具体 as first，as last，before，after四种选项，可以参考A中的使用方法 



F.插入处理指令 
 代码如下:

set @XMLVar.modify( 
'insert  
before (/catalog[1]/book[1]/title[1])' ) 
SELECT @XMLVar.query('/catalog[1]/book[1]'); 
 
结果集为： 
1:  
2:  
3:  
4: Bill Zack 
5: 49.99 
6:  
注意插入处理指令同样具体 as first，as last，before，after四种选项，可以参考A中的使用方法 



G.根据 if 条件语句进行插入 
 代码如下:

set @XMLVar.modify( 
'insert 
if (/catalog[1]/book[1]/title[2]) then 
text{"this is a 1 step"} 
else ( text{"this is a 2 step"} ) 
into (/catalog[1]/book[1]/price[1])' ) 
SELECT @XMLVar.query('/catalog[1]/book[1]'); 
 
结果集为： 
1:  
2:  
3: Bill Zack 
4: 49.99this is a 2 step 
5:  



2.XML.Modify(delete)语句介绍 
 代码如下:

--删除属性 
set @XMLVar.modify('delete /catalog[1]/book[1]/@category') 


--删除节点 
set @XMLVar.modify('delete /catalog[1]/book[1]/title[1]') 


--删除内容 
set @XMLVar.modify('delete /catalog[1]/book[1]/author[1]/text()') 


--全部删除 
set @XMLVar.modify('delete /catalog[1]/book[2]') 

SELECT @XMLVar.query('/catalog[1]'); 
 
结果集为： 
 代码如下:

1:  
2:  
3:  
4: 49.99 
5:  
6:  
7:  
8: Stephen Forte 
9: 59.99 
10:  
11:  
 

3.XML.Modify(replace)语句介绍 
 代码如下:

--替换属性 
set @XMLVar.modify(N'replace value of(/catalog[1]/book[1]/@category) 
with ("替换属性")' ) 
--替换内容 
set @XMLVar.modify(N'replace value of(/catalog[1]/book[1]/author[1]/text()[1]) 
with("替换内容")' ) 
--条件替换 
set @XMLVar.modify(N'replace value of (/catalog[1]/book[2]/@category) 
with( 
if(count(/catalog[1]/book)>4) then 
"条件替换1" 
else 
"条件替换2")' ) 

SELECT @XMLVar.query('/catalog[1]'); 
[code] 
结果集为： 
[code] 
1:  
2:  
3:  
4: 替换内容 
5: 49.99 
6:  
7:  
8:  
9: Andrew Brust 
10: 39.93 
11:  
12:  
13:  
14: Stephen Forte 
15: 59.99 
16:  
17: