Inside T-SQL XML (一)XML数据类型

你真的了解SQL Server XML?---- (xml数据类型)

一、排版

之前有博友一直说我的排版很非主流,所以重新约定下:

a)         红色(章节)

b)         绿色(书籍)

c)         蓝色(超链接)

d)         蓝色(重要内容,关键性东西)

二、前言:

关系数据库一直给我们行和列的影响,通过扩展无非就是添加行记录,然后也有添加列.至于分表扩展等我就不多说了.XML作为一种数据的载体,是否适用于关系型数据库呢?我不敢定论,但是通过一系列项目的经验也让我感受到XML在关系数据库中的魅力.当然也有人第一接触XMLSQL 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 ON A.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=3book节点是否存在

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含有一个类型为XMLx列的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链接地址,希望这节让你学会不少东西,高手们就当复习了,呵呵

下节主要是讲讲XML语法中的增加删除修改以及XML索引的分析和XML架构集合的管理

期待下文吧.GL!
ps:代码

/Files/bhtfg538/T-SQL1.rar 

posted @ 2009-08-23 17:25  Sai~  阅读(...)  评论(...编辑  收藏