SQL Server XML 数据类型

关系数据库一直给我们行和列的影响,通过扩展无非就是添加行记录,然后也有添加列.至于分表扩展等我就不多说了.XML作为一种数据的载体,是否适用于关系型数据库呢?我不敢定论,但是通过一系列项目的经验也让我感受到XML在关系数据库中的魅力.当然也有人第一接触XML在SQL Server的支持时候大吃一惊,为什么我们需要呢?

通过一些资料我们也可以看看高人们对XML在关系型数据库中的理解,比如在书

< Inside Microsoft SQL Server 2005— T-SQL Programming中就说到:>

当我听到SQL Server将支持本地XML数据类型的时候,第一闯入我思维中的问题是:我们为什么需要在关系数据库中得到如此的支持?我考虑了数个多月,并且最终认为这种支持非常重要也很有效.XML是不同应用程序和不同平台下的可扩展数据的混合语言.它被广泛的使用,而且几乎所有流行的技术都支持它.所以显见的数据也能处理XML.现在,XML能够以简单文本的形似存储在数据库中,但是纯文本的表现形式意味着对XML文档的内置结构一无所知.你可以分解它为文本,然后存储在多个关系表中,然后使用关系引擎来操作数据.但是关系结构都是静态的,不容易改变的.考虑到动态,容易改变结构的XML,就能解决这些问题,也能启用附加在此类型上的许多XML技术的功能.

   

我引用了书中的一小段话,当然你可以详细看看这本书,如果你对XML 数据库感兴趣的话也可以看看这本书<  Beginning XML Databases>.

一、 Xml 数据类型

还是那句老话,经典都在MSDN上,所以给出链接,需要大家安装本地MSDN Library,文章也是按照微软的叙述风格,但是加之自己的一些见解,避免MSDN上一些费解的东东.呵呵

参考链接:

ms-help://MS.MSDNQTR.v90.chs/udb9/html/d832f90c-a6c2-4552-9a14-f66274b6c6e8.htm

1.           XML 基本语法

首先是一些基本的XML数据类型语法和一些说明,XML作为一种数据类型,他最大可支持 2GB大小,可以使用XML类型作为表的列,变量,存储过程的参数,自定义函数参数等等.而通常可以使用这种类型保存XML的片段或者整个XML.

比如:

USE  tempdb;

GO

 

CREATE  TABLE #T(ID INT PRIMARY KEY IDENTITY(1,1),Col1 XML);

GO

 

INSERT  INTO #T SELECT N'

<books>

 <book id="1" />

 <book id="2" />

</books>'

SELECT  * FROM #T

返回结果:

--ID          Col1

--1           <books><book id="1" /><book id="2" /></books>

--(1  行受影响)

当然强大的2005引擎还提供了XML类型是否合法的功能,避免我们插入一些不合非,标签不匹配的XML节,

修改上面的例子:

INSERT  INTO #T SELECT N'

<books>

 <book id="1" />

 <book id="2" />

 <book>

</books>'

那么SSMS将提示:

消息9436，级别16，状态1，第1 行

XML 分析: 行6，字符8，结束标记与开始标记不匹配

诸如其他的语法,比如给表加DEFAULT,或者CHECK约束那么也和其他类型是一样的了.

例如MSDN的一个例子:

CREATE TABLE T (XmlColumn xml default N'<element1/><element2/>');

参考链接: ms-help://MS.MSDNQTR.v90.chs/udb9/html/a699d976-8099-4af1-a2f8-cd0e2bd57a83.htm

2.     类型化于非类型化的XML

以前接触过XML的朋友知道,XML特别的灵活,所以他可以有很多数据类型,但是我们必须给予一些规则约束,所以DTD就诞生了,但是DTD本身不属于XML,而且也很麻烦,后面有诞生了一些其他的XML验证,比如XDR,XML Schema,RELAX NG等等东西,关于详细的XML学习,大家可以去看看这本书:

< Professional XML byBill Evjenet al.>

当然如果你不想看英文书籍,那么可以去看看MSDN:

参考链接: ms-help://MS.MSDNQTR.v90.chs/wd_xmlstd/html/79c78508-c9d0-423a-a00f-672e855de401.htm

然后认真学习XML Schema架构,通过XML Schema我们可以将咱的XML编程强类型的,那么违反了XML Schema验证规则的东东,我们就能知道了.

