OPENXML (Transact-SQL)

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

トピック リンク アイコン 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

    既定では、属性中心のマッピングが使用されます。

    1

    属性中心のマッピングを使用します。 XML_ELEMENTS と組み合わせることができます。 この場合、まだ処理されていないすべての列に対して、まず属性中心のマッピングが適用され、次に要素中心のマッピングが適用されます。

    2

    要素中心のマッピングを使用します。 XML_ATTRIBUTES と組み合わせることができます。 この場合、まだ処理されていないすべての列に対して、まず属性中心のマッピングが適用され、次に要素中心のマッピングが適用されます。

    8

    XML_ATTRIBUTES または XML_ELEMENTS と組み合わせる (論理和) ことができます。 取得したコンテキストにおいて、このフラグは、使用したデータをオーバーフロー プロパティ @mp:xmltext にコピーしないことを示します。

  • SchemaDeclaration
    スキーマ定義を次の形式で指定します。ColName ColType [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 ノードの情報 (相対的な位置や名前空間の情報など) を抽出でき、 これにより、テキストとして表示されている情報だけでなく、さらに多くの情報を取得することができます。

  • 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, @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 の使用