OPENXML (Transact-SQL)

[アーティクル]
06/22/2024

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

OPENXML は、XML ドキュメントに対する行セットビューを提供します。 OPENXMLは行セットプロバイダーであるため、テーブル、ビュー、OPENROWSET関数などの行セットプロバイダーが表示される Transact-SQL ステートメントでは、OPENXMLを使用できます。

Transact-SQL 構文表記規則

構文

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`	既定値は `attribute-centric` マッピングです。
`1`	`attribute-centric` マッピングを使用します。 `XML_ELEMENTS` と組み合わせることができます。この場合、 `attribute-centric` マッピングが最初に適用されます。次に、残りの列 `element-centric` マッピングが適用されます。
`2`	`element-centric` マッピングを使用します。 `XML_ATTRIBUTES` と組み合わせることができます。この場合、 `element-centric` マッピングが最初に適用されます。次に、残りの列 `attribute-centric` マッピングが適用されます。
`8`	`XML_ATTRIBUTES`または`XML_ELEMENTS`と結合 (論理 OR) できます。取得のコンテキストにおいて、このフラグは、使用したデータをオーバーフロープロパティ `@mp:xmltext` にコピーしないことを示します。

SchemaDeclaration

フォームのスキーマ定義: ColNameColType [ ColPattern | MetaProperty ] [ , ColNameColType [ ColPattern | MetaProperty ] ..]

ColName

行セット内の列名。
ColType

行セット内の列の SQL Server データ型。列のデータ型が、基になる属性の xml データ型とは異なる場合、強制型変換が行われます。
ColPattern

XML ノードを列へマップする方法を表す標準 XPath パターン (省略可能)。 ColPattern が指定されていない場合は、既定のマッピング (フラグで指定されたattribute-centricまたはelement-centric マッピング) が実行されます。

ColPattern として指定された XPath パターンは、マッピングの特殊な性質 (attribute-centricおよびelement-centric マッピングの場合) を指定するために使用され、フラグによって示される既定のマッピングを上書きまたは拡張します。

ColPattern で指定する標準 XPath パターンでは、メタプロパティもサポートされます。
MetaProperty

OPENXMLによって提供されるメタプロパティの 1 つ。 MetaProperty を指定すると、メタプロパティで提供される情報が列に格納されます。メタプロパティによって、XML ノードの情報 (相対的な位置や名前空間の情報など) を抽出できます。これらのメタプロパティにより、テキストとして表示されている情報よりも多くの情報が表示されます。

TableName

目的のスキーマを備えたテーブルが既に存在し、列パターンが必要ない場合に (SchemaDeclaration の代わりに) 指定できるテーブル名。

解説

WITH句は、SchemaDeclarationまたは既存のTableNameを指定することによって、行セット形式 (および必要に応じて追加のマッピング情報) を提供します。省略可能な WITH 句が指定されていない場合、結果は edge テーブル形式で返されます。エッジテーブルでは、1 つのテーブル内での詳細な XML ドキュメントの構造 (要素/属性名、ドキュメント階層、名前空間、PI など) が表されます。

次の表で、エッジ テーブルの構造について説明します。

列名	データ型	説明
`id`	bigint	ドキュメントノードの一意の ID。ルート要素には、 ID 値があります。負の ID 値は予約済みです。
`parentid`	bigint	ノードの親の識別子。この ID で識別される親は必ずしも親要素ではありませんが、この ID によって親が識別されるノードの `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 ID。 `NULL` 前の兄弟が直接存在しない場合は。
`text`	ntext	属性値または要素の内容をテキスト形式で格納します (または、edge テーブルエントリに値が必要ない場合は`NULL`されます)。

例

A. OPENXML を指定して基本的な SELECT ステートメントを使う

次の例では、sp_xml_preparedocument を使用して XML イメージの内部表現を作成します。次に、XML ドキュメントの内部表現に対して、SELECT 行セットプロバイダーを使用して OPENXML ステートメントを実行します。

flag の値は、1 に設定されています。この値は、マッピング attribute-centric 示します。したがって、XML 属性は行セット内の列にマップされます。 rowpattern は /ROOT/Customer として指定します。これは <Customers> ノードを処理することを示します。

列名が XML 属性名と一致しているので、省略可能な ColPattern (列パターン) パラメーターは指定しません。

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ステートメントが flags 2 に設定され、element-centricマッピングを示す場合、XML ドキュメントに CustomerID または ContactName という名前の要素がないため、XML ドキュメント内の両方の顧客のCustomerIDとContactNameの値がNULLとして返されます。

結果セットは次のとおりです。

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 属性にマップされます。

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はedgeテーブル形式で行セットを返します。

最後に、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)

構文

引数

idoc

rowpattern

flags

SchemaDeclaration

TableName

解説

例

A. OPENXML を指定して基本的な SELECT ステートメントを使う

B. 列と XML 属性の間のマッピングに ColPattern を指定する

C: エッジテーブル形式で結果を取得する

フィードバック

その他のリソース

次の方法で共有

OPENXML (Transact-SQL)

構文

引数

idoc

rowpattern

flags

SchemaDeclaration

TableName

解説

例

A. OPENXML を指定して基本的な SELECT ステートメントを使う

B. 列と XML 属性の間のマッピングに ColPattern を指定する

C: エッジ テーブル形式で結果を取得する

関連するコンテンツ

フィードバック

その他のリソース

C: エッジテーブル形式で結果を取得する