在SQL Server中如何与XML交互-技术开发专区

在SQL Server中如何与XML交互

作者：IT 专家网编辑：许剑 2008-08-05 10:28 来源：ITPUB.n

【ITPUB 技术文档】XML可以说是数据库表现的一种灵活的形式，在数据库开发过程中，很多时候我们都会需要涉及到XML，特别是用在数据交换。而在SQL Server 2005中，XML已成为一种流行的数据类型，XML已经做为一种新型的类型存储在数据库中。借助于基于XML模式的强类型化支持和基于服务器端的XML数据校验功能，开发者就可以对存储的XML文档进行轻松地远程修改。

在SQL Server 2000中就已经包括了一些XML特征。最常用的是使用FOR XML语句以XML形式返回结果。SQL Server 2005的功能则明显不同。在SQL Server 2005中，XML是一种真正的数据类型;这意味着，可以使用XML作为表和视图中的列，XML可以用于T-SQL语句中或作为存储过程的参数。可以直接在数据库中存储、查询和管理XML文件。

在SQL Server中，主要有以下几种操作XML的方式：

1) FOR XML语句的使用

FOR XML语句在使用的时候有三种模式，参数分别为：RAW、AUTO和EXPLICIT。

先来分别看这几种模式：

RAW模式：

RAW 模式将查询结果集中的每一行转换为带有通用标识符或可能提供元素名称的XML元素。默认情况下，行集中非 NULL 的每列值都将映射为元素的一个属性。如果将 ELEMENTS 指令添加到 FOR XML 子句，则每个列值都将映射到元素的子元素。

先来看RAW的实现的方式：

创建一个表：

CREATE TABLE [dbo].[Tb_User](
[ID] [int] IDENTITY(1,1) NOT NULL,
[user_name] [varchar](20) NULL,
[user_Pwd] [varchar](20) NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

语句一：　

SELECT ID, User_Name, User_pwd FROM Tb_User FOR XML RAW

输出的结果如下：

语句二：　

SELECT ID, User_Name, User_pwd FROM Tb_User FOR XML RAW, ELEMENTS

输出的结果如下：

<row>
<ID>1</ID>
<User_Name>admin</User_Name>
<User_pwd>admin888</User_pwd>
</row>
<row>
<ID>2</ID>
<User_Name>user</User_Name>
<User_pwd>user</User_pwd>
</row>

AUTO模式：

AUTO 模式可确定基于查询返回的 XML 的形状。在确定嵌套元素的方式时，AUTO 模式试探法将比较相邻行中的列值。在FROM 子句内，每个在 SELECT 子句中至少有一列被列出的表都表示为一个 XML 元素。如果在 FOR XML 子句中指定了可选的 ELEMENTS 选项，SELECT 子句中列出的列将映射到属性或子元素。

同样来看看两种实现的方式：

语句一：

SELECT ID, User_Name, User_pwd FROM Tb_User FOR XML AUTO

输出的结果如下：

<Tb_User ID="1" User_Name="admin" User_pwd="admin888" />
<Tb_User ID="2" User_Name="user" User_pwd="user" />

语句二：

SELECT ID, User_Name, User_pwd FROM Tb_User FOR XML AUTO,ELEMENTS

输出的结果如下：　

<Tb_User>
<ID>1</ID>
<User_Name>admin</User_Name>
<User_pwd>admin888</User_pwd>
</Tb_User>
<Tb_User>
<ID>2</ID>
<User_Name>user</User_Name>
<User_pwd>user</User_pwd>
</Tb_User>

EXPLICIT模式：

在 EXPLICIT 模式中，查询书写器控制由执行查询所返回的 XML 文档的形式。必须以特定的方式编写查询，将有关预期嵌套的附加信息显式指定为查询的一部分。当指定 EXPLICIT 模式时，必须负责确保生成的 XML 符合语法规则并且有效。

EXPLICIT 模式会将由查询执行生成的行集转换为 XML 文档。为使 EXPLICIT 模式生成 XML 文档，行集必须具有特定的格式。这需要编写 SELECT 查询以生成具有特定格式的行集(通用表)，以便处理逻辑随后可以生成所需的 XML。

首先，查询必须生成下列两个元数据列：

第一列必须提供当前元素的标记号(整数类型)，并且列名必须是 Tag。查询必须为从行集构造的每个元素提供唯一标记号。

第二列必须提供父元素的标记号，并且此列的列名必须是 Parent。这样，Tag 和 Parent 列将提供层次结构信息。

前两列是 Tag 和 Parent，它们是元数据列。这些值确定层次结构。查询必须以特定的方式提供列名，Parent 列中的 0 或 NULL 表明相应的元素没有父级。

在构造 XML 的过程中，处理逻辑为每行选择一组列，然后构造一个元素。

现在来看看下面的两个语句：

语句一：

SELECT 1 as Tag,
NULL as Parent,
User_Name as [TbUser!1!UserName],
User_pwd as [TbUser!2!UserPwd]
FROM Tb_User
FOR XML EXPLICIT

输出的结果如下：

语句二：

SELECT 2 as Tag,
NULL as Parent,
User_Name as [TbUser!1!UserName],
User_pwd as [TbUser!2!UserPwd]
FROM Tb_User
FOR XML EXPLICIT

输出的结果如下：

在此示例中，列TbUser!1!UserName和TbUser!2!UserPwd 形成一组，然后该组用于构造元素。对于第一行中的 Tag 列值 1和对于 Tag 列值为 2 的行，根据Tag的选择的不同，生成对应表的记录就发生了变化。

2) 使用OPENXML

