OPENXML (Transact-SQL)

9374 ワード
Transact-SQL Transact-SQL テキストリンク

OPENXMLについて

XMLの属性や要素を指定して、SQLでテーブルとして使用できる。

使用手順

  1. XMLドキュメント(文字列)をドキュメントハンドルに変換する。(sp_xml_preparedocument)
  2. SQLのテーブルを使用する箇所でOPENXML関数を呼び出す。

サンプルソース1

以下のサンプルソースではCustmor要素をレコード、その属性をカラムとして使用している。

OPENXML_SAMPLE1
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

サンプルソース2

以下のサンプルソースでは要素OrderDetailをレコード、要素OrderDetailの属性とその親要素Orderの属性をカラムとして使用している。

OPENXML_SAMPLE2
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

OPENXML呼び出し構文

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

idoc
XMLドキュメントのハンドルを指定します。 XMLドキュメントのハンドルは、sp_xml_preparedocument を呼び出すことによって作成されます。

rowpattern
行として処理するノードを識別するときに使用する XPath パターンを指定します。サンプルソース1の'/ROOT/Customer'

flags
XML データとリレーショナル行セットとの間で使用するマッピング、およびオーバーフローした列の処理方法を指定します。 flags は省略可能な入力パラメーターで、次のいずれかの値を指定できます。

バイト値 説明
0 属性中心のマッピングを使用します。デフォルト
1 属性中心のマッピングを使用します。 XML_ELEMENTS と組み合わせることができます。 この場合、まだ処理されていないすべての列に対して、まず属性中心のマッピングが適用され、次に要素中心のマッピングが適用されます。
2 要素中心のマッピングを使用します。 XML_ATTRIBUTES と組み合わせることができます。 この場合、まだ処理されていないすべての列に対して、まず属性中心のマッピングが適用され、次に要素中心のマッピングが適用されます。
8 XML_ATTRIBUTES または XML_ELEMENTS と組み合わせる (論理和) ことができます。 取得したコンテキストにおいて、このフラグは、使用したデータをオーバーフロー プロパティ @mp:xmltext にコピーしないことを示します。

SchemaDeclaration
スキーマ定義を次の形式で指定します。ColName ColType [ColPattern | MetaProperty] [,ColName ColType [ColPattern | MetaProperty]...]

ColName
カラム名
ColType
カラムのSQL Serverデータ型
ColPattern
XML ノードを列へマップする方法を表す標準 XPath パターンを指定します (省略可能)。 ColPattern の指定を省略すると、既定のマッピング (flags で指定した属性中心または要素中心のマッピング) が適用されます。ColPattern として指定した XPath パターンは、属性中心のマッピングや要素中心のマッピングの場合に、flags で指定される既定のマッピングを上書きまたは拡張する、特殊なマッピング特性を指定するときに使用されます。 ColPattern で指定する標準 XPath パターンでは、メタプロパティもサポートされます。
MetaProperty
筆者理解不能。引用元を参照してください。

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

引用

OPENXML (Transact-SQL)