共用方式為

OPENXML （SQL Server）

OPENXML，作為 Transact-SQL 關鍵詞，提供儲存在記憶體中的 XML 文件的資料列集，類似於資料表或視圖。 OPENXML 允許存取 XML 數據，就像它是關係型數據列集一樣。 其方式是提供 XML 文件內部表示法的數據列集檢視。 數據列集中的記錄可以儲存在資料庫數據表中。

OPENXML 可用於 SELECT 和 SELECT INTO 語句，無論是哪裡行集提供者、檢視或 OPENROWSET 可以作為資料來源。 如需 OPENXML 語法的相關信息，請參閱 OPENXML （Transact-SQL） 。

若要使用 OPENXML 撰寫 XML 檔案查詢，您必須先呼叫 sp_xml_preparedocument。 這會剖析 XML 檔，並將句柄傳回已剖析的檔，以供取用。 剖析的檔是 XML 檔中各種節點的檔案物件模型 （DOM） 樹狀結構表示法。 檔案句柄會傳遞至 OPENXML。 OPENXML 接著會根據傳遞給文件的參數，提供文件的數據列集檢視。

備註

sp_xml_preparedocument 使用經過 SQL 更新的 MSXML 剖析器版本，Msxmlsql.dll。 這個版本的 MSXML 剖析器是設計來支援 SQL Server，並與 MSXML 2.6 版保持回溯相容。

XML 檔的內部表示法必須藉由呼叫 sp_xml_removedocument 系統預存程式來釋放記憶體，以從記憶體中移除。

以下為流程說明。

使用 OPENXML 剖析 XML

請注意，若要瞭解 OPENXML，需要熟悉 XPath 查詢和瞭解 XML。 如需 SQL Server 中 XPath 支援的詳細資訊，請參閱 在 SQLXML 4.0 中使用 XPath 查詢。

備註

OpenXML 允許將數據列和數據行 XPath 模式參數化為變數。 如果程式設計人員向外部使用者公開參數化，這類參數化可能會導致 XPath 運算式插入（例如，如果參數是透過外部呼叫的預存程式提供）。 為了避免這類潛在的安全性問題，建議永遠不要向外部呼叫者公開 XPath 參數。

範例

下列範例示範 在 OPENXML 語句和 SELECT 語句中使用 INSERT 。 範例 XML 檔包含 <Customers> 和 <Orders> 元素。

首先，sp_xml_preparedocument 預存程序會剖析 XML 文件。 剖析的檔是 XML 檔中節點的樹狀結構表示法（元素、屬性、文字和批注）。 OPENXML 然後參考這個剖析的 XML 檔，並提供此 XML 檔之所有或部分的數據列集檢視。 INSERT使用 OPENXML 的語句可以將這類數據列集的數據插入資料庫數據表中。 若干 OPENXML 呼叫可用來提供 XML 文件中不同部分的數據行集檢視，並透過將它們插入不同的數據表來處理。 此程式也稱為將 XML 切割成數據表。

在下列範例中，XML 文件被分割為<Customers>元素，儲存在Customers資料表中，<Orders>元素則通過使用兩個INSERT語句儲存在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 樹狀結構

OPENXML 參數

OPENXML 的參數包括下列各項：

  • XML 檔案句柄 （idoc）

  • 用來識別節點要對應至資料列的 XPath 運算式（rowpattern）

  • 要產生之數據列集的描述

  • 數據列集數據行與 XML 節點之間的對應

XML 檔案句柄 （idoc）

預存程序會返回文件句柄 sp_xml_preparedocument。

用於識別要處理節點的 XPath 運算式（rowpattern）

指定為 rowpattern 的 XPath 運算式會識別 XML 檔中的一組節點。 rowpattern 所識別的每個節點都會對應至 OPENXML 所產生的數據列集中的單一數據列。

