OPENXML (Transact-SQL)
XML ドキュメントに対して行セット ビューを提供します。 OPENXML は行セット プロバイダーなので、テーブル、ビュー、または OPENROWSET 関数など、行セット プロバイダーを指定できる Transact-SQL ステートメントの中で使用できます。
.gif "トピック リンク アイコン")
構文
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;