sql server T-Sql操作Xml数据
来源:互联网 发布:枢木朱雀知乎 编辑:程序博客网 时间:2024/05/16 13:56
一、前言
SQLServer 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, xColXML not null)
XML类型除了在表中使用,还可以在存储过程、事务、函数等中出现。下面我们来完成我们对XML操作的第一步,使用SQL语句定义一个XML类型的数据,并为它赋值:
declare @xmlDoc xml;
set @xmlDoc='<bookid="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)来进行查询,查询语句为:
declare @xmlDocxml;
set @xmlDoc='<root>
<bookid="0001">
<title>C#Program</title>
<author>Jerry</author>
<price>50</price>
</book>
<bookid="0002">
<title>JavaProgram</title>
<author>Tom</author>
<price>49</price>
</book>
</root>'
select @xmlDoc.query('/root/book/title')
select @xmlDoc.query('(//title)[2]')
select @xmlDoc.query('(root/book[1]/title)')
select @xmlDoc.query('(/root/book/title)[1]')
select @xmlDoc.query('/root/book[position()<2]/title')
declare @idvarchar(max)= '0001'
select @xmlDoc.query('/root/book[@id=sql:variable("@id")]/title')
2、使用value(xquery, dataType) 查询
同样是得到书的标题,使用value函数,需要指明两个参数,一个为xquery,另一个为得到数据的类型。看下面的查询语句:
select @xmlDoc.value('(/root/book/title)[1]','nvarchar(max)')
select @xmlDoc.value('(/root/book[2]/title)[1]','nvarchar(max)')
select @xmlDoc.value('(//title)[1]','nvarchar(max)')
declare @idvarchar(max)= '0002'
select @xmlDoc.value('(/root/book[@id=sql:variable("@id")]/title)[1]','nvarchar(max)')
3、查询属性值
无论是使用query还是value,都可以很容易的得到一个节点的某个属性值,例如,我们很希望得到book节点的id,我们这里使用value方法进行查询,语句为:
select @xmlDoc.value('(/root/book/@id)[1]','nvarchar(max)')
select @xmlDoc.value('(/root/book[2]/@id)[1]','nvarchar(max)')
四、修改操作
SQL的修改操作包括更新和删除。SQL提供了modify()方法,实现对Xml的修改操作。modify方法的参数为XML修改语言。XML修改语言类似于SQL的Insert、Delete、UpDate,但并不一样。
1、修改节点值
代码如下:
select @xmlDoc.query('/root/book')
declare @idvarchar(max)= '0001'
declare @pricevarchar(max)= '12345'
set @xmlDoc.modify('replace value of(/root/book[@id=sql:variable("@id")]/price/text())[1] with sql:variable("@price")')
select @xmlDoc.query('(/root/book[@id="0001"])')
set @xmlDoc.modify('replace value of(/root/book[@id="0002"]/price/text())[1] with 54321')
select @xmlDoc.query('(/root/book[@id="0002"])')
注意:modify方法必须出现在set的后面。
2、删除节点
代码如下:
select @xmlDoc.query('/root/book')
declare @idvarchar(max)= '0001'
set @xmlDoc.modify('delete root/book[@id=sql:variable("@id")]')
select @xmlDoc.query('(/root/book)')
3、添加节点
代码如下:
select @xmlDoc.query('/root/book')
declare @idvarchar(max)= '0001'
set @xmlDoc.modify('insert<isbn>{sql:variable("@id")}</isbn> before(/root/book[@id=0001]/price)[1]')
select @xmlDoc.query('(/root/book[@id="0001"])')
4、添加属性
代码如下:
--添加属性
set @xmlDoc.modify('insert attribute date{sql:variable("@id")} into (/root/book[@id=0001])[1]')
select @xmlDoc.query('(/root/book[@id="0001"])')
set @xmlDoc.modify('insert (attribute www{sql:variable("@id")} ,attribute wzg{sql:variable("@id")}) into (/root/book[@id=0001])[1]')
select @xmlDoc.query('(/root/book[@id="0001"])')
5、删除属性
declare @idvarchar(max)= '0001'
select @xmlDoc.query('/root/book[@id=sql:variable("@id")]')
set @xmlDoc.modify('delete root/book[@id=sql:variable("@id")]/@id')
select @xmlDoc.query('(/root/book)[1]')
6、修改属性
declare @idvarchar(max)= '0001'
select @xmlDoc.query('/root/book[@id=sql:variable("@id")]')
set @xmlDoc.modify('replace value of (root/book[@id="0001"]/@id)[1]with "0005"')
select @xmlDoc.query('(/root/book)[1]')
7.判断指定的节点是否存在
exist()方法,用来判断指定的节点是否存在,返回值为true或false
declare @idvarchar(max)= '0001'
select @xmlDoc.exist('(/root/book[@id=sql:variable("@id")])[2]')
8.把一组由一个查询返回的节点转换成一个类似于结果集的表中的一组记录行
nodes()方法,用来把一组由一个查询返回的节点转换成一个类似于结果集的表中的一组记录行
DECLARE @xXML
SET @x='
<root>
<rogue id="001">
<hobo id="1">
<name>1</name>
<nickname>1</nickname>
<type>1</type>
</hobo>
</rogue>
<rogue id="002">
<hobo id="2">
<name>2</name>
<nickname>2</nickname>
<type>2</type>
</hobo>
</rogue>
<rogue id="003">
<hobo id="3">
<name>3</name>
<nickname>3</nickname>
<type>3</type>
</hobo>
</rogue>
</root>'
SELECT T.c.query('./hobo/name')AS result
FROM @x.nodes('/root/rogue') T(c);
SELECT T.c.value('(./hobo/name)[1]','varchar') AS result
FROM @x.nodes('/root/rogue') T(c);
- sql server T-Sql操作Xml数据
- sql server T-Sql操作Xml 和 数据生成xml
- sql server表操作T-sql
- Sql Server XML 操作实例
- SQL Server与XML 数据
- SQL Server XML数据解析
- SQL Server XML数据解析
- SQL Server XML数据解析
- [转]T-SQL语句 XML操作
- sql server 大量数据操作
- SQL SERVER插入数据操作
- 用T-SQL操作面试SQL Server开发人员
- 使用T-SQL操作面试SQL Server开发人员
- 用T-SQL操作面试SQL Server开发人员
- T-SQL(5)-操作数据行
- SQL SERVER 2012 T-SQL 发现存储过程元数据
- T-sql(SQL Server)
- sql server T-SQL 基础
- glibc 内存重复释放检测失效
- 详细解析Java中抽象类和接口的区别
- 查看Linux用的桌面是GNOME、KDE或者其他
- oracle特殊字符替换用chr
- mysql双机热备份的实现步骤
- sql server T-Sql操作Xml数据
- Android高级应用开发(基础篇) - stage8 - 学习笔记
- Android MediaPlayer 常用方法介绍 .
- POJ--1328
- spring 定时器配置 摘自 那年那月那天
- 零基础学习JAVA.第六天:面向对象
- 打算进军海外市场的手游开发者,来看看全球各地玩家的奇葩习惯
- Android网络连接判断与处理
- WinSCP