OPENXML 演算子を使用した XML のインポート

クエリの FROM 句で OPENXML 演算子を使用すると、XML 文書から結果セットを生成できます。OPENXML は、XPath クエリ言語のサブセットを使用して、XML 文書からノードを選択します。

XPath 式の使用

OPENXML を使用すると、XML 文書が解析され、結果はツリーとしてモデル化されます。このツリーはノードで構成されています。XPath 式は、ツリー内のノードを選択するために使用されます。次のリストは、一般的に使用される XPath 式の一部を示しています。

/ XML 文書のルートノードを示します。
// ルートのすべての子孫を示します。ルートノードも含まれます。
. (単一のピリオド) XML 文書のカレントノードを示します。
.// カレントノードのすべての子孫を示します。カレントノードも含まれます。
.. カレントノードの親ノードを示します。
./@attributename attributename という名前を持つ、カレントノードの属性を示します。
./childname カレントノードの子で、childname という名前を持つ要素を示します。

次の XML 文書を考えてみます。

<inventory>
  <product ID="301" size="Medium">Tee Shirt
    <quantity>54</quantity>
  </product>
  <product ID="302" size="One Size fits all">Tee Shirt
    <quantity>75</quantity>
  </product>
  <product ID="400" size="One Size fits all">Baseball Cap
    <quantity>112</quantity>
  </product>
</inventory>

<inventory> 要素は、ルートノードです。この要素は、次の XPath 式を使用して参照できます。

/inventory

カレントノードが <quantity> 要素であると仮定します。このノードは、次の XPath 式を使用して参照できます。

<inventory> 要素の子である <product> 要素をすべて検出するには、次の XPath 式を使用します。

/inventory/product

カレントノードが <product> 要素のときに、size 属性を参照したい場合は、次の XPath 式を使用します。

./@size

XPath クエリ言語の詳細については、 [external link] http://www.w3.org/TR/xpathを参照してください。

OPENXML を使用した結果セットの生成

OPENXML の最初の xpath-query 引数に一致するごとに、結果セットにローが 1 つ生成されます。WITH 句は、結果セットのスキーマと、結果セット内で各カラムに値がどのように格納されるかを指定します。次のクエリを例にとります。

SELECT * FROM OPENXML(
'<inventory>
   <product>Tee Shirt
     <quantity>54</quantity>
     <color>Orange</color>
   </product>
   <product>Baseball Cap
     <quantity>112</quantity>
     <color>Black</color>
   </product>
</inventory>',
'/inventory/product' )
WITH ( Name CHAR (25) './text()',
       Quantity CHAR(3) 'quantity',
       Color CHAR(20) 'color');

最初の xpath-query 引数は、/inventory/product です。そして XML には <product> 要素が 2 つあるため、このクエリによってローが 2 つ生成されます。

WITH 句は、カラムが Name、Quantity、Color の 3 つであることを指定します。これらのカラムの値は、<product>、<quantity>、<color> の各要素から取得されます。前述のクエリは、次の結果を生成します。

Name	Quantity	Color
Tee Shirt	54	Orange
Baseball Cap	112	Black

OPENXML を使用したエッジテーブルの生成

OPENXML 演算子を使用すると、XML 文書内の各要素に対応する各行から成るエッジテーブルを生成できます。エッジテーブルを生成すると、SQL を使用して結果セット内のデータを問い合わせできます。

次の SQL 文は、XML 文書を 1 つ含むテーブルを作成します。このクエリが生成する XML には、<root> と呼ばれるルート要素があります。このルート要素は、XMLELEMENT 関数を使用して生成されています。また、ELEMENTS 修飾子を持つ FOR XML AUTO を使用して、Employees テーブル、SalesOrders テーブル、Customers テーブルの指定された各カラムに対応する要素が生成されています。

CREATE TABLE IF NOT EXISTS xmldata (xmldoc XML);
INSERT INTO xmldata WITH AUTO NAME
    SELECT XMLELEMENT( NAME root,
         (SELECT EmployeeID, Employees.GivenName, Employees.Surname,
           Customers.ID, Customers.GivenName, Customers.Surname, Customers.Phone, CompanyName,
           SalesOrders.ID, OrderDate, Region
         FROM Employees
         KEY JOIN SalesOrders        
         KEY JOIN Customers
         ORDER BY EmployeeID, Customers.ID, SalesOrders.ID
         FOR XML AUTO, ELEMENTS)) AS xmldoc;
SELECT xmldoc FROM xmldata;

生成される XML ロックは、次のようになります (結果は読みやすいようにフォーマットされています。クエリから返される結果は 1 つの連続した文字列です)。

