OPENXML (Transact-SQL)

發行項
05/06/2023

適用於：SQL Server Azure SQL Database Azure SQL 受控執行個體

OPENXML 提供 XML 文件上的資料列集檢視。因為 OPENXML 是資料列集提供者，所以 OPENXML 可以用在像是資料表、檢視或 OPENROWSET 函數等資料列集提供者會出現的 Transact-SQL 陳述式中。

Syntax

OPENXML ( idoc int [ in ]
    , rowpattern nvarchar [ in ]
    , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]

注意

若要檢視 SQL Server 2014 （12.x）和舊版的 Transact-SQL 語法，請參閱舊版檔。

引數

idoc

XML 檔內部表示的檔案控制代碼。 XML 檔的內部標記法是藉由呼叫 sp_xml_preparedocument 來建立。

rowpattern

用來識別要當做資料列處理的節點的 XPath 模式。節點來自在 idoc 參數中傳遞其控點的 XML 文件。

flags

指出 XML 資料和關聯式資料列集之間所使用的對應，以及填入溢出資料行的方式。 flags 是選擇性的輸入參數，而且可以是下列其中一個值。

位元組值	描述
0	預設為以屬性為主的對應。
1	使用以屬性為主的對應。可以和 XML_ELEMENTS 合併使用。在此情況下，會先套用屬性中心對應。接下來，會針對任何剩餘的資料行套用項目中心對應。
2	使用以元素為主的對應。可以和 XML_ATTRIBUTES 合併使用。在此情況下，會先套用屬性中心對應。接下來，會針對任何剩餘的資料行套用項目中心對應。
8	可以和 XML_ATTRIBUTES 或 XML_ELEMENTS 合併使用 (邏輯 OR)。在擷取的內容中，此旗標表示取用的資料不應複製到溢位屬性 `@mp:xmltext` 。

SchemaDeclaration

這是表單的架構定義：ColNameColType [ ColPattern | MetaProperty ] [ ， ColNameColType [ ColPattern | MetaProperty ] ... ]

ColName

資料列集中的資料行名稱。
ColType

資料列集中資料行的 SQL Server 資料類型。如果資料行類型與屬性的基礎 xml 資料類型不同，則會發生強制型轉。
ColPattern

選擇性的一般 XPath 模式，描述 XML 節點應該如何對應至資料行。若未指定 ColPattern，則會發生預設對應 (flags 指定的屬性中心或項目中心對應)。

作為指定為 ColPattern 之 XPath 模式會用於指定對應的特殊性質 (針對屬性中心和項目中心對應)，覆寫或增強 flags 指出的預設對應。

指定為 ColPattern 的一般 XPath 模式也支援中繼屬性。
MetaProperty

OPENXML 提供的其中一個中繼屬性。如果指定 MetaProperty，資料行會包含由中繼屬性所提供的資訊。中繼屬性可讓您擷取 XML 節點的相關資訊 (如相對位置和命名空間資訊)。這些中繼屬性可提供比文字表示中可見內容更多的資訊。

TableName

如果具有所需架構的資料表已經存在且不需要資料行模式，則可以指定資料表名稱（而不是 SchemaDeclaration ）。

備註

WITH 子句會使用 SchemaDeclaration 或指定現有的 TableName，來提供資料列集格式 (以及其他需要的對應資訊)。若沒有指定選擇性的 WITH 子句，則會以邊緣資料表格式傳回結果。邊緣資料表代表單一資料表中細部 XML 檔結構（例如元素/屬性名稱、檔階層、命名空間、API 等等）。

下表說明邊緣資料表的結構。

資料行名稱	資料類型	描述
id	bigint	檔節點的唯一識別碼。根元素的識別碼值為 0。負的識別碼值會保留。
parentid	bigint	識別節點的父系。這個識別碼所識別的父系不一定是父項目，這要視其父系由這個識別碼所識別節點的 NodeType 而定。例如，如果節點是文字節點，則其父系可能是屬性節點。如果節點位於 XML 文件的最上層，其 ParentID 為 NULL。
nodetype	int	識別節點類型。這是對應到 XML DOM 節點類型編號的整數。節點類型有： 1 = 元素節點 2 = 屬性節點 3 = 文字節點
localname	nvarchar	提供元素或屬性的本機名稱。如果 DOM 物件不具有名稱，則為 NULL。
prefix	nvarchar	節點名稱的命名空間前置詞。
namespaceuri	nvarchar	節點的命名空間 URI。如果值是 NULL，表示沒有命名空間。
datatype	nvarchar	專案或屬性資料列的實際資料類型，否則為 Null。資料類型是從內嵌 DTD 或內嵌結構描述推斷。
prev	bigint	上一個同層級專案的 XML 識別碼。如果沒有直接的前一個同層級，則為 NULL。
text	ntext	包含文字形式的屬性值或項目內容 (如果邊緣資料表項目不需要值，則為 NULL)。

