OPENXML (Transact-SQL)
OPENXML 提供 XML 文件上的資料列集檢視。因為 OPENXML 是資料列集提供者，所以 OPENXML 可以用在像是資料表、檢視表或 OPENROWSET 函數等資料列集提供者能出現的 Transact-SQL 陳述式中。
.gif "主題連結圖示")
語法
OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
引數
- idoc
這是 XML 文件內部表示的文件控制代碼。XML 文件的內部表示是由呼叫 sp_xml_preparedocument 建立的。
- rowpattern
這是用來識別要當做資料列處理的節點 (在控制代碼由 idoc 參數傳送的 XML 文件中) 之 XPath 模式。
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
這是描述 XML 節點應該如何對應到資料行的選擇性、一般 XPath 模式。如果未指定 ColPattern，會發生預設的對應 (flags 指定的屬性中心或元素中心對應)。使用指定為 ColPattern 的 XPath 模式，來指定覆寫或增強 flags 所表示預設對應的對應之特殊本質 (如果是屬性中心和元素中心對應)。
指定為 ColPattern 的一般 XPath 模式也支援中繼屬性。
- MetaProperty
這是 OPENXML 提供的中繼屬性之一。如果指定 MetaProperty，資料行會包含中繼屬性提供的資訊。中繼屬性可讓您擷取 XML 節點的相關資訊 (如相對位置和命名空間資訊)。這提供比文字表示更詳細的資訊。
- ColName
- TableName
這是當具有所要結構描述的資料表已經存在且不需要資料行模式時，可以給予的資料表名稱 (取代SchemaDeclaration)。
備註
WITH 子句以使用 SchemaDeclaration 或指定現有的 TableName，來提供資料列集格式 (以及需要的額外對應資訊)。如果未指定選擇性的 WITH 子句，會以邊緣資料表格式傳回結果。邊緣資料表代表單一資料表中的細粒 XML 文件結構 (如元素/屬性名稱、文件階層、命名空間、PI 等等)。
下表描述邊緣資料表的結構。
| 資料行名稱 | 資料類型 | 描述 |
|---|---|---|
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 文件的內部表示法執行使用 OPENXML 資料列集提供者的 SELECT 陳述式。
flag 值設成 1。這表示屬性中心對應。因此 XML 屬性是對應到資料列集中的資料行。指定為 /ROOT/Customer 的 rowpattern 識別要處理的 <Customers> 節點。
因為資料行名稱符合 XML 屬性名稱，所以沒有指定選擇性的 ColPattern (資料行模式)。
OPENXML 資料列集提供者建立了有 2 個資料行的資料列集 (CustomerID 和 ContactName)，SELECT 陳述式可由這個資料列集擷取必要的資料行 (本範例中為所有資料行)。
DECLARE @idoc int
DECLARE @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 陳述式以 flags 設為 2 來執行 (表示元素中心對應)，由於 <Customers> 元素不具有任何子元素，因此 XML 文件中兩個客戶的 CustomerID 及 ContactName 值會以 NULL 傳回。
以下為結果集：
CustomerID ContactName
---------- -----------
NULL NULL
NULL NULL
B. 指定資料行與 XML 屬性間對應的 ColPattern
下列查詢從 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
DECLARE @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">
<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
DECLARE @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