SQL Server中的XML数据进行insert、update、delete

答案：SQL Server中新增加了XML.Modify()方法，分别为xml.modify(insert),xml.modify(delete),xml.modify(replace)对应XML的插入，删除和修改操作。 
本文以下面XML为例，对三种DML进行说明： 
declare 
@XMLVar xml = ' 
<catalog> 
<book category="ITPro"> 
<title>Windows Step By Step</title> 
<author>Bill Zack</author> 
<price>49.99</price> 
</book> 
<book category="Developer"> 
<title>Developing ADO .NET</title> 
<author>Andrew Brust</author> 
<price>39.93</price> 
</book> 
<book category="ITPro"> 
<title>Windows Cluster Server</title> 
<author>Stephen Forte</author> 
<price>59.99</price> 
</book> 
</catalog> 
' 
1.XML.Modify(Insert)语句介绍 
A.利用as first,at last,before,after四个参数将元素插入指定的位置 
set 
@XMLVar.modify 
( 
'insert <first name="at first" /> as first into (/catalog[1]/book[1])' 
) 
set 
@XMLVar.modify 
( 
'insert <last name="at last"/> as last into (/catalog[1]/book[1])' 
) 
set 
@XMLVar.modify 
( 
'insert <before name="before"/> before (/catalog[1]/book[1]/author[1])' 
) 
set 
@XMLVar.modify 
( 
'insert <after name="after"/> after (/catalog[1]/book[1]/author[1])' 
) 
SELECT 
@XMLVar.query('/catalog[1]/book[1]' 
); 
结果集为： 
<book category="ITPro" 
> 
<first name="at first" 
/> 
<title>Windows Step By Step</title> 
<before name="before" 
/> 
<author>Bill Zack</author> 
<after name="after" 
/> 
<price>49.99</price> 
<last name="at last" 
/> 
</book> 
B.将多个元素插入文档中 
--方法一：利用变量进行插入 
DECLARE @newFeatures xml; 
SET @newFeatures = N' 

<first>one element</first> 
<second>second element</second>' 
SET @XMLVar.modify(' 
) 
insert sql:variable("@newFeatures") 
into (/catalog[1]/book[1])' 
--方法二：直接插入 
set @XMLVar.modify(' 
) 
insert (<first>one element</first>,<second>second element</second>) 
into (/catalog[1]/book[1]/author[1])' 
SELECT @XMLVar.query('/catalog[1]/book[1]' 
); 
结果集为： 
1: 
< 
book 
category 
="ITPro" 
> 
2: 
< 
title 
> 
Windows Step By Step</ 
title 
> 
3: 
< 
author 
> 
Bill Zack 
4: 
< 
first 
> 
one element</ 
first 
> 
5: 
< 
second 
> 
second element</ 
second 
> 
6: 
</ 
author 
> 
7: 
< 
price 
> 
49.99</ 
price 
> 
8: 
< 
first 
> 
one element</ 
first 
> 
9: 
< 
second 
> 
second element</ 
second 
> 
10: 
</ 
book 
> 
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: 
<book category="ITPro" 
var="变量插入" 
> 
2: 
<title name="直接插入" 
>Windows Step By Step</title> 
3: 
<author Id="多值插入1" 
name="多值插入2" 
>Bill Zack</author> 
4: 
<price>49.99</price> 
5: 
</book> 
D.插入文本节点 
set 
@XMLVar.modify 
( 
'insert text{"at first"} as first 
) 
into (/catalog[1]/book[1])' 
SELECT 
@XMLVar.query('/catalog[1]/book[1]' 
); 
结果集为： 
1: 
< 
book 
category 
="ITPro" 
> 
2: 
at first 
3: 
< 
title 
> 
Windows Step By Step</ 
title 
> 
4: 
< 
author 
> 
Bill Zack</ 
author 
> 
5: 
< 
price 
> 
49.99</ 
price 
> 
6: 
</ 
book 
> 
注意：插入本文同样具体 as first，as last，before，after四种选项，可以参考A中的使用方法 
E.插入注释节点 
set @XMLVar.modify( 
'insert <!--插入评论--> 
) 
before (/catalog[1]/book[1]/title[1])' 
SELECT @XMLVar.query('/catalog[1]/book[1]' 
); 
结果集为： 
1: 
<book category="ITPro" 
> 
2: 
<!--插入评论--> 
3: 
<title>Windows Step By Step</title> 
4: 
<author>Bill Zack</author> 
5: 
<price>49.99</price> 
6: 
</book> 
注意插入注释节点同样具体 as first，as last，before，after四种选项，可以参考A中的使用方法 
F.插入处理指令 
set @XMLVar.modify( 
'insert <?Program "Instructions.exe" ?> 
) 
before (/catalog[1]/book[1]/title[1])' 
SELECT @XMLVar.query('/catalog[1]/book[1]' 
); 
结果集为： 
1: <bookcategory="ITPro"> 
2: <?Program"Instructions.exe"?> 
3: <title>Windows Step By Step</title> 
4: <author>Bill Zack</author> 
5: <price>49.99</price> 
6: </book> 
注意插入处理指令同样具体 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: <book category="ITPro"> 
2: <title>Windows Step By Step</title> 
3: <author>Bill Zack</author> 
4: <price>49.99this isa 2 step</price> 
5: </book> 
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: <catalog> 
2: <book> 
3: <author /> 
4: <price>49.99</price> 
5: </book> 
6: <book category="ITPro"> 
7: <title>Windows Cluster Server</title> 
8: <author>Stephen Forte</author> 
9: <price>59.99</price> 
10: </book> 
11: </catalog> 
3.XML.Modify(replace)语句介绍 
--替换属性 
set @XMLVar.modify('replace value of(/catalog[1]/book[1]/@category)) 
with ("替换属性")' 
--替换内容 
set @XMLVar.modify('replace value of(/catalog[1]/book[1]/author[1]/text()[1])) 
with("替换内容")' 
--条件替换 
set @XMLVar.modify('replace value of (/catalog[1]/book[2]/@category)) 
with( 
if(count(/catalog[1]/book)>4) then 
"条件替换1" 
else 
"条件替换2")' 
SELECT @XMLVar.query('/catalog[1]' 
); 
结果集为： 
1: <catalog> 
2: <bookcategory="替换属性"> 
3: <title>Windows Step By Step</title> 
4: <author>替换内容</author> 
5: <price>49.99</price> 
6: </book> 
7: <bookcategory="条件替换2"> 
8: <title> 
Developing ADO .NET</title> 
9: 
<author> 
Andrew Brust</author> 
10: <price>39.93</price> 
11: </book> 
12: <bookcategory="ITPro"> 
13: <title>Windows Cluster Server</title> 
14: <author>Stephen Forte</author> 
15: <price>59.99</price> 
16: </book> 
17: </catalog>

上一个：SQL Sever 2005 Express 安装失败解决办法
下一个：sql rollup用法 小计汇总