XPath 運算式所識別的節點可以是 XML 檔中的任何 XML 節點。 如果 rowpattern 識別 XML 檔中的一組元素，則每個已識別之元素節點的數據列集中會有一個數據列。 例如，如果 rowpattern 以屬性結尾，則會針對 rowpattern所選取的每個屬性節點建立一個數據列。

要產生之數據列集的描述

OPENXML 會使用數據列集架構來產生產生的數據列集。 指定資料列集架構時，您可以使用下列選項。

使用 Edge 表格格式

您應該使用邊緣資料表格式來指定資料列集架構。 請勿使用WITH子句。

當您這樣做時，OPENXML 會傳回邊緣數據表格式的數據列集。 這稱為邊表，因為剖析的 XML 檔樹狀結構中的每個邊都會對應至列集中的一列。

邊緣數據表代表單一數據表中細部 XML 文件結構。 此結構包含元素和屬性名稱、檔案階層、命名空間和處理指令。 邊緣數據表格式可讓您取得不會透過中繼屬性公開的其他資訊。 如需中繼屬性的詳細資訊，請參閱 在 OPENXML 中指定中繼屬性。

邊緣數據表所提供的其他資訊可讓您儲存和查詢元素和屬性的數據類型，以及節點類型，以及儲存和查詢 XML 檔案結構的相關信息。 透過這項額外資訊，您也可以建置自己的 XML 檔管理系統。

藉由使用邊緣數據表，您可以撰寫將 XML 檔當作二進位大型物件 （BLOB） 輸入的預存程式、產生邊緣數據表，然後在更詳細的層級擷取和分析檔。 此詳細層級可能包括尋找檔階層、元素和屬性名稱、命名空間和處理指令。

當映射到其他關係型格式不合邏輯時，邊緣表格也可以作為 XML 文件的儲存格式，而 ntext 字段不能提供足夠的結構資訊。

在您可以使用 XML 剖析器來檢查 XML 檔時，您可以改用邊緣資料表來取得相同的資訊。

下表描述邊緣數據表的結構。

欄位名稱 數據類型 說明
識別碼 bigint 這是文件節點的唯一標識碼。

根元素的標識碼值為 0。 保留負標識碼值。
parentid bigint 識別節點的父代。 此 ID 所指示的父項不一定是父元素。 不過，這取決於這個ID所識別的父節點的 NodeType。 例如，如果節點是文字節點，其父節點可能是屬性節點。

如果節點位於 XML 檔中的最上層，其 ParentID 為 NULL。
節點類型 int 識別節點類型，而且是對應至 XML 物件模型 （DOM） 節點類型編號的整數。

以下是可以出現在此數據行中的值，以指出節點類型：

1 = 元素節點

2 = 屬性節點

3 = 文字節點

4 = CDATA 區段節點

5 = 實體參考節點

6 = 實體節點

7 = 處理指令節點

8 = 批注節點

9 = 檔案節點

10 = 檔案類型節點

11 = 檔案片段節點

12 = 表示法節點

如需詳細資訊，請參閱 Microsoft XML （MSXML） SDK 中的 “nodeType 屬性” 主題。
localname nvarchar（max） 提供元素或屬性的本地名稱。 如果 DOM 對象沒有名稱，則為 NULL。
前綴 nvarchar（max） 這是節點名稱的命名空間前置詞。
namespaceuri nvarchar（max） 這是節點的命名空間 URI。 如果值為 NULL，則沒有任何命名空間存在。
datatype nvarchar（max） 這是專案或屬性數據列的實際數據類型，否則為 NULL。 數據類型是從內嵌 DTD 或內嵌架構推斷而來。
昨日 bigint 這是上一個兄弟元素的 XML 識別碼。 如果沒有直接的上一個同層級節點，則為 NULL。
文字 ntext 包含文字格式的屬性值或項目內容。 或者，如果邊緣表格條目不需要值，則為 NULL。

使用WITH子句指定現有的數據表

