OPENXML (Transact-SQL)

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

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

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

```sql: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の属性をカラムとして使用している。

```SQL: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]...]

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

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

# 引用
[OPENXML (Transact-SQL)](https://msdn.microsoft.com/ja-jp/library/ms186918(v=sql.120).aspx)