Inside T-SQL XML (一)XML数据类型
你真的了解SQL Server XML吗?---- (一xml数据类型)
一、排版
之前有博友一直说我的排版很非主流,所以重新约定下:
a) 红色(章节)
b) 绿色(书籍)
c) 蓝色(超链接)
d) 蓝色(重要内容,关键性东西)
二、前言:
关系数据库一直给我们行和列的影响,通过扩展无非就是添加行记录,然后也有添加列.至于分表扩展等我就不多说了.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 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=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链接地址,希望这节让你学会不少东西,高手们就当复习了,呵呵
下节主要是讲讲XML语法中的增加删除修改以及XML索引的分析和XML架构集合的管理
