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

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 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]');结果集为：

   1:  <book category="ITPro">   2:    <first name="at first" />   3:    <title>Windows Step By Step</title>   4:    <before name="before" />   5:    <author>Bill Zack</author>   6:    <after name="after" />   7:    <price>49.99</price>   8:    <last name="at last" />   9:  </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:  <book category="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 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:      <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:    <book category="替换属性">   3:      <title>Windows Step By Step</title>   4:      <author>替换内容</author>   5:      <price>49.99</price>   6:    </book>   7:    <book category="条件替换2">   8:      <title>Developing ADO .NET</title>   9:      <author>Andrew Brust</author>  10:      <price>39.93</price>  11:    </book>  12:    <book category="ITPro">  13:      <title>Windows Cluster Server</title>  14:      <author>Stephen Forte</author>  15:      <price>59.99</price>  16:    </book>  17:  </catalog>

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/tjvictor/archive/2009/07/21/4368496.aspx