OPENXML (Transact-SQL)
XML ドキュメントに対して行セット ビューを提供します。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
行として処理するノードを識別するときに使用する XPath パターンを指定します。XML ドキュメントの場合、そのハンドルは idoc パラメータで渡されます。
flags
XML データとリレーショナル行セットとの間で使用するマッピング、およびオーバーフローした列の処理方法を指定します。flags はオプションの入力パラメータで、次のいずれかの値を指定できます。バイト値 説明 0
既定では、属性中心のマッピングが使用されます。
1
属性中心のマッピングを使用します。XML_ELEMENTS と組み合わせることができます。この場合、まだ処理されていないすべての列に対して、まず属性中心のマッピングが適用され、次に要素中心のマッピングが適用されます。
2
要素中心のマッピングを使用します。XML_ATTRIBUTES と組み合わせることができます。この場合、まだ処理されていないすべての列に対して、まず属性中心のマッピングが適用され、次に要素中心のマッピングが適用されます。
8
XML_ATTRIBUTES または XML_ELEMENTS と組み合わせる (論理和) ことができます。取得したコンテキストにおいて、このフラグは、使用したデータをオーバーフロー プロパティ @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 で提供されるメタプロパティの 1 つを指定します。MetaProperty を指定すると、メタプロパティで提供される情報が列に格納されます。これらのメタプロパティによって、XML ノードの情報 (相対的な位置や名前空間の情報など) を抽出でき、これにより、テキストとして表示されている情報だけでなく、さらに多くの情報を取得することができます。
- ColName
- TableName
目的のスキーマを備えたテーブルが既に存在し、列パターンが必要ない場合は、SchemaDeclaration の代わりにテーブル名を指定できます。
解説
WITH 句を、SchemaDeclaration、または既存の TableName と共に使用すると、行セットの形式、および必要に応じて追加のマッピング情報を指定できます。WITH 句の指定を省略すると、結果はエッジ テーブル形式で返されます。エッジ テーブルでは、1 つのテーブル内での詳細な XML ドキュメントの構造 (要素/属性名、ドキュメント階層、名前空間、PI など) が表されます。
次の表は、エッジ テーブルの構造についての説明です。
| 列名 | データ型 | 説明 |
|---|---|---|
id |
bigint |
ドキュメント ノードの一意な ID。 ルート要素の ID 値は 0 です。負の ID 値は予約済みです。 |
parentid |
bigint |
ノードの親の識別子。この ID によって識別される親のノードは、必ずしも要素ではなく、この ID によって親が識別されるノードの 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 ID。前に直接の兄弟がない場合は NULL になります。 |
text |
ntext |
テキスト形式の、属性値または要素の内容。エッジ テーブル エントリに値が必要ない場合は NULL になります。 |
例
A. OPENXML と共に単純な SELECT ステートメントを使用する
次の例では、sp_xml_preparedocument を使用して XML イメージの内部表現を作成します。次に、XML ドキュメントの内部表現に対して、OPENXML 行セット プロバイダを使用して SELECT ステートメントを実行します。
flag の値は、1 に設定されています。これは属性中心のマッピングを示します。したがって、XML 属性は行セット内の列にマップされます。rowpattern は /ROOT/Customer として指定します。これは <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
flags に、要素中心のマッピングを表す 2 を設定して同じ SELECT ステートメントを実行すると、XML ドキュメント内の両方の顧客について、CustomerID および ContactName の値には NULL が返されます。これは <Customers> 要素に副要素がないためです。
以下に結果セットを示します。
CustomerID ContactName
---------- -----------
NULL NULL
NULL NULL
B. 列と XML 属性の間のマッピングに ColPattern を指定する
次のクエリでは、XML ドキュメントから顧客 ID、注文日、製品 ID、および数量の属性を返します。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