OPENXML (Transact-SQL)

文章
06/22/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

OPENXML 提供 XML 檔案的數據列集檢視。因為 OPENXML 是數據列集提供者，可用於 Transact-SQL 語句中， 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`	默認為 `attribute-centric` 對應。
`1`	`attribute-centric`使用對應。可與 `XML_ELEMENTS` 結合。在此情況下， `attribute-centric` 會先套用對應。接下來， `element-centric` 會針對任何剩餘的數據行套用對應。
`2`	`element-centric`使用對應。可與 `XML_ATTRIBUTES` 結合。在此情況下， `element-centric` 會先套用對應。接下來， `attribute-centric` 會針對任何剩餘的數據行套用對應。
`8`	可以結合（邏輯 OR）與 `XML_ATTRIBUTES` 或 `XML_ELEMENTS`。在擷取的內容中，此旗標表示取用的數據不應複製到溢位屬性 `@mp:xmltext`。

SchemaDeclaration

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

ColName

數據列集中的數據行名稱。
ColType

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

選擇性的一般 XPath 模式，描述 XML 節點應該如何對應至數據行。如果未指定 ColPattern，則會進行預設對應（attribute-centric或element-centric旗標所指定的對應）。

指定為 ColPattern 的 XPath 模式可用來指定覆寫或增強旗標所表示之預設對應的特殊本質。attribute-centricelement-centric。

指定為 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	提供元素或屬性的本機名稱。 `NULL` 如果 DOM 物件沒有名稱，則為。
`prefix`	nvarchar	節點名稱的命名空間前置詞。
`namespaceuri`	nvarchar	節點的命名空間 URI。如果值為 `NULL`，則沒有任何命名空間存在。
`data type`	nvarchar	專案或屬性資料列的實際資料類型，否則為 `NULL`。資料類型是從內嵌 DTD 或內嵌結構描述推斷。
`prev`	bigint	上一個同層級專案的 XML 識別碼。 `NULL` 如果沒有直接的上一個同層級。
`text`	ntext	包含屬性值或文字形式的項目內容（如果`NULL`邊緣資料表專案不需要值則為）。

範例

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

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

flag 值是設為 1。這個值表示 attribute-centric 對應。因此 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的旗標來執行，表示element-centric對應，則會傳回 NULLXML 檔中兩個客戶的和 ContactName 值CustomerID，因為 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 屬性。

element-centric雖然對應是由 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. 取得邊緣數據表格式的結果

意見反映

意見反映

更多資源

分享方式：

OPENXML (Transact-SQL)

Syntax

引數

idoc

rowpattern

flags

SchemaDeclaration

TableName

備註

範例

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

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

C. 取得邊緣數據表格式的結果

相關內容

意見反映

意見反映

更多資源