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>
