共用方式為


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

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

例如,下列 SELECT 陳述式可從 AdventureWorks 資料庫的 Sales.CustomerSales.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 文件之全部或各部分的資料列集檢視。使用 OPENXMLINSERT 陳述式,便可從這樣的資料列集將資料插入資料庫資料表。有數個 OPENXML 呼叫可用來提供 XML 文件各個部分的資料列集檢視並處理它們,例如,將它們插入不同的資料表。此處理序又稱為將 XML 切割成資料表。

下列範例將 XML 文件成兩部分,然後使用兩個 INSERT 陳述式,將其 <Customers> 元素儲存在 Customers 資料表中,以及將其 <Orders> 元素儲存在 Orders 資料表中。此範例同時也顯示帶有 OPENXMLSELECT 陳述式,從 XML 文件中擷取 CustomerIDOrderDate。在此程序中的最一個步驟是呼叫 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_pareparedocument 所建立的 XML 文件,顯示其剖析後的 XML 樹狀結構。

剖析的 XML 樹狀

請參閱

參考

利用 OPENXML 查詢 XML
使用 FOR XML 建構 XML
FOR XML 子句的基本語法

概念

XML 資料類型
XML 應用程式範例

其他資源

OPENXML (Transact-SQL)

說明及資訊

取得 SQL Server 2005 協助