通过

  • 项目

使用 FOR XML 和 OPENXML 发布和处理 XML 数据

可以执行 SQL 查询将结果返回为 XML，而不是标准行集。这些查询可以直接执行，也可以从存储过程和用户定义函数中执行。若要直接检索结果，首先要使用 SELECT 语句的 FOR XML 子句。然后，在 FOR XML 子句中，指定 XML 模式：RAW、AUTO、EXPLICIT 或 PATH。

例如，下面的 SELECT 语句将从 AdventureWorks2008R2 数据库中的 Sales.Customer 和 Sales.SalesOrderHeader 表中检索信息。此查询在 FOR XML 子句中指定了 AUTO 模式：

USE AdventureWorks2008R2
GO
SELECT Cust.CustomerID, 
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       OrderHeader.Status
FROM Sales.Customer Cust 
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
FOR XML AUTO

虽然可以使用 FOR XML 子句检索 XML 文档形式的数据，但也可以使用 Transact-SQL OPENXML 函数插入以 XML 文档形式表示的数据。OPENXML 是一个行集提供程序，类似于表或视图，提供内存中 XML 文档上的行集。OPENXML 通过提供 XML 文档内部表示形式的行集视图，允许访问 XML 数据，就像它是关系行集一样。行集中的记录可以存储在数据库表中。OPENXML 可在用于指定源表或源视图的 SELECT 和 SELECT INTO 语句中使用。

下面的示例说明了如何在 INSERT 语句和 SELECT 语句中使用 OPENXML。示例 XML 文档包含 <Customers> 和 <Orders> 元素。

首先，sp_xml_preparedocument 存储过程分析 XML 文档。分析后的文档是 XML 文档中各节点（元素、属性、文本和注释）的树状表示形式。然后，OPENXML 引用此经过分析的 XML 文档，并提供此 XML 文档全部或部分内容的行集视图。使用 OPENXML 的 INSERT 语句可将数据从这样的行集插入数据库表中。可以使用多个 OPENXML 调用来提供 XML 文档中各部分的行集视图，并对它们进行处理，例如，将它们插入不同的表中。此过程也称为“将 XML 拆分到表中”。

在下面的示例中，拆分 XML 文档的方式是使用两个 INSERT 语句，将 <Customers> 元素存储在 Customers 表中，将 <Orders> 元素存储在 Orders 表中。另外，此例还说明了 SELECT 语句如何使用 OPENXML 从 XML 文档中检索 CustomerID 和 OrderDate。该过程的最后一步是调用 sp_xml_removedocument。这样做是为了释放已分配的内存，以包含在分析阶段创建的内部 XML 树表示形式。

-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
                ContactName varchar(20), 
                CompanyName varchar(20))
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime)
GO
DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(max) -- or xml type
SET @xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers 
SELECT * 
FROM OPENXML(@docHandle, N'/ROOT/Customers') 
  WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders 
SELECT * 
FROM OPENXML(@docHandle, N'//Orders') 
  WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle

下图显示了使用 sp_xml_preparedocument 创建的上述 XML 文档的 XML 分析树。

经过分析的 XML 树