为了做本章的测试,你要安装个VS 2005以上的版本哦,否则你就要手动写XML Schema,当然如果你相当熟悉XML Schema,手写也无妨.

首先,我们打开VS 2008,随便建立一个什么项目,控制台就行了.然后往解决方案中添加一个xml文件.输入以下内容:

<? xml version =" 1.0"encoding="utf-8" ?>

< books >

 < book id =" 1" />

 < book id =" 2" />

</ books >

选择VS工具栏上面的XML选项卡,选择”创建架构”,那么IDE将会打开一个新的XSD文件,如下内容:

<? xml version =" 1.0"encoding="utf-8"?>

< xs:schema attributeFormDefault =" unqualified"elementFormDefault="qualified"xmlns:xs="http://www.w3.org/2001/XMLSchema">

 < xs:element name =" books">

    < xs:complexType >

      < xs:sequence >

        < xs:element maxOccurs =" unbounded"name="book">

          < xs:complexType >

            < xs:attribute name =" id"type="xs:unsignedByte"use="required" />

          </ xs:complexType >

        </ xs:element >

      </ xs:sequence >

    </ xs:complexType >

 </ xs:element >

</ xs:schema >

这个XML Schema可以理解为:

定义了一个books 元素,他属于复杂类型(嵌套了其他类型,有点像类,呵呵),他是按照类似这样的顺序定义了他的子节点book,也只有book,而book元素可以出现无限多次,book还有一个id属性,而且是必须有的,属于无符号字节类型.

    通过如下语法为数据建立一个XML架构:

CREATE  XML SCHEMA COLLECTION [dbo].[BookSchemaCollection]

AS

N'

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

 <xs:element name="books">

    <xs:complexType>

      <xs:sequence>

        <xs:element maxOccurs="unbounded" name="book">

          <xs:complexType>

            <xs:attribute name="id" type="xs:unsignedByte" use="required" />

          </xs:complexType>

        </xs:element>

      </xs:sequence>

    </xs:complexType>

 </xs:element>

</xs:schema>'

我们需要做的就是去掉XML头文件 <? xml version =" 1.0"encoding="utf-8"?>,运行显示创建成功,那么XML架构会保存在SSMS的这个位置:

我们也可以通过如下语法来获取他的详细信息:

SELECT  * FROM sys.xml_schema_collections

SELECT  * FROM sys.xml_schema_collections A INNER JOIN sys.schemas B ONA.schema_id=B.schema_id

SELECT  XML_SCHEMA_NAMESPACE('dbo','BookSchemaCollection')

第一个查询返回当前数据库下的所有XML架构,显示了他的姓名,创建时间等等信息,我现在创建的XML架构集合属于dbo架构下的,但是一些情况下,可能有其他的架构名,所以我们通过第2个查询与schemas做一个链接,返回XML架构的详细信息,最后我们通过获取的数据库架构名,以及XML架构名来调用XML_SCHEMA_NAMESPACE返回XML架构的XML内容.

现在也有了架构集合,那么我们就能够创佳类型化的XML字段,参数等了.例如:

DECLARE  @xml XML (dbo.BookSchemaCollection)

 

SET  @xml=N'<books></books>'

SET  @xml=N'<books><book /></books>'

SET  @xml=N'<books><book id="1"/></books>'

SET  @xml=N'<books><book id="1"/><other></other></books>'

经过测试,使用第3个给@xml变量赋值的语句才能成功,因此是按照我们XML架构 dbo . BookSchemaCollection来进行验证的.通过使用XML Schema可以一些约束来达到我们业务实现的目的,这种方式同样可以运用到存储过程,自定义函数,表中.我就不细说了.

参考链接:

ms-help://MS.MSDNQTR.v90.chs/udb9/html/4bc50af9-2f7d-49df-bb01-854d080c72c7.htm

3.     XML 数据类型方法

query方法: 对 xml 数据类型的实例指定 XQuery。结果为 xml 类型。该方法返回非类型化的 XML 实例。Xquery是建立在XPath基础之上的,所以在query方法中我们也能够使用XPath表达式.

如果你对Xpath不了解,那么可以先去学习下XPath,还是非常简单的,为什么叫XPath呢,因为和Windows文件路径一样使用类似E:\文件夹方式来访问数据,所以取名叫XPath,只不过XPath使用/这个斜杠.

XPath参考链接:

