SQL 和XML的交互

Oct 6

T-SQL

在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级的层次。

tags:sql xml 技巧

奇文共欣赏,疑义相与析