sql server T-Sql操作Xml数据

一、前言

SQLServer 2005 引入了一种称 XML 的本机数据型。用可以这样的表,它在关系列之外有一个或多个 XML 型的列;此外,许带量和参数。了更好地支持 XML 模型特征(例如文档序和递归结构),XML 以内部格式存储为大型二 (BLOB)

将一个XML数据存入数据候,可以使用XML的字符串,SQL Server会自的将个字符串XML型,并存到数据中。

随着SQL Server XML字段的支持,相的,T-SQL句也提供了大量XML操作的功能来配合SQL ServerXML字段的使用。本文主要明如何使用SQLXML行操作。

二、定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 @xmlDoc xml;

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 @id varchar(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 @id varchar(max) = ‘0002‘

select @xmlDoc.value(‘(/root/book[@id=sql:variable("@id")]/title)[1]‘, ‘nvarchar(max)‘)

 

3查询属性

是使用queryvalue,都可以很容易的得到一个点的某个属性,例如,我很希望得到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 InsertDeleteUpDate,但并不一

 

1、修改

如下:

select @xmlDoc.query(‘/root/book‘)

declare @id varchar(max) = ‘0001‘

declare @price varchar(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 @id varchar(max) = ‘0001‘

 

set @xmlDoc.modify(‘delete root/book[@id=sql:variable("@id")]‘)

select @xmlDoc.query(‘(/root/book)‘)

 

 

 

3、添加

如下:

select @xmlDoc.query(‘/root/book‘)

declare @id varchar(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 @id varchar(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 @id varchar(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()方法,用来判断指定的点是否存在,返回值为truefalse

declare @id varchar(max) = ‘0001‘        

select @xmlDoc.exist(‘(/root/book[@id=sql:variable("@id")])[2]‘)

 

8. 把一由一个查询返回的转换成一个似于果集的表中的一组记录

nodes()方法,用来把一由一个查询返回的转换成一个似于果集的表中的一组记录

DECLARE @x XML  

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数据

上一篇:冲,java学习第二天


下一篇:SQL server排序函数细说