Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (日本語) » SQL Anywhere サーバ - SQL の使用法 » データベースにおける XML » データベースにおける XML の使用 » XML 文書をリレーショナル・データとしてインポートする

 

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

openxml がサポートする XPath 構文の完全なリストについては、openxml システム・プロシージャを参照してください。

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 を使用したエッジ・テーブルの生成

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

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

XMLELEMENT 関数の詳細については、XMLELEMENT 関数 [文字列] を参照してください。

FOR XML AUTO の詳細については、FOR XML AUTO の使用を参照してください。

CREATE VARIABLE x XML;
SET x=(SELECT XMLELEMENT( NAME root,
         (SELECT * FROM Employees
         KEY JOIN SalesOrders
         KEY JOIN Customers
         FOR XML AUTO, ELEMENTS)));
SELECT x;

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

<root>
 <Employees>
  <EmployeeID>299</EmployeeID>
  <ManagerID>902</ManagerID>
  <Surname>Overbey</Surname>
  <GivenName>Rollin</GivenName>
  <DepartmentID>200</DepartmentID>
  <Street>191 Companion Ct.</Street>
  <City>Kanata</City>
  <State>CA</State>
  <Country>USA</Country>
  <PostalCode>94608</PostalCode>
  <Phone>5105557255</Phone>
  <Status>A</Status>
  <SocialSecurityNumber>025487133</SocialSecurityNumber>
  <Salary>39300.000</Salary>
  <StartDate>1987-02-19</StartDate>
  <BirthDate>1964-03-15</BirthDate>
  <BenefitHealthInsurance>Y</BenefitHealthInsurance>
  <BenefitLifeInsurance>Y</BenefitLifeInsurance>
  <BenefitDayCare>N</BenefitDayCare>
  <Sex>M</Sex>
  <SalesOrders>
  <ID>2001</ID>
  <CustomerID>101</CustomerID>
  <OrderDate>2000-03-16</OrderDate>
  <FinancialCode>r1</FinancialCode>
  <Region>Eastern</Region>
  <SalesRepresentative>299</SalesRepresentative>
    <Customers>
    <ID>101</ID>
    <Surname>Devlin</Surname>
    <GivenName>Michael</GivenName>
    <Street>114 Pioneer Avenue</Street>
    <City>Kingston</City>
    <State>NJ</State>
    <PostalCode>07070</PostalCode>
    <Phone>2015558966</Phone>
    <CompanyName>The Power Group</CompanyName>
    </Customers>
  </SalesOrders>
</Employees>
...

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

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 299
55 16 ManagerID 902
79 16 Surname Overbey
... ... ... ...
xp_read_file での openxml の使用

これまで XMLELEMENT のようなプロシージャで生成された XML を使用してきましたが、xp_read_file プロシージャを使用して、ファイルからの XML を読み込んで解析することもできます。ファイル c:\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 を読み込み、解析できます。

CREATE VARIABLE x XML;
SELECT xp_read_file( 'c:\\inventory.xml' ) 
 INTO x;
SELECT * FROM openxml( x, '//*' )
 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 test (ManagerID INT, Reports XML);
INSERT INTO test
SELECT ManagerID, XMLELEMENT( NAME reports,
              XMLAGG( XMLELEMENT( NAME e, EmployeeID))) 
FROM Employees
GROUP BY ManagerID;

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

SELECT * FROM test
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 test, LATERAL( openxml( test.Reports, '//e' )
WITH (EmployeeID INT '.') ) DerivedTable
ORDER BY ManagerID, EmployeeID;

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

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

ラテラル派生テーブルの詳細については、FROM 句を参照してください。