<root>
  <Employees>
    <EmployeeID>129</EmployeeID>
    <GivenName>Philip</GivenName>
    <Surname>Chin</Surname>
     
    <Customers>
      <ID>101</ID>
      <GivenName>Michaels</GivenName>
      <Surname>Devlin</Surname>
      <Phone>2015558966</Phone>
      <CompanyName>The Power Group</CompanyName>
        <SalesOrders>
          <ID>2560</ID>
          <OrderDate>2001-03-16</OrderDate>
          <Region>Eastern</Region>
        </SalesOrders>
    </Customers>
      
    <Customers>
      <ID>103</ID>
      <GivenName>Erin</GivenName>
      <Surname>Niedringhaus</Surname>
      <Phone>2155556513</Phone>
      <CompanyName>Darling Associates</CompanyName>
        <SalesOrders>
          <ID>2451</ID>
          <OrderDate>2000-12-15</OrderDate>
          <Region>Eastern</Region>
        </SalesOrders>
    </Customers>
      
    <Customers>
      <ID>104</ID>
      <GivenName>Meghan</GivenName>
      <Surname>Mason</Surname>
      <Phone>6155555463</Phone>
      <CompanyName>P.S.C.</CompanyName>
        <SalesOrders>
          <ID>2331</ID>
          <OrderDate>2000-09-17</OrderDate>
          <Region>South</Region>
        </SalesOrders>
        
        <SalesOrders>
          <ID>2342</ID>
          <OrderDate>2000-09-28</OrderDate>
          <Region>South</Region>
        </SalesOrders>
    </Customers>
    ...
  </Employees>
  ...
  <Employees>
  ...
  </Employees>
</root>

次のクエリは、descendant-or-self (//*) XPath 式を使用して、前述の XML 文書内の各要素とのマッチングを行っています。次に、各要素に対し id メタプロパティを使用して、ノードの ID を取得しています。また、parent (../) XPath 式を id メタプロパティとともに使用して、親ノードを取得しています。localname メタプロパティは、各要素の名前を取得するために使用されています。メタプロパティ名では大文字と小文字が区別されます。そのため、ID や LOCALNAME はメタプロパティ名として使用できません。

CREATE OR REPLACE VARIABLE x XML;
SELECT xmldoc INTO x FROM xmldata;

SELECT * 
FROM OPENXML( x, '//*' )
WITH (ID INT '@mp:id',
       parent INT '../@mp:id',
       name CHAR(25) '@mp:localname',
       text LONG VARCHAR 'text()' )
ORDER BY ID;

このクエリによって生成される結果セットには、XML 文書内の各ノードの ID、親ノードの ID、各要素の名前と内容が表示されています。

ID	parent	name	text
5	(NULL)	root	(NULL)
16	5	Employees	(NULL)
28	16	EmployeeID	129
55	16	GivenName	Phillip
82	16	Surname	Chin
...	...	...	...

xp_read_file での OPENXML の使用

これまで XMLELEMENT のようなプロシージャで生成された XML を使用してきましたが、xp_read_file プロシージャを使用して、ファイルからの XML を読み込んで解析することもできます。次のクエリを使用してファイル c:\temp\inventory.xml が書き込まれたとします。

SELECT xp_write_file( 'c:\\temp\\inventory.xml',
'<inventory>
   <product>Tee Shirt
      <quantity>54</quantity>
      <color>Orange</color>
   </product>
   <product>Baseball Cap
      <quantity>112</quantity>
      <color>Black</color>
   </product>
</inventory>'
);

この場合、次の文を使用してファイル内の XML を読み込み、解析できます。

SELECT * 
FROM OPENXML( xp_read_file( 'c:\\temp\\inventory.xml' ),
  '//*' )
WITH (ID INT '@mp:id',
       parent INT '../@mp:id',
       name CHAR(128) '@mp:localname',
       text LONG VARCHAR 'text()' )
ORDER BY ID;

カラム内の XML の問い合わせ

XML を含むカラムを持つテーブルがある場合、OPENXML を使用して、カラム内のすべての XML 値を一度に問い合わせできます。これには、ラテラル派生テーブルを使用します。

次の文は、ManagerID と Reports という 2 つのカラムを持つテーブルを作成します。Reports カラムには、Employees テーブルから生成された XML データが含まれます。

CREATE TABLE IF NOT EXISTS xmltest (ManagerID INT, Reports XML);
INSERT INTO xmltest
  SELECT ManagerID, XMLELEMENT( NAME reports,
    XMLAGG( XMLELEMENT( NAME e, EmployeeID))) 
  FROM Employees
  GROUP BY ManagerID;

次のクエリを実行して、テストテーブル内のデータを表示してください。

SELECT *
FROM xmltest
ORDER BY ManagerID;

このクエリは、次の結果を生成します。

ManagerID

Reports

501

<reports>
 <e>102</e>
 <e>105</e>
 <e>160</e>
 <e>243</e>
 ...
</reports>

703

<reports>
 <e>191</e> 
 <e>750</e>
 <e>868</e>
 <e>921</e>
 ...
</reports>

902

<reports>
 <e>129</e>
 <e>195</e>
 <e>299</e>
 <e>467</e>
 ...
</reports>

1293

<reports>
 <e>148</e>
 <e>390</e>
 <e>586</e>
 <e>757</e>
 ...
</reports>

...

次のクエリは、ラテラル派生テーブルを使用して、2 つのカラムを持つ結果セットを生成しています。1 つのカラムは、各マネージャの ID をリストします。もう 1 つのカラムは、そのマネージャに報告を行う各従業員の ID をリストします。

SELECT ManagerID, EmployeeID
FROM xmltest, LATERAL( OPENXML( xmltest.Reports, '//e' )
WITH (EmployeeID INT '.') ) DerivedTable
ORDER BY ManagerID, EmployeeID;

このクエリは、次の結果を生成します。

ManagerID	EmployeeID
501	102
501	105
501	160
501	243
...	...

参照

DocCommentXchange で意見交換できます