OPENXML (Transact-SQL)

[アーティクル]
05/10/2023

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

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

Transact-SQL 構文表記規則

構文

OPENXML ( idoc int [ in ]
    , rowpattern nvarchar [ in ]
    , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

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

TableName

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

注釈

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

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

列名	データ型	説明
id	bigint	ドキュメントノードの一意の ID。ルート要素の ID 値は 0 です。負の ID 値は予約済みです。
parentid	bigint	ノードの親の識別子。この ID によって識別される親は、必ずしも親要素ではなく、ノードの NodeType に依存します。その親は、この ID によって識別されます。たとえば、ノードがテキストノードである場合、その親は属性ノードである場合もあります。ノードが 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 ドキュメントの内部表現に対して、SELECT 行セットプロバイダーを使用して OPENXML ステートメントを実行します。

flag の値は、1 に設定されています。この値は属性中心のマッピングを示します。したがって、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

flags を 2 に設定して (要素中心のマッピングを示します) 同じ SELECT ステートメントを実行すると、XML ドキュメント内の両方の顧客に対する CustomerID および ContactName の値が NULL として返されます。これは、CustomerID または ContactName という名前の要素が XML ドキュメント内に存在しないためです。

結果セットは次のようになります。

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, @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;

こちらもご覧ください

例 :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. エッジテーブル形式で結果を取得する