發行項
09/24/2010

使用 FOR XML 與 OPENXML 發行和處理 XML 資料

您可以執行 SQL 查詢以傳回 XML 的結果，而非標準資料列集的結果。您可以直接執行這些查詢，或是從預存程序與使用者自訂函數中執行。若要直接擷取結果，就必須先使用 SELECT 陳述式中的 FOR XML 子句。然後，在 FOR XML 子句中，指定 XML 模式：RAW、AUTO、EXPLICIT 或 PATH。

例如，下列 SELECT 陳述式可從 AdventureWorks 資料庫的 Sales.Customer 與 Sales.SalesOrderHeader 資料表擷取資訊。此查詢在 FOR XML 子句中指定了 AUTO 模式：

USE AdventureWorks
GO
SELECT Cust.CustomerID, 
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       OrderHeader.Status,
       Cust.CustomerType
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 資料表中。此範例同時也顯示帶有 OPENXML 的 SELECT 陳述式，從 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 樹狀