ms-help://MS.MSDNQTR.v90.chs/wd_xmlstd/html/6da1b6e3-256e-4919-8848-53b425f72ed1.htm

我做了一些简单的Demo帮助大家理解,其他更深入的语法我会在以后哦文章中演示:

DECLARE  @xml XML;

SET  @xml=N'

<books>

 <book id="1" />

 <book id="2" />

 <book />

</books>

'

-- 返回所有的book节点

SELECT  @xml.query('/books');

 

-- 返回当前上下文节点

SELECT  @xml.query('.')

 

-- 返回当前上级节点

SELECT  @xml.query('/books/book/..')

 

-- 查询ID为的book节点

SELECT  @xml.query('/books/book[@id="1"]')

 

-- 查询book节点集合中的第个

SELECT  @xml.query('/books/book[2]');

 

-- 查询含有id属性的book节点

SELECT  @xml.query('/books/book[@id]')

 

-- 查询所有book节点

SELECT  @xml.query('//book')

上述例子中都是没有带命名空间的XML片段,下面的一些例子中用于查询带有命名空间的XML片段,有两种方式可以为带有命名空间的XML执行查询,如下实例:

DECLARE  @xml XML;

SET  @xml=N'

<lk:books xmlns:lk="http://www.mm2vv.cn">

    <lk:book id="1" />

    <lk:book id="2" />

    <lk:book id="3" />

</lk:books>

'

 

-- 查询不到,没带命名空间

SELECT  @xml.query('/books/book');

 

-- 内嵌方式定义命名空间

