使用 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 树