一家专做有机蔬菜的网站,创建一个网站买卖,郑佩佩 最新消息,佛山有什么网站SQL Server 2005/2008增加了对XML数据的支持#xff0c;同时也新增了几种操作XML的方法#xff0c;本文主要以SQL Server 2008为例介绍如何对XML数据进行insert、update、delete。 SQL Server中新增加了XML.Modify()方法#xff0c;分别为xml.modify(insert),xml.modify(del… SQL Server 2005/2008增加了对XML数据的支持同时也新增了几种操作XML的方法本文主要以SQL Server 2008为例介绍如何对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 categoryITPro titleWindows Step By Step/title authorBill Zack/author price49.99/price /book book categoryDeveloper titleDeveloping ADO .NET/title authorAndrew Brust/author price39.93/price /book book categoryITPro titleWindows Cluster Server/title authorStephen Forte/author price59.99/price /book /catalog 1.XML.Modify(Insert)语句介绍 A.利用as first,at last,before,after四个参数将元素插入指定的位置 set XMLVar.modify( insert first nameat first / as first into (/catalog[1]/book[1])) set XMLVar.modify( insert last nameat last/ as last into (/catalog[1]/book[1])) set XMLVar.modify( insert before namebefore/ before (/catalog[1]/book[1]/author[1])) set XMLVar.modify( insert after nameafter/ after (/catalog[1]/book[1]/author[1])) SELECT XMLVar.query(/catalog[1]/book[1]); 结果集为 1: book categoryITPro 2: first nameat first / 3: titleWindows Step By Step/title 4: before namebefore / 5: authorBill Zack/author 6: after nameafter / 7: price49.99/price 8: last nameat last / 9: /book B.将多个元素插入文档中 --方法一利用变量进行插入 DECLARE newFeatures xml; SET newFeatures N; firstone element/first secondsecond element/second SET XMLVar.modify( ) insert sql:variable(newFeatures) into (/catalog[1]/book[1]) --方法二直接插入 set XMLVar.modify() insert (firstone element/first,secondsecond element/second) into (/catalog[1]/book[1]/author[1]) SELECT XMLVar.query(/catalog[1]/book[1]); 结果集为 1: book categoryITPro 2: titleWindows Step By Step/title 3: authorBill Zack 4: firstone element/first 5: secondsecond element/second 6: /author 7: price49.99/price 8: firstone element/first 9: secondsecond 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 categoryITPro var变量插入 2: title name直接插入Windows Step By Step/title 3: author Id多值插入1 name多值插入2Bill Zack/author 4: price49.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 categoryITPro 2: at first 3: titleWindows Step By Step/title 4: authorBill Zack/author 5: price49.99/price 6: /book 注意插入本文同样具体 as firstas lastbeforeafter四种选项可以参考A中的使用方法 E.插入注释节点 set XMLVar.modify( insert !--插入评论--) before (/catalog[1]/book[1]/title[1]) SELECT XMLVar.query(/catalog[1]/book[1]); 结果集为 1: book categoryITPro 2: !--插入评论-- 3: titleWindows Step By Step/title 4: authorBill Zack/author 5: price49.99/price 6: /book 注意插入注释节点同样具体 as firstas lastbeforeafter四种选项可以参考A中的使用方法 F.插入处理指令 set XMLVar.modify( insert ?Program Instructions.exe ?) before (/catalog[1]/book[1]/title[1]) SELECT XMLVar.query(/catalog[1]/book[1]);结果集为 1: book categoryITPro 2: ?Program Instructions.exe ? 3: titleWindows Step By Step/title 4: authorBill Zack/author 5: price49.99/price 6: /book 注意插入处理指令同样具体 as firstas lastbeforeafter四种选项可以参考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 categoryITPro 2: titleWindows Step By Step/title 3: authorBill Zack/author 4: price49.99this is a 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: price49.99/price 5: /book 6: book categoryITPro 7: titleWindows Cluster Server/title 8: authorStephen Forte/author 9: price59.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: book category替换属性 3: titleWindows Step By Step/title 4: author替换内容/author 5: price49.99/price 6: /book 7: book category条件替换2 8: titleDeveloping ADO .NET/title 9: authorAndrew Brust/author 10: price39.93/price 11: /book 12: book categoryITPro 13: titleWindows Cluster Server/title 14: authorStephen Forte/author 15: price59.99/price 16: /book 17: /catalog 转载于:https://blog.51cto.com/cnming/662362
