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
DECLARE @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 が返されます。これは <Customers> 要素に副要素がないためです。
以下に結果セットを示します。
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
DECLARE @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">
<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
DECLARE @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