範例

A. 搭配 OPENXML 使用基本 SELECT 語句

下列範例會使用 sp_xml_preparedocument 來建立 XML 影像的內部表示法。然後對 XML 文件的內部表示法執行使用 SELECT 資料列集提供者的 OPENXML 陳述式。

flag 值是設為 1。此值指出屬性中心對應。因此 XML 屬性是對應到資料列集中的資料行。指定為 /ROOT/Customer 的 rowpattern 會識別要處理的 <Customers> 節點。

此處並未指定選擇性的 ColPattern (資料行模式) 參數，因為資料行名稱與 XML 屬性名稱相符。

OPENXML 資料列集提供者建立了有 2 個資料行的資料列集 (CustomerID 和 ContactName)，SELECT 陳述式可由這個資料列集擷取必要的資料行 (本範例中為所有資料行)。

DECLARE @idoc INT, @doc VARCHAR(1000);

SET @doc = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer', 1) WITH (
        CustomerID VARCHAR(10),
        ContactName VARCHAR(20)
    );

以下為結果集。

CustomerID ContactName
---------- --------------------
VINET      Paul Henriot
LILAS      Carlos Gonzlez

如果相同的 SELECT 陳述式是以「旗標」設為 2 的方式 (表示項目中心對應) 來執行，則 XML 文件中兩個客戶的 CustomerID 及 ContactName 值都會以 NULL 傳回，因為 XML 文件中沒有任何名為 CustomerID 或 ContactName 的項目。

以下為結果集。

CustomerID ContactName
---------- -----------
NULL       NULL
NULL       NULL

B. 指定 ColPattern 以在資料行與 XML 屬性之間進行對應

下列查詢會從 XML 文件傳回客戶識別碼、訂單日期、產品識別碼與數量屬性。 rowpattern 會識別 <OrderDetails> 元素。 ProductID 和 Quantity 是 <OrderDetails> 元素的屬性。然而，OrderID、CustomerID 和 OrderDate 是父元素 (<Orders>) 的屬性。

選擇性的 ColPattern 已針對下列對應指定：

資料列集中的 OrderID、CustomerID 和 OrderDate 會對應到由 XML 文件中 rowpattern 所識別節點之父系的屬性。
資料列集中的 ProdID 資料行會對應到 ProductID 屬性，而資料列集中的 Qty 資料行則會對應到 rowpattern 中所識別之節點的 Quantity 屬性。

雖然以元素為主的對應是由 flags 參數所指定，但是在 ColPattern 中指定的對應會覆寫這個對應。

DECLARE @idoc INT, @doc VARCHAR(1000);

SET @doc = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
           OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">v
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
           OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer/Order/OrderDetail', 2) WITH (
        OrderID INT '../@OrderID',
        CustomerID VARCHAR(10) '../@CustomerID',
        OrderDate DATETIME '../@OrderDate',
        ProdID INT '@ProductID',
        Qty INT '@Quantity'
    );

以下為結果集。

OrderID CustomerID           OrderDate                 ProdID    Qty
------------------------------------------------------------------------
10248      VINET       1996-07-04 00:00:00.000   11      12
10248      VINET       1996-07-04 00:00:00.000   42      10
10283      LILAS       1996-08-16 00:00:00.000   72      3

C. 取得邊緣資料表格式的結果

下列範例中的取樣 XML 文件是由 <Customers>、<Orders> 和 <Order_0020_Details> 元素組成。首先， sp_xml_preparedocument 呼叫以取得檔案控制代碼。接著將這個文件控制代碼傳送到 OPENXML。

在 OPENXML 陳述式中，rowpattern (/ROOT/Customers) 會識別要處理的 <Customers> 節點。由於並未提供 WITH 子句，因此 OPENXML 會以邊緣資料表格式傳回資料列集。

最後，SELECT 陳述式會擷取邊緣資料表中的所有資料行。

DECLARE @idoc INT, @doc VARCHAR(1000);

SET @doc = '
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
   <Orders CustomerID="VINET" EmployeeID="5" OrderDate=
           "1996-07-04T00:00:00">
      <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
      <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
   </Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
           "1996-08-16T00:00:00">
      <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
   </Orders>
</Customers>
</ROOT>';

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML(@idoc, '/ROOT/Customers')

EXEC sp_xml_removedocument @idoc;

另請參閱

範例：使用 OPENXML

OPENXML (Transact-SQL)

Syntax

引數

idoc

rowpattern

flags

SchemaDeclaration

TableName

備註

範例

A. 搭配 OPENXML 使用基本 SELECT 語句

B. 指定 ColPattern 以在資料行與 XML 屬性之間進行對應

C. 取得邊緣資料表格式的結果

另請參閱

意見反應

意見反應

其他資源