您可以使用 WITH 子句來指定現有資料表的名稱。 若要這樣做，只要指定OPENXML可以使用其架構來產生資料列集的現有資料表名稱。

使用WITH子句指定架構

您可以使用 WITH 子句來指定完整的架構。 在指定資料列集架構時，您可以指定資料行名稱、其數據類型，以及它們對應至 XML 檔。

您可以使用 SchemaDeclaration 中的 ColPattern 參數來指定數據行模式。 指定的數據行模式可用來將數據列集數據行對應至 rowpattern 所識別的 XML 節點，也用來判斷對應的類型。

如果未為某列指定 ColPattern，該行集中的列會根據 flags 參數所指定的映射方式，映射至具有相同名稱的 XML 節點。 不過，如果 ColPattern 在 WITH 子句中指定為架構規格的一部分，則會覆寫 flags 參數中指定的對應。

數據列集數據行與 XML 節點之間的對應

在OPENXML語句中，您可以選擇指定資料列集數據行與 rowpattern所識別之 XML 節點之間的對應類型，例如屬性中心或元素中心。 這項資訊用於 XML 節點和數據列集數據行之間的轉換。

您可以透過兩種方式指定映射，也可以同時選擇兩者進行指定：

  • 使用 flags 參數

    flags 參數所指定的對應假設 XML 節點與具有相同名稱的列集中的相應列進行名稱對應。

  • 使用 ColPattern 參數

    COLPattern 是 XPath 運算式，在 WITH 子句中指定為 SchemaDeclaration 的一部分。 ColPattern 中指定的對應會覆寫 flags 參數所指定的對應。

    ColPattern 可用來指定對應類型，例如屬性中心或元素中心，覆寫或增強 旗標所表示的默認對應。

    ColPattern 會在下列情況下指定：

    • 數據列集中的數據行名稱與所對應的專案或屬性名稱不同。 在此情況下， ColPattern 可用來識別數據列集數據行所對應的 XML 元素和屬性名稱。

    • 您想要將中繼屬性屬性對應至欄位。 在此情況下， ColPattern 可用來識別數據列集數據行所對應的中繼屬性。 如需如何使用中繼屬性的詳細資訊，請參閱 在 OPENXML 中指定中繼屬性。

旗標和 ColPattern 參數都是選擇性的。 如果未指定對應，則會假設以屬性為中心的對應。 以屬性為中心的對應是 flags 參數的預設值。

屬性驅動的映射

將 OPENXML 中的 flags 參數設定為 1 （XML_ATTRIBUTES） 會指定 以屬性為中心的 對應。 如果 旗標 包含 XML_ATTRIBUTES，公開的資料列集合會提供或使用每個 XML 元素顯示為一列的資料。 XML 屬性會根據名稱對應到在模式宣告中定義的屬性，或者由 WITH 子句中的表名稱提供的屬性。 名稱對應表示特定名稱的 XML 屬性會儲存在數據列集中具有相同名稱的數據行中。

如果數據行名稱與它對應的屬性名稱不同，則必須指定 ColPattern 。

如果 XML 屬性具有命名空間限定符，數據列集中的數據行名稱也必須有限定符。

以元素為核心的映射

將 OPENXML 中的 flags 參數設定為 2 （XML_ELEMENTS） 會指定 以元素為中心的 對應。 這類似於 以屬性為中心的 對應，但有下列差異：

  • 對應範例的名稱對應，除非指定數據行層級模式，否則數據行對應至具有相同名稱的 XML 元素會選擇非複雜子元素。 在擷取程式中，如果子元素很複雜，因為它包含其他子元素，則數據行會設定為 NULL。 接著會忽略子元素的屬性值。

  • 針對多個具有相同名稱的子元素，會傳回第一個節點。

另請參閱

sp_xml_preparedocument（Transact-SQL）SP_XML_REMOVEDOCUMENT（Transact-SQL）OPENXML（Transact-SQL）XML 數據（SQL Server）

  • Last updated on