不同于以上，OPENXML主要通过读取XML然后可以转换为数据库的数据。

OPENXML的语法如下：

OPENXML( idoc int, rowpattern nvarchar , [ flags byte ] )
[ WITH ( SchemaDeclaration | TableName ) ]

Idoc：XML 文档的内部表式形式的文档句柄。

Rowpattern：XPath 模式，用来标识要作为行处理的节点。

Flags：指示应在 XML 数据和关系行集间使用映射以及应如何填充溢出列。

0 默认为“以属性为中心”的映射。

1 使用“以属性为中心”的映射。

2 使用“以元素为中心”的映射。

8 可与XML_ATTRIBUTES 或 XML_ELEMENTS 组合使用(逻辑或)。

WITH后面就是对应的字段名及字段对应的属性。

我们先来看一个微软关于这个调用的实例：　

DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @DocHandle

运行这个实例，结果如下：　

CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez

现在来分析以上这个例子：

在定义好了@XmlDocument的XML文档后，然后调用系统程序sp_xml_preparedocument，它表示在XML文档存在内存的时候，允许XML文档作为调用参数。然后就可以调用OPENXML函数。通过指定为 /ROOT/Customer 的标识要处理的节点，读取CustomerID和ContactName的值，然后就调用系统程序sp_xml_removedocument删除SQL Server 服务器内存中的XML数据。

但在调用OPENXML函数时要注意内存的使用情况。系统程序sp_xml_preparedocument 的返回值是指向内存中XML文档的句柄。所以不要使用OPENXML加载较大的XML文档，否则可能会造成服务器的内存溢出。

3) 针对 xml 数据类型的 XQuery

XQuery 是一种可以查询结构化或半结构化 XML 数据的语言。由于 SQL Server 2005 数据库引擎中提供 xml 数据类型支持，因此可以将文档存储在数据库中，然后使用 XQuery 进行查询。

在此我们只举一个简单的例子：

DECLARE @x xml
SET @x = '<Myroot><Element1>First</Element1><Element2>Second</Element2>
</Myroot>'
SELECT @x.query('/Myroot')

输出的结果如下：

<Myroot>
<Element1>First</Element1>
<Element2>Second</Element2>
</Myroot>

DECLARE @x xml
SET @x = '<Myroot><Element1>First</Element1><Element2>Second</Element2>
</Myroot>'
SELECT @x.query('/Myroot/Element1')

输出的结果如下：

<Element1>First</Element1>

可以看到，它通过读取XML文档的节点，而输出对应的值。

XQuery功能非常强大，所涉及到的内容也非常广泛，感兴趣的读者可以查看SQL Server 2005 Books Online中的XQuery详细介绍。

尽管在SQL Server 2005中XML数据类型就象许多其它数据类型一样对待，它还是存在以下的一些限制：

· XML类型不能转换成text或ntext数据类型。

· 除了string类型，没有其它数据类型能够转换成XML。

· XML列不能应用于GROUP BY语句中。

· 分布式局部(partitioned)视图不能包含XML数据类型。

· sql_variant实例的使用不能把XML作为一种子类型。

· XML列不能成为主键或外键的一部分。

· XML列不能指定为唯一的。

· COLLATE子句不能被使用在XML列上。

· XML列不能加入到规则中。

· 唯一可应用于XML列的内置标量函数是ISNULL和COALESCE。没有任何其它内置标量函数支持使用XML类型。

· 表中最多只能拥有32个XML列。

· 具有XML列的表不能有一个超过15列的主键。

· 具有XML列的表不能有一个timestamp数据类型作为它们的主键的一部分。

· 存储在数据库中的XML仅支持128级的层次。

关注我们