OPENXML (Transact-SQL)
適用対象: SQL Server
Azure SQL Database
Azure SQL Managed Instance
OPENXML
は、XML ドキュメントに対する行セット ビューを提供します。 OPENXML
は行セット プロバイダーであるため、テーブル、ビュー、OPENROWSET
関数などの行セット プロバイダーが表示される Transact-SQL ステートメントでは、OPENXML
を使用できます。
構文
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 |
既定値は attribute-centric マッピングです。 |
1 |
attribute-centric マッピングを使用します。 XML_ELEMENTS と組み合わせることができます。 この場合、 attribute-centric マッピングが最初に適用されます。 次に、残りの列 element-centric マッピングが適用されます。 |
2 |
element-centric マッピングを使用します。 XML_ATTRIBUTES と組み合わせることができます。 この場合、 element-centric マッピングが最初に適用されます。 次に、残りの列 attribute-centric マッピングが適用されます。 |
8 |
XML_ATTRIBUTES またはXML_ELEMENTS と結合 (論理 OR) できます。 取得のコンテキストにおいて、このフラグは、使用したデータをオーバーフロー プロパティ @mp:xmltext にコピーしないことを示します。 |
SchemaDeclaration
フォームのスキーマ定義: ColNameColType [ ColPattern | MetaProperty ] [ , ColNameColType [ ColPattern | MetaProperty ] ..]
ColName
行セット内の列名。
ColType
行セット内の列の SQL Server データ型。 列のデータ型が、基になる属性の xml データ型とは異なる場合、強制型変換が行われます。
ColPattern
XML ノードを列へマップする方法を表す標準 XPath パターン (省略可能)。 ColPattern が指定されていない場合は、既定のマッピング (フラグで指定された
attribute-centric
またはelement-centric
マッピング) が実行されます。ColPattern として指定された XPath パターンは、マッピングの特殊な性質 (
attribute-centric
およびelement-centric
マッピングの場合) を指定するために使用され、フラグによって示される既定のマッピングを上書きまたは拡張します。ColPattern で指定する標準 XPath パターンでは、メタプロパティもサポートされます。
MetaProperty
OPENXML
によって提供されるメタプロパティの 1 つ。 MetaProperty を指定すると、メタプロパティで提供される情報が列に格納されます。 メタプロパティによって、XML ノードの情報 (相対的な位置や名前空間の情報など) を抽出できます。 これらのメタプロパティにより、テキストとして表示されている情報よりも多くの情報が表示されます。
TableName
目的のスキーマを備えたテーブルが既に存在し、列パターンが必要ない場合に (SchemaDeclaration の代わりに) 指定できるテーブル名。
解説
WITH
句は、SchemaDeclarationまたは既存のTableNameを指定することによって、行セット形式 (および必要に応じて追加のマッピング情報) を提供します。 省略可能な WITH
句が指定されていない場合、結果は edge テーブル形式で返されます。 エッジ テーブルでは、1 つのテーブル内での詳細な XML ドキュメントの構造 (要素/属性名、ドキュメント階層、名前空間、PI など) が表されます。
次の表で、エッジ テーブルの構造について説明します。
列名 | データ型 | 説明 |
---|---|---|
id |
bigint | ドキュメント ノードの一意の ID。 ルート要素には、 |
parentid |
bigint | ノードの親の識別子。 この ID で識別される親は必ずしも親要素ではありませんが、この ID によって親が識別されるノードの nodetype によって異なります。 たとえば、ノードがテキスト ノードの場合、その親ノードは属性ノードである可能性があります。ノードが XML ドキュメントの最上位にある場合、その ParentID は NULL 。 |
nodetype |
int | ノードの型を識別します。 この値は、XML DOM ノード型の番号付けに対応する整数です。 ノードの型は次のとおりです。1 = 要素ノード2 = 属性ノード3 = テキスト ノード |
localname |
nvarchar | 要素または属性のローカル名。 NULL DOM オブジェクトに名前がない場合は 。 |
prefix |
nvarchar | ノード名の名前空間のプレフィックス。 |
namespaceuri |
nvarchar | ノードの名前空間 URI。 値が NULL の場合、名前空間は存在しません。 |
data type |
nvarchar | 要素または属性行の実際のデータ型。それ以外の場合は NULL 。 データ型は、インライン DTD またはインライン スキーマから推定されます。 |
prev |
bigint | 前の兄弟要素の XML ID。 NULL 前の兄弟が直接存在しない場合は 。 |
text |
ntext | 属性値または要素の内容をテキスト形式で格納します (または、edge テーブルエントリに値が必要ない場合はNULL されます)。 |
例
A. OPENXML を指定して基本的な SELECT ステートメントを使う
次の例では、sp_xml_preparedocument
を使用して XML イメージの内部表現を作成します。 次に、XML ドキュメントの内部表現に対して、SELECT
行セット プロバイダーを使用して OPENXML
ステートメントを実行します。
flag の値は、1
に設定されています。 この値は、マッピング attribute-centric
示します。 したがって、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
同じSELECT
ステートメントが flags 2
に設定され、element-centric
マッピングを示す場合、XML ドキュメントに CustomerID
または ContactName
という名前の要素がないため、XML ドキュメント内の両方の顧客のCustomerID
とContactName
の値がNULL
として返されます。
結果セットは次のようになります。
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
属性にマップされます。
element-centric
マッピングは 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
はedgeテーブル形式で行セットを返します。
最後に、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;