分享方式:


OPENXML (Transact-SQL)

適用於:SQL ServerAzure SQL DatabaseAzure 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_ATTRIBUTESXML_ELEMENTS。 在擷取的內容中,此旗標表示取用的數據不應複製到溢位屬性 @mp:xmltext

SchemaDeclaration

表單的架構定義:ColNameColType [ ColPattern MetaProperty ] [ , ColNameColType [ ColPattern | MetaProperty ] ... ] |

  • ColName

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

  • ColType

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

  • ColPattern

    選擇性的一般 XPath 模式,描述 XML 節點應該如何對應至數據行。 如果未指定 ColPattern,則會進行預設對應(attribute-centricelement-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 檔案中的最上層,則其 ParentIDNULL
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/Customerrowpattern 會識別要處理的 <Customers> 節點。

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

OPENXML 資料列集提供者建立了有 2 個資料行的資料列集 (CustomerIDContactName),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 檔中兩個客戶的 和 ContactNameCustomerID,因為 XML 檔中沒有任何名為 CustomerIDContactName 的專案。

以下為結果集。

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

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

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

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

  • 資料列集中的 OrderIDCustomerIDOrderDate 會對應到由 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;