次の方法で共有

  • [アーティクル]

FOR XML および OPENXML の使用による XML データのパブリッシュと処理

SQL クエリを実行した結果は、標準の行セットとしてではなく XML として返すこともできます。このようなクエリは、直接実行することも、ストアド プロシージャとユーザー定義関数から実行することもできます。結果を直接取得するには、まず SELECT ステートメントの FOR XML 句を使用します。この FOR XML 句に XML モード (RAW、AUTO、EXPLICIT、または PATH) を指定します。

たとえば、次の SELECT ステートメントは、AdventureWorks データベースの Sales.Customer テーブルおよび Sales.SalesOrderHeader テーブルの情報を取得します。このクエリでは、FOR XML 句に AUTO モードを指定しています。

USE AdventureWorks
GO
SELECT Cust.CustomerID, 
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       OrderHeader.Status,
       Cust.CustomerType
FROM Sales.Customer Cust 
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
FOR XML AUTO

FOR XML 句を使用するとデータを XML ドキュメントとして取得できるのに対して、Transact-SQL OPENXML 関数を使用すると XML ドキュメントとして表されるデータを挿入できます。OPENXML は、テーブルまたはビューと似た行セットのプロバイダであり、メモリ内の XML ドキュメントに対する行セットを提供します。OPENXML を使用すると、XML ドキュメントの内部表現の行セット ビューを提供することで、XML データがリレーショナル行セットであるかのように、XML データにアクセスできます。行セット内のレコードは、データベース テーブルに格納できます。OPENXML は、SELECT ステートメントや、ソース テーブルやビューを指定できる SELECT INTO ステートメントで使用できます。

次の例では、INSERT ステートメントと SELECT ステートメントで OPENXML を使用します。サンプルの XML ドキュメントには、<Customers> 要素と <Orders> 要素が含まれています。

最初に、sp_xml_preparedocument ストアド プロシージャで XML ドキュメントを解析します。解析後のドキュメントは、XML ドキュメント内のノード (要素、属性、テキスト、コメント) をツリー形式で表現したものです。次に OPENXML はこの解析後の XML ドキュメントを参照して、この XML ドキュメントのすべてまたは一部の行セットを表示します。INSERT ステートメントで OPENXML を使用すると、このような行セットのデータをデータベース テーブルに挿入できます。複数の OPENXML 呼び出しを使用することで、XML ドキュメントの各部の行セット ビューを提供し、各部を処理 (たとえば、別のテーブルに挿入) できます。この処理は、テーブルへの XML の細分化とも呼ばれます。

次の例では、2 つの INSERT ステートメントで <Customers> 要素を Customers テーブルに格納し、<Orders> 要素を Orders テーブルに格納することで、XML ドキュメントを細分化しています。また、この例では、OPENXML を使用して XML ドキュメントから CustomerID と OrderDate を取得する SELECT ステートメントも示しています。この処理の最後に sp_xml_removedocument が呼び出されています。これは、解析時に作成された内部 XML ツリー表現を保持するために割り当てられたメモリを解放するための処理です。

-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
                ContactName varchar(20), 
                CompanyName varchar(20))
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime)
GO
DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(max) -- or xml type
SET @xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers 
SELECT * 
FROM OPENXML(@docHandle, N'/ROOT/Customers') 
  WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders 
SELECT * 
FROM OPENXML(@docHandle, N'//Orders') 
  WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle

次の図は、sp_xml_preparedocument によって作成された上記の XML ドキュメントを解析した結果の XML ツリーを表しています。

解析された XML のツリー