SELECT  @xml.query('declare namespace lk="http://www.mm2vv.cn";

/lk:books/lk:book

' );

 

-- 外部声明

WITH  XMLNAMESPACES

(

'http://www.mm2vv.cn'  AS lk

)

SELECT  @xml.query('/lk:books/lk:book');

参考链接:

ms-help://MS.MSDNQTR.v90.chs/udb9/html/f48f6f7b-219f-463a-bf36-bc10f21afaeb.htm

4.     Value 函数

对 XML 执行 XQuery，并返回 SQL 类型的值。此方法将返回标量值。
XQuery 表达式，一个字符串文字，从  XML  实例内部检索数据。 XQuery  必须最多返回一个值。否则，将返回错误。

所以下面的例子你可以我都加了”[1]”

DECLARE  @xml XML;

SET  @xml=N'

<lk:books xmlns:lk="http://www.mm2vv.cn">

    <lk:book id="1">book1</lk:book>

    <lk:book id="2">book2</lk:book>

    <lk:book id="3">book3</lk:book>

</lk:books>

'

-- 取第一个book节点的值

SELECT  @xml.value('declare namespace lk="http://www.mm2vv.cn";

(/lk:books/lk:book)[1]' , 'varchar(20)' );

 

-- 取属性值

SELECT  @xml.value('declare namespace lk="http://www.mm2vv.cn";

(/lk:books/lk:book/@id)[1]' , 'int' );

5.     Exist 函数
对 XML 执行 XQuery，并返回 SQL 类型的值。此方法将返回标量值 。 返回“位”，表示下列条件之一：

1 ，表示 True（如果查询中的 XQuery 表达式返回一个非空结果）。即，它至少返回一个 XML 节点。0，表示 False（如果它返回一个空结果）。NULL（如果执行查询的 xml 数据类型实例包含 NULL）。

 

DECLARE  @xml XML;

SET  @xml=N'

<lk:books xmlns:lk="http://www.mm2vv.cn">

    <lk:book id="1">book1</lk:book>

    <lk:book id="2">book2</lk:book>

    <lk:book id="3">book3</lk:book>

</lk:books>

'

-- 判断ID=3的book节点是否存在

SELECT  @xml.exist('declare namespace lk="http://www.mm2vv.cn";/lk:books/lk:book[@id="3"]');

 

DECLARE  @x xml

DECLARE  @f bit

SET  @x = '<Somedate>2002-01-01Z</Somedate>'

SET  @f = @x.exist('/Somedate[(text()[1] cast as xs:date ?) = xs:date("2002-01-01Z") ]')

SELECT  @f

参考链接:

ms-help://MS.MSDNQTR.v90.chs/udb9/html/a55b75e0-0a17-4787-a525-9b095410f7af.htm

6.     Nodes 函数

如果要将 xml 数据类型实例拆分为关系数据，则 nodes() 方法非常有用。它允许您标识将映射到新行的节点。

我们通过对nodes可以了解到,将xml数据类型拆分成关系数据,也就是拆分成行,这让我们不得不联想到,解决SQL中一个棘手的问题

我有一串字符a,123,fa,dfadf,123,fad,fadf

现在我想获取

类似这样的结果:

Col

a

123

Fa

Dfadf

123

Fad

Fadf

一般的话,我们可以建立一个自定义函数,根据判断”,”的位置来SUBSTRING,或者我们利用辅助表Nums,或者 SELECT  distinct number FROM master..spt_values获取一些连续数字序列来连接表,然后根据”,”的索引位置取数.如果你也可以批凑类似UNION ALL SELECT RIGHT(字段,长度)==的方式.也可以使用SQL CLR,一个”xx”.Split就搞定

其实还有一种比较新颖的解法:

DECLARE  @String NVARCHAR(200);

 

SET  @String=N' 我和你,买大米,1234,1234,向前进,我们是害虫,动起来~,你问我爱你有多深' ;

 

SELECT  T.x.query('data(.)') FROM

( SELECT  CONVERT(XML,'<x>'+REPLACE(@String,',','</x><x>')+'</x>',1) Col1) A

OUTER  APPLY A.Col1.nodes('/x') AS T(x)

呵呵,是不是很神奇呢,所以继续学习下文:

DECLARE  @xml XML

SET  @xml=N'

<books>

 <book id="1">book1</book>

 <book id="2">book2</book>

 <book id="3">book3</book>

</books>

' ;

 

--nodes  返回未命名的行集,所以使用AS T(x) 做一个表T含有一个类型为XML的x列的T(x),然后使用query查询获取当前节点值

SELECT  T.x.query('./text()') FROM @xml.nodes('/books/book') AS T(x)

 

-- 不能直接返回T.x

--SELECT T.x FROM @xml.nodes('/books/book') AS T(x)

 

-- 返回属性值

SELECT  T.x.value('./@id','int') FROM @xml.nodes('/books/book') AS T(x)

 

--nodes 返回T1表以后,使用OUTER APPLY 继续拆分name节点,最后判断row节点下是否含有name,然后查询

DECLARE  @x xml       

SET  @x='      

<Root>      

    <row id="1"><name>Larry</name><oflw>some text</oflw></row>      

    <row id="2"><name>Joe</name></row>      

    <row id="3" />      

</Root>'      

SELECT  T1.rows.value('@id','int') as id,T1.rows.query('.')      

FROM  @x.nodes('/Root/row') T1(rows)      

OUTER  APPLY T1.rows.nodes('./name') as T2(names)      

WHERE  T2.names IS NOT NULL       

GO  

参考链接:

ms-help://MS.MSDNQTR.v90.chs/udb9/html/7267fe1b-2e34-4213-8bbf-1c953822446c.htm

7.     在XML数据内部绑定关系数据

其实就两种语法:

sql:column()

sql:variable()

顾名思义,一个调用列,一个调用变量

DECLARE  @T TABLE(ID INT PRIMARY KEY IDENTITY(1,1),Col1 XML)

INSERT  INTO @T SELECT N'

<books>

    <book id="1" />

</books>'

UNION  ALL SELECT N'

<books>

    <book id="2" />

</books>

'

-- 插入到自定义节点中book 的id属性,调用的是ID主键

SELECT  Col1.query('<book id="{ sql:column("A.ID") }" />') FROM @T A

 

DECLARE  @I INT

SET  @I='123123';

 

SELECT  Col1.query('<book id="{ sql:variable("@I") }" />') FROM @T A

参考链接:

ms-help://MS.MSDNQTR.v90.chs/udb9/html/03d013a9-b53f-46c3-9628-da77f099c74a.htm

8.     XML 数据类型的使用准则和XML数据类型的限制

参考链接:

ms-help://MS.MSDNQTR.v90.chs/udb9/html/1a483aa1-42de-4c88-a4b8-c518def3d496.htm

ms-help://MS.MSDNQTR.v90.chs/udb9/html/529244cf-3f00-49df-aa8d-ffc29c8a3cbb.htm

二、节后语

本文主要给读者一个SQL XML开头,很多东西还是需要读者自己去消化,尤其是通看下我给定的XML链接地址,希望这节让你学会不少东西,高手们就当复习了,呵呵