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四個參數(shù)將元素插入指定的位置 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.插入文本節(jié)點 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.插入注釋節(jié)點 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> 注意插入注釋節(jié)點同樣具體 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.根據(jù) 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') --刪除節(jié)點 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>
