sql添加字段:SQL对Xml字段的操作

、前言
    SQL Server 2005 引入了种称为 XML 本机数据类型用户可以创建这样表它在关系列的外还有个或多个 XML 类型列；此外还允许带有变量和参数为了更好地支持 XML 模型特征(例如文档顺序和递归结构)XML 值以内部格式存储为大型 2进制对象 (BLOB)
   用户将个XML数据存入数据库时候可以使用这个XML串SQL Server会自动将这个串转化为XML类型并存储到数据库中
   随着SQL Server 对XML字段支持相应T-SQL语句也提供了大量对XML操作功能来配合SQL Server中XML字段使用本文主要介绍说明如何使用SQL语句对XML进行操作

2、定义XML字段
   在进行数据库设计中我们可以在表设计器中很方便将个字段定义为XML类型需要注意是XML字段不能用来作为主键或者索引键同样我们也可以使用SQL语句来创建使用XML字段数据表下面语句创建个名为“docs”表该表带有整型主键“pk”和非类型化 XML 列“xCol”:
    XML类型除了在表中使用还可以在存储过程、事务、等中出现下面我们来完成我们对XML操作第步使用SQL语句定义个XML类型数据并为它赋值:   


   CREATE TABLE docs (pk INT PRIMARY KEY, xCol XML not null)

 



="cnblogs_code_Collapse" id="Code_Closed_Text_145824">定义XML类型数据
declare @xmlDoc xml;
 @xmlDoc='<book id="0001">
                          <title>C Program</title>
                          <author>David</author>
                          <price>21</price>
                          </book>'

3、查询操作
   在定义了个XML类型数据的后我们最常用就是查询操作下面我们来介绍如何使用SQL语句来进行查询操作
   在T-Sql中提供了两个对XML类型数据进行查询分别是query(xquery)和value(xquery, dataType)其中query(xquery)得到是带有标签数据而value(xquery, dataType)得到则是标签内容接下类我们分别使用这两个来进行查询
   1、使用query(xquery) 查询
我们需要得到书标题(title)使用query(xquery)来进行查询查询语句为:



="cnblogs_code_Collapse" id="Code_Closed_Text_150945">query
select @xmlDoc.query('/book/title')

运行结果如图:


   2、使用value(xquery, dataType) 查询
同样是得到书标题使用value需要指明两个参数个为xquery 另个为得到数据类型看下面查询语句:


="cnblogs_code_Collapse" id="Code_Closed_Text_152143">value
select @xmlDoc.value('(/book/title)[1]', 'nvarchar(max)')

运行结果如图:


   3、查询属性值
无论是使用query还是value都可以很容易得到个节点某个属性值例如我们很希望得到book节点id我们这里使用value思路方法进行查询语句为:


="cnblogs_code_Collapse" id="Code_Closed_Text_152657">查询属性
select @xmlDoc.value('(/book/@id)[1]', 'nvarchar(max)')

运行结果如图:


   4、使用xpath进行查询
xpath是.net平台下支持统Xml查询语句使用XPath可以方便得到想要节点而不用使用where语句例如我们在@xmlDoc中添加了另外个节点重新定义如下:


="cnblogs_code_Collapse" id="Code_Closed_Text_154355">xpath查询
 @xmlDoc='<root>
                <book id="0001">
                    <title>C# Program</title>
                    <author>Jerry</author>
                    <price>50</price>
                 </book>
                 <book id="0002">
                    <title>Java Program</title>
                    <author>Tom</author>
                    <price>49</price>
                 </book>
              </root>'

--得到id为0002book节点
select @xmlDoc.query('(/root/book[@id="0002"])')

上面语句可以独立运行它得到是id为0002节点运行结果如下图:



4、修改操作
   SQL修改操作包括更新和删除SQL提供了mody思路方法实现对Xml修改操作mody思路方法参数为XML修改语言XML修改语言类似于SQL Insert、Delete、UpDate但并不样例如我们希望将id为0001书价钱(price)修改为100 我们就可以使用mody思路方法代码如下:

="cnblogs_code_Collapse" id="Code_Closed_Text_160556">mody修改节点值
 @xmlDoc.mody('replace value of (/root/book[@id=0001]/price/text)[1] with "100"')

--得到id为0001book节点
select @xmlDoc.query('(/root/book[@id="0001"])')

注意:mody思路方法必须出现在后面运行结果如图:



接下来我们来删除id为0002节点代码如下:

="cnblogs_code_Collapse" id="Code_Closed_Text_161127">删除节点
--删除节点id为0002book节点
 @xmlDoc.mody('delete /root/book[@id=0002]')
select @xmlDoc

运行结果如图:



很多时候我们还需要向xml里面添加节点这个时候我们样需要使用mody思路方法下面我们就向id为0001book节点中添加个ISBN节点代码如下:

="cnblogs_code_Collapse" id="Code_Closed_Text_162632">插入节点
--添加节点
 @xmlDoc.mody('insert <isbn>78-596-134</isbn> before (/root/book[@id=0001]/price)[1]')
select @xmlDoc.query('(/root/book[@id="0001"]/isbn)')

运行结果如图:


   添加和删除属性:当你学会对节点操作以后你会发现很多时候我们需要对节点进行操作这个时候我们依然使用mody思路方法例如向id为0001book节点中添加个date属性用来存储出版时间代码如下:
="cnblogs_code_Collapse" id="Code_Closed_Text_163649">添加属性
--添加属性
 @xmlDoc.mody('insert attribute date{"2008-11-27"} o (/root/book[@id=0001])[1]')
select @xmlDoc.query('(/root/book[@id="0001"])')
运行结果如图:

如果你想同时向个节点添加多个属性你可以使用个属性集合来实现属性集合可以写成:(attribute date{"2008-11-27"}, attribute year{"2008"})你还可以添加更多这里就不再举例了

   删除个属性例如删除id为0001 book节点id属性我们可以使用如下代码:
="cnblogs_code_Collapse" id="Code_Closed_Text_164341">删除属性
--删除属性
 @xmlDoc.mody('delete root/book[@id="0001"]/@id')
select @xmlDoc.query('(/root/book)[1]')
运行结果如图:

   修改属性值也是很常用例如把id为0001book节点id属性修改为0005我们可以使用如下代码:
="cnblogs_code_Collapse" id="Code_Closed_Text_164729">修改属性
--修改属性
 @xmlDoc.mody('replace value of (root/book[@id="0001"]/@id)[1] with "0005"')
select @xmlDoc.query('(/root/book)[1]')
运行结果如图:


OK经过上面学习相信你已经可以很好在SQL中使用Xml类型了下面是我们没有提到你可以去其它地方查阅:exist思路方法用来判断指定节点是否存在返回值为true或false； nodes思路方法用来把组由个查询返回节点转换成个类似于结果集表中组记录行

延伸阅读

• 2011-4-26-- 字段唯一,SQL Server如何保证可空字段中非空值唯一
• 2010-12-9-- mssql字段,MSSQL 添加字段说明
• 2010-12-9-- sql多字段排序,SQL 按特定字段值排序
• 2010-12-24-- sql删除字段,使用ArcCatalog删除某个字段的问题以及解决方法
• 2010-11-26-- 查找重复字段,SQL SERVER中查找某个字段位于哪些表
• 2009-2-12-- sql增加字段:sql server2000中bit类型字段的取反操作
• 2009-2-12-- sql修改表字段:SQL动态更新表字段
• 2008-11-24-- sql删除字段:创建与删除SQL约束或字段约束
• 2009-2-12-- sql添加字段:向下拉列表添加字段的几种方法