转:http://www.cnblogs.com/youring2/archive/2008/11/27/1342288.html
T-Sql操作Xml数据
一、前言
SQL Server 2005 引入了一种称为 XML 的本机数据类型。用户可以创建这样的表,它在关系列之外还有一个或多个 XML 类型的列;此外,还允许带有变量和参数。为了更好地支持 XML 模型特征(例如文档顺序和递归结构),XML 值以内部格式存储为大型二进制对象 (BLOB)。
用户将一个XML数据存入数据库的时候,可以使用这个XML的字符串,SQL Server会自动的将这个字符串转化为XML类型,并存储到数据库中。
随着SQL Server 对XML字段的支持,相应的,T-SQL语句也提供了大量对XML操作的功能来配合SQL Server中XML字段的使用。本文主要说明如何使用SQL语句对XML进行操作。
二、定义XML字段
在进行数据库的设计中,我们可以在表设计器中,很方便的将一个字段定义为XML类型。需要注意的是,XML字段不能用来作为主键或者索引键。同样,我们也可以使用SQL语句来创建使用XML字段的数据表,下面的语句创建一个名为“docs”的表,该表带有整型主键“pk”和非类型化的 XML 列“xCol”:
CREATE TABLE docs (pk INT PRIMARY KEY, xCol XML not null)
XML类型除了在表中使用,还可以在存储过程、事务、函数等中出现。下面我们来完成我们对XML操作的第一步,使用SQL语句定义一个XML类型的数据,并为它赋值:
declare @xmlDoc xml;
set @xmlDoc='<book id="0001">
<title>C Program</title>
<author>David</author>
<price>21</price>
</book>'
三、查询操作
在定义了一个XML类型的数据之后,我们最常用的就是查询操作,下面我们来介绍如何使用SQL语句来进行查询操作的。
在T-Sql中,提供了两个对XML类型数据进行查询的函数,分别是query(xquery)和value(xquery, dataType),其中,query(xquery)得到的是带有标签的数据,而value(xquery, dataType)得到的则是标签的内容。接下类我们分别使用这两个函数来进行查询。
1、使用query(xquery) 查询
我们需要得到书的标题(title),使用query(xquery)来进行查询,查询语句为:
select @xmlDoc.query('/book/title')
运行结果如图:
2、使用value(xquery, dataType) 查询
同样是得到书的标题,使用value函数,需要指明两个参数,一个为xquery, 另一个为得到数据的类型。看下面的查询语句:
select @xmlDoc.value('(/book/title)[1]', 'nvarchar(max)')
运行结果如图:
3、查询属性值
无论是使用query还是value,都可以很容易的得到一个节点的某个属性值,例如,我们很希望得到book节点的id,我们这里使用value方法进行查询,语句为:
select @xmlDoc.value('(/book/@id)[1]', 'nvarchar(max)')
运行结果如图:
4、使用xpath进行查询
xpath是.net平台下支持的,统一的Xml查询语句。使用XPath可以方便的得到想要的节点,而不用使用where语句。例如,我们在@xmlDoc中添加了另外一个节点,重新定义如下:
set @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为0002的book节点
select @xmlDoc.query('(/root/book[@id="0002"])')
上面的语句可以独立运行,它得到的是id为0002的节点。运行结果如下图:
四、修改操作
SQL的修改操作包括更新和删除。SQL提供了modify()方法,实现对Xml的修改操作。modify方法的参数为XML修改语言。XML修改语言类似于SQL 的Insert、Delete、UpDate,但并不一样。
1、修改节点值
我们希望将id为0001的书的价钱(price)修改为100, 我们就可以使用modify方法。代码如下:
set @xmlDoc.modify('replace value of (/root/book[@id=0001]/price/text())[1] with "100"')
--得到id为0001的book节点
select @xmlDoc.query('(/root/book[@id="0001"])')
注意:modify方法必须出现在set的后面。运行结果如图:
2、删除节点
接下来我们来删除id为0002的节点,代码如下:
--删除节点id为0002的book节点
set @xmlDoc.modify('delete /root/book[@id=0002]')
select @xmlDoc
运行结果如图:
3、添加节点
很多时候,我们还需要向xml里面添加节点,这个时候我们一样需要使用modify方法。下面我们就向id为0001的book节点中添加一个ISBN节点,代码如下:
--添加节点
set @xmlDoc.modify('insert <isbn>78-596-134</isbn> before (/root/book[@id=0001]/price)[1]')
select @xmlDoc.query('(/root/book[@id="0001"]/isbn)')
运行结果如图:
4、添加和删除属性
当你学会对节点的操作以后,你会发现,很多时候,我们需要对节点进行操作。这个时候我们依然使用modify方法,例如,向id为0001的book节点中添加一个date属性,用来存储出版时间。代码如下:
--添加属性
set @xmlDoc.modify('insert attribute date{"2008-11-27"} into (/root/book[@id=0001])[1]')
select @xmlDoc.query('(/root/book[@id="0001"])')
运行结果如图:
如果你想同时向一个节点添加多个属性,你可以使用一个属性的集合来实现,属性的集合可以写成:(attribute date{"2008-11-27"}, attribute year{"2008"}),你还可以添加更多。这里就不再举例了。
5、删除属性
删除一个属性,例如删除id为0001 的book节点的id属性,我们可以使用如下代码:
--删除属性
set @xmlDoc.modify('delete root/book[@id="0001"]/@id')
select @xmlDoc.query('(/root/book)[1]')
运行结果如图:
6、修改属性
修改属性值也是很常用的,例如把id为0001的book节点的id属性修改为0005,我们可以使用如下代码:
--修改属性
set @xmlDoc.modify('replace value of (root/book[@id="0001"]/@id)[1] with "0005"')
select @xmlDoc.query('(/root/book)[1]')
运行结果如图:
OK,经过上面的学习,相信你已经可以很好的在SQL中使用Xml类型了,下面是我们没有提到的,你可以去其它地方查阅:exist()方法,用来判断指定的节点是否存在,返回值为true或false; nodes()方法,用来把一组由一个查询返回的节点转换成一个类似于结果集的表中的一组记录行。
作者:齐飞
日期:2008年11月27日
参考资料:SQL Server联机丛书、MSDN、《数据库与SQL Server 2005教程》(清华大学出版社,钱雪忠编著)
版权归作者所有,转载请注明出处。
SQL对Xml字段的操作的更多相关文章
-
(轉載)sql server xml字段的操作
原文轉自:http://blog.csdn.net/hliq5399/article/details/8315373 另外可參考:https://msdn.microsoft.com/en-us/li ...
-
(转)SQL对Xml字段的操作
T-Sql操作Xml数据 一.前言 SQL Server 2005 引入了一种称为 XML 的本机数据类型.用户可以创建这样的表,它在关系列之外还有一个或多个 XML 类型的列:此外,还允许带有变量和 ...
-
SQL Server对Xml字段的操作
T-Sql操作Xml数据 一.前言 SQL Server 2005 引入了一种称为 XML 的本机数据类型.用户可以创建这样的表,它在关系列之外还有一个或多个 XML 类型的列:此外,还允许带有变量和 ...
-
SQL读取XML字段类型的信息
USE CSOS_NEW_2 GO --(1)定义临时表 DECLARE @table TABLE(id INT IDENTITY(1,1),XMLDetail XML) DECLARE @xml X ...
-
SQL ------------ 对表中字段的操作 alter
ALTER TABLE 语句用于在现有表中添加.删除或修改列. 注意不同的数据库的语句有所不一样 增加和删除都基本一致. 进行修改表中字段的时候注意: 本文主要介绍 sqlserver/mysql/ ...
-
Sql Server xml 类型字段的增删改查
1.定义表结构 在MSSM中新建数据库表CommunicateItem,定义其中一个字段ItemContentXml 为xml类型 2.编辑表数据,新增一行,发现xml类型不能通过设计器录入数据. 需 ...
-
Javascript 操作 Sql中的Xml 字段
在一个项目当中,我们需要把数据库中的Xml字段中的信息表现在页面中,如果用Sql进行操作,读取.这样的话,必然导致太复杂,于是我想到了,如果把Xml字段先读出来,然后用Js去操作,这样岂不是简单了许多 ...
-
一个由正则表达式引发的血案 vs2017使用rdlc实现批量打印 vs2017使用rdlc [asp.net core 源码分析] 01 - Session SignalR sql for xml path用法 MemCahe C# 操作Excel图形——绘制、读取、隐藏、删除图形 IOC,DIP,DI,IoC容器
1. 血案由来 近期我在为Lazada卖家中心做一个自助注册的项目,其中的shop name校验规则较为复杂,要求:1. 英文字母大小写2. 数字3. 越南文4. 一些特殊字符,如“&”,“- ...
-
SQL Server 2008中如何为XML字段建立索引
from:http://blog.csdn.net/tjvictor/article/details/4370771 SQL Server中的XML索引分为两类:主XML 索引和辅助XML索引.其中辅 ...
随机推荐
-
js瀑布流 原理实现揭秘 javascript 原生实现
web,js瀑布流揭秘 瀑布流再很久之前流行,可能如我一样入行晚的 ,可能就没有机会去使用.但是这个技术终究是个挺炫酷的东西,花了一个上午来研究,用原生js实现了一个,下面会附上源码,供大家解读. 说 ...
-
Couldn&#39;t open file on client side, trying server side 错误解决
09-09 09:43:21.651: D/MediaPlayer(3340): Couldn't open file on client side, trying server side09-09 ...
-
让spark运行在mesos上 -- 分布式计算系统spark学习(五)
mesos集群部署参见上篇. 运行在mesos上面和 spark standalone模式的区别是: 1)stand alone 需要自己启动spark master 需要自己启动spark slav ...
-
tomcat发布项目时,localhost可以访问,用ip地址不能访问,同时其他电脑也不能访问。
最近在开发项目中,遇到的一个问题是:在 tomcat中发布一个web项目,但是发布成功后,只能用http://localhost:8080/aml访问项目,不能用http://172.0.0.1:80 ...
-
【技术引擎——汇聚IT思想之间的碰撞】
转载请注明作者和出处:http://blog.csdn.net/pearyangyang/article/details/40869825 谢谢. 这篇博客将记录我所学习的那些博客大牛,方面以后 ...
-
JAVA基础总结2
2.1 关键字 常用的关键字主要包括如下: 2.2 标识符 简单而言标识符它其实就是用于标识某些东西的符号. 2.3 注释的应用 注释说明 注释的作用: 注释的应用: 2.4 常量和变量 常量的定义与 ...
-
20165206 2017-2018-2 《Java程序设计》第二周学习总结
20165205 2017-2018-2 <Java程序设计>第一周学习总结 教材学习内容总结 java语言共有8种基本数据类型,分别是boolean.byte.short.char.in ...
-
UI自动化环境准备
1.安装专业版pycharm,只要是填写了licensed的都是专业版本的pycharm 2.python中安装好selenium包 pip install selenium 3.python中安装 ...
-
name是个特殊的变量名吗
这是为什么?求大神
-
有趣:256个class选择器可以干掉1个id选择器——张鑫旭
我们应该都知道,从选择器得分权重上将,id选择器(#aaa{})和class选择器(.aaa{})完全不是一个数量级的,前者:1-0-0; 而后者为0-1-0.因此: #id { color:dark ...