OPENXML (Transact-SQL)
適用於:SQL Server
Azure SQL 資料庫 Azure SQL 受控執行個體
OPENXML
提供 XML 檔案的數據列集檢視。 因為 OPENXML
是數據列集提供者,可用於 Transact-SQL 語句中, OPENXML
其中數據列集提供者,例如數據表、檢視或 OPENROWSET
函式可以出現。
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-centric
element-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
對應,則會傳回 NULL
XML 檔中兩個客戶的 和 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;