| Oracle® XML DB開発者ガイド 12cリリース1 (12.1) B71282-04 |
|
 前 |
 次 |
Oracle Database内のXMLデータ(XML Schemaに基づくものと基づかないものの両方)をアプリケーションで問合せおよび更新する方法は多数あります。
関連項目:
XMLType記憶域の推奨事項は、 Oracle XML DBの使用方法の概要を参照してください。
XML Schemaに基づくXMLTypeの表および列の使用方法は、XML Schemaの格納と問合せ: 基本を参照してください。
XQuery Updateを使用したXMLデータの更新の詳細は、XQueryとOracle XML DBを参照してください。
XQueryは非常に汎用性と表現力に富む言語で、SQL/XML関数XMLQuery、XMLTable、XMLExistsおよびXMLCastにより、それらの表現力と計算力がSQLの長所と組み合されます。
Oracle XML DBでは、通常は次のようにXQueryを使用します。ここでの例は、それらの使用方法の違いを示すように構成されています。
Oracle XML DBリポジトリ内でXMLデータを問い合せる。
リレーショナル表またはビューを、XMLデータと同様に問い合せる。これを実行するには、XQuery関数fn:collectionを使用して、URIスキーム名oradbを使用するURIを、引数としてデータのデータベースの場所とともに引き渡します。
XMLTypeデータを問い合せ、場合により関数XMLTableを使用して結果のXMLをリレーショナル・データに分解します。
「XQueryを使用したXMLTypeデータの問合せ」を参照してください。
例5-1は、この章の他の例の一部で使用するOracle XML DBリポジトリ・リソースを作成します。
例5-1 例で使用するリソースの作成
DECLARE
res BOOLEAN;
empsxmlstring VARCHAR2(300):=
'<?xml version="1.0"?>
<emps>
<emp empno="1" deptno="10" ename="John" salary="21000"/>
<emp empno="2" deptno="10" ename="Jack" salary="310000"/>
<emp empno="3" deptno="20" ename="Jill" salary="100001"/>
</emps>';
empsxmlnsstring VARCHAR2(300):=
'<?xml version="1.0"?>
<emps xmlns="http://example.com">
<emp empno="1" deptno="10" ename="John" salary="21000"/>
<emp empno="2" deptno="10" ename="Jack" salary="310000"/>
<emp empno="3" deptno="20" ename="Jill" salary="100001"/>
</emps>';
deptsxmlstring VARCHAR2(300):=
'<?xml version="1.0"?>
<depts>
<dept deptno="10" dname="Administration"/>
<dept deptno="20" dname="Marketing"/>
<dept deptno="30" dname="Purchasing"/>
</depts>';
BEGIN
res := DBMS_XDB_REPOS.createResource('/public/emps.xml', empsxmlstring);
res := DBMS_XDB_REPOS.createResource('/public/empsns.xml', empsxmlnsstring);
res := DBMS_XDB_REPOS.createResource('/public/depts.xml', deptsxmlstring);
END;
/
重要なことは、XQueryが汎用のシーケンス操作言語であるということです。式や結果は、XMLデータでなくてもかまいません。XQueryシーケンスは数値、文字列、ブール値、日付など任意のXQuery型の項目と、様々な型のXMLノード(document-node()、element()、attribute()、text()、namespace()など)を含むことができます。
例5-2に例を示します。ここでは、SQL/XML関数XMLQueryを、異なる様々な種類の項目を含むXQueryシーケンスに適用します。
整数リテラル: 1
算術式: 2 + 3
文字列リテラル: "a"
整数のシーケンス: 100 to 102
構成されたXML要素ノード: <A>33</A>
例5-2は、カンマ演算子(,)とカッコ((、))を使用してグループ化したシーケンスの構成も示しています。
シーケンス式100 to 102の評価結果はシーケンス(100, 101, 102)なので、XMLQueryの引数は、ネストされたシーケンスを含むシーケンスです。シーケンス引数は、XQueryシーケンスと同様、自動的にフラット化されます。実際の引数は、(1, 5, "a", 100, 101, 102, <A>33</A>)となります。
例5-2 様々な型の項目シーケンスに適用されるXMLQuery
SELECT XMLQuery('(1, 2 + 3, "a", 100 to 102, <A>33</A>)'
RETURNING CONTENT) AS output
FROM DUAL;
OUTPUT
--------------------------
1 5 a 100 101 102<A>33</A>
1 row selected.
XQueryをOracle XML DBリポジトリのXMLデータとともに使用する例を示します。XQuery関数fn:docとfn:collectionは、それぞれリポジトリ内のファイルおよびフォルダのリソースを問い合せるときに使用します。
ここに示す例では、XQuery関数fn:docを使用してXMLデータを含むリポジトリ・ファイルを取得し、FLWOR式の句forおよびletを使用してXQuery変数をそのデータの部分にバインドしています。
例5-3は、Oracle XML DBリポジトリ内の2つのXML文書リソース、/public/emps.xmlおよび/public/depts.xmlを問い合せます。fn:docの使用方法および可能なFLWOR式のそれぞれの句を説明しています。
例5-3では、各種のFLWOR句が次の操作を行っています。
forは/public/emps.xml内のemp要素について反復を実行し、変数$eを各要素の値に順にバインドします。つまり、従業員全体のリストについて反復を実行し、$eを各従業員にバインドします。
letは変数$dを、/public/emps.xml内の、deptno属性が要素$eのdeptno属性と同じであるdept要素が持つdname属性のすべての値で構成されているシーケンスにバインドします(結合操作)。つまり、これは$dを、従業員$eの部門と同じ部門番号を持つすべての部門の名前にバインドします。(depts.xml内の各deptno値に対してdname値が一意であることもあり得ます)。forと異なりletは値に対して反復を行わないことに注意してください。この例で、$dは1度のみバインドされています。
forとletを同時に使用すると、タプル($e, $d)のストリームを生成できます。ここで$eはある従業員、$dはその従業員が所属するすべての部門の名前(この場合は従業員の一意の部門の一意の名前)を表します。
whereはそのタプル・ストリームをフィルタリングし、給与が100,000を超える従業員のみを残します。
order byは、フィルタリングされたタプル・ストリームを従業員番号empnoでソートします(デフォルトでは昇順)。
returnは、各タプルについて1つのemp要素を構成します。それらの要素の属性enameおよびdeptが、それぞれ入力の属性enameおよび$dを使用して構成されます。出力内の要素名と属性名empおよびenameと入力文書emps.xml内の同じ名前の間に必然的な関係はないことに注意してください。
例5-4でもFLWOR式の句をそれぞれ使用しています。この例は、組込みXQuery関数 http://www.w3.org/2003/11/xpath-functionsの名前空間にあるXQuery関数doc、count、avg、およびintegerの使用方法を示しています。この名前空間は接頭辞fnにバインドされます。
例5-4では、各種のFLWOR句が次の操作を行っています。
forは、入力文書/public/depts.xml内のdeptno属性について反復を実行し、変数$dを各属性の値に順にバインドします。
letは変数$eを、入力文書/public/emps.xmlの、deptno属性が$dという値を持つすべてのemp要素で構成されるシーケンスにバインドします(join操作)。
Together, forとletを同時に使用すると、タプル($d, $e)のストリームを生成できます。ここで$dは部門番号、$eはその部門の従業員の集合を表します。
whereはそのタプル・ストリームをフィルタリングし、複数の従業員を持つタプルのみを残します。
order byは、フィルタリングされたタプル・ストリームを平均給与の降順でソートします。平均は、名前空間fnのXQuery関数avgを、属性salaryの値に適用して計算します。この値は$eのemp要素に連結されています。
returnは、order byで生成された各タプルについて1つのbig-dept要素を構成します。big-deptのtext()ノードには、$dにバインドされた部門番号が含まれます。XQuery関数countで指定されたとおり、headcount子要素には、従業員数が$eにバインドされて含まれます。avgsal子要素には、計算された平均給与が含まれます。
例5-3 for、let、order by、whereおよびreturnを使用するFLOWR式
SELECT XMLQuery('for $e in doc("/public/emps.xml")/emps/emp
let $d :=
doc("/public/depts.xml")//dept[@deptno = $e/@deptno]/@dname
where $e/@salary > 100000
order by $e/@empno
return <emp ename="{$e/@ename}" dept="{$d}"/>'
RETURNING CONTENT) FROM DUAL;
XMLQUERY('FOR$EINDOC("/PUBLIC/EMPS.XML")/EMPS/EMPLET$D:=DOC("/PUBLIC/DEPTS.XML")
--------------------------------------------------------------------------------
<emp ename="Jack" dept="Administration"></emp><emp ename="Jill" dept="Marketing"
></emp>
1 row selected.
例5-4 組込み関数を使用したFLOWR式
SELECT XMLQuery('for $d in fn:doc("/public/depts.xml")/depts/dept/@deptno
let $e := fn:doc("/public/emps.xml")/emps/emp[@deptno = $d]
where fn:count($e) > 1
order by fn:avg($e/@salary) descending
return
<big-dept>{$d,
<headcount>{fn:count($e)}</headcount>,
<avgsal>{xs:integer(fn:avg($e/@salary))}</avgsal>}
</big-dept>'
RETURNING CONTENT) FROM DUAL;
XMLQUERY('FOR$DINFN:DOC("/PUBLIC/DEPTS.XML")/DEPTS/DEPT/@DEPTNOLET$E:=FN:DOC("/P
--------------------------------------------------------------------------------
<big-dept deptno="10"><headcount>2</headcount><avgsal>165500</avgsal></big-dept>
1 row selected.
XQueryを使用してリレーショナル表またはビューのデータをXMLデータであるかのように問い合せる例を示します。例では、XQuery関数fn:collectionを使用して、URIスキーム名oradbを使用するURIを、引数としてデータのデータベースの場所とともに引き渡します。
例5-5では、Oracle XQuery関数fn:collectionをFLWOR式で使用して、2つのリレーショナル表regionsおよびcountriesを問い合せています。どちらの表も、サンプル・データベース・スキーマHRに属しています。この例ではさらに、SQLスカラー値AsiaがXQuery変数$regionnameに渡されています。どのSQL式でも、評価することによってPASSINGを使用してXQueryに渡す値を生成できます。その場合、値はSQL*Plus変数REGIONにより与えられます。またこの値は、適切なスカラーSQLデータ型(この場合はVARCHAR2(40))にキャストする必要があります。
例5-5では、各種のFLWOR句が次の操作を行っています。
forは、fn:collectionのコールによって戻されたXML要素のシーケンスについて反復を実行します。最初のコールでは、各要素はリレーショナル表hr.regionsの行に対応し、変数$iにバインドされます。同様に、fn:collectionの2回目のコールでは、$jは、表hr.countriesの後続する行にバインドされます。regionsおよびcountriesはXMLType表ではないため、各表の行に対応する最上位要素はROWです(ラッパー要素)。行要素についての反復は順序付けられていません。
whereは両方の表から行をフィルタリングし、各表でregion_idが同一であり(region_idに結合を実行)、region_nameがAsiaである行のペアのみを残します。
returnは、hr.countries表からの行を、ROWを最上位要素とする、XMLフラグメントを含むXML文書として戻します。
例5-6では、ネストされたFLWOR式でfn:collection を使用して、リレーショナル・データを問い合せています。
例5-6では、各種のFLWOR句が次の操作を行っています。
外側のforはfn:collectionによって戻されたXML要素のシーケンスについて反復を実行します。各要素はリレーショナル表oe.warehousesの行に対応していて、変数$iにバインドされています。warehousesはXMLType表ではないので、行に対応する最上位の要素はROWとなります。行要素についての反復は順序付けられていません。
内側のforも同様にfn:collectionによって戻されたXML要素のシーケンスについて反復を実行します。各要素はリレーショナル表hr.locationsの行に対応していて、変数$jにバインドされています。
whereはタプル($i, $j)をフィルタリングし、location_idの子が、$iおよび$jに対して同じであるタプルのみを残します(location_idに基づいてjoinを実行します)。
内側のreturnは、要素STREET_ADDRESS、CITY、およびSTATE_PROVINCEのXQueryシーケンスを、すべてlocations表のROW要素$jの子、つまり、locations表の同名の列の値として構成します。
外側のreturnは内側のreturnの結果をLocation要素で囲み、それをさらにWarehouse要素で囲みます。Warehouse要素に対して、値を表warehousesのwarehouse_id列から取得したid属性を提供します。
例5-7では、SQL/XML関数XMLTableは、XQuery問合せの結果を仮想リレーショナル・データに分解するために使用されています。この例で使用されているXQuery式は 例5-6で使用されているものと同一です。XQuery式の評価結果はWarehouse要素のシーケンスです。関数XMLTableは、行がWarehouse要素である仮想リレーショナル表を生成します。正確には、この例では、仮想表の各行に対応する疑似列COLUMN_VALUEの値はXMLフラグメント(XMLType型)で、単一のWarehouse要素を持ちます。
例5-5 XMLQueryを使用したXMLとしてのリレーショナル・データの問合せ
DEFINE REGION = 'Asia'
SELECT XMLQuery('for $i in fn:collection("oradb:/HR/REGIONS"),
$j in fn:collection("oradb:/HR/COUNTRIES")
where $i/ROW/REGION_ID = $j/ROW/REGION_ID
and $i/ROW/REGION_NAME = $regionname
return $j'
PASSING CAST('®ION' AS VARCHAR2(40)) AS "regionname"
RETURNING CONTENT) AS asian_countries
FROM DUAL;
これによって、次の結果が戻されます。(ここではわかりやすいように、この結果をフォーマット出力しています。)
ASIAN_COUNTRIES ----------------------------------------- <ROW> <COUNTRY_ID>AU</COUNTRY_ID> <COUNTRY_NAME>Australia</COUNTRY_NAME> <REGION_ID>3</REGION_ID> </ROW> <ROW> <COUNTRY_ID>CN</COUNTRY_ID> <COUNTRY_NAME>China</COUNTRY_NAME> <REGION_ID>3</REGION_ID> </ROW> <ROW> <COUNTRY_ID>HK</COUNTRY_ID> <COUNTRY_NAME>HongKong</COUNTRY_NAME> <REGION_ID>3</REGION_ID> </ROW> <ROW> <COUNTRY_ID>IN</COUNTRY_ID> <COUNTRY_NAME>India</COUNTRY_NAME> <REGION_ID>3</REGION_ID> </ROW> <ROW> <COUNTRY_ID>JP</COUNTRY_ID> <COUNTRY_NAME>Japan</COUNTRY_NAME> <REGION_ID>3</REGION_ID> </ROW> <ROW> <COUNTRY_ID>SG</COUNTRY_ID> <COUNTRY_NAME>Singapore</COUNTRY_NAME> <REGION_ID>3</REGION_ID> </ROW> 1 row selected.
例5-6 ネストされたFLWOR式を使用したXMLとしてのリレーショナル・データの問合せ
CONNECT hr Enter password: password Connected. GRANT SELECT ON LOCATIONS TO OE / CONNECT oe Enter password: password Connected. SELECT XMLQuery( 'for $i in fn:collection("oradb:/OE/WAREHOUSES")/ROW return <Warehouse id="{$i/WAREHOUSE_ID}"> <Location> {for $j in fn:collection("oradb:/HR/LOCATIONS")/ROW where $j/LOCATION_ID eq $i/LOCATION_ID return ($j/STREET_ADDRESS, $j/CITY, $j/STATE_PROVINCE)} </Location> </Warehouse>' RETURNING CONTENT) FROM DUAL;
この問合せは、ネストされたFLWOR式の使用例です。アクセスするリレーショナル表は、サンプル・データベース・スキーマoeにあるwarehousesと、サンプル・データベース・スキーマHRにあるlocationsです。この例をユーザーoeとして実行するには、最初にユーザーhrとして接続し、ユーザーoeに対して、表locationsでSELECT操作を実行する権限を付与する必要があります。
これによって、次の結果が戻されます。(ここではわかりやすいように、この結果をフォーマット出力しています。)
XMLQUERY('FOR$IINFN:COLLECTION("ORADB:/OE/WAREHOUSES")/ROWRETURN<WAREHOUSEID="{$
--------------------------------------------------------------------------------
<Warehouse id="1">
<Location>
<STREET_ADDRESS>2014 Jabberwocky Rd</STREET_ADDRESS>
<CITY>Southlake</CITY>
<STATE_PROVINCE>Texas</STATE_PROVINCE>
</Location>
</Warehouse>
<Warehouse id="2">
<Location>
<STREET_ADDRESS>2011 Interiors Blvd</STREET_ADDRESS>
<CITY>South San Francisco</CITY>
<STATE_PROVINCE>California</STATE_PROVINCE>
</Location>
</Warehouse>
<Warehouse id="3">
<Location>
<STREET_ADDRESS>2007 Zagora St</STREET_ADDRESS>
<CITY>South Brunswick</CITY>
<STATE_PROVINCE>New Jersey</STATE_PROVINCE>
</Location>
</Warehouse>
<Warehouse id="4">
<Location>
<STREET_ADDRESS>2004 Charade Rd</STREET_ADDRESS>
<CITY>Seattle</CITY>
<STATE_PROVINCE>Washington</STATE_PROVINCE>
</Location>
</Warehouse>
<Warehouse id="5">
<Location>
<STREET_ADDRESS>147 Spadina Ave</STREET_ADDRESS>
<CITY>Toronto</CITY>
<STATE_PROVINCE>Ontario</STATE_PROVINCE>
</Location>
</Warehouse>
<Warehouse id="6">
<Location>
<STREET_ADDRESS>12-98 Victoria Street</STREET_ADDRESS>
<CITY>Sydney</CITY>
<STATE_PROVINCE>New South Wales</STATE_PROVINCE>
</Location>
</Warehouse>
<Warehouse id="7">
<Location>
<STREET_ADDRESS>Mariano Escobedo 9991</STREET_ADDRESS>
<CITY>Mexico City</CITY>
<STATE_PROVINCE>Distrito Federal,</STATE_PROVINCE>
</Location>
</Warehouse>
<Warehouse id="8">
<Location>
<STREET_ADDRESS>40-5-12 Laogianggen</STREET_ADDRESS>
<CITY>Beijing</CITY>
</Location>
</Warehouse>
<Warehouse id="9">
<Location>
<STREET_ADDRESS>1298 Vileparle (E)</STREET_ADDRESS>
<CITY>Bombay</CITY>
<STATE_PROVINCE>Maharashtra</STATE_PROVINCE>
</Location>
</Warehouse>
1 row selected.
例5-7 XMLTableを使用したXMLとしてのリレーショナル・データの問合せ
SELECT *
FROM XMLTable(
'for $i in fn:collection("oradb:/OE/WAREHOUSES")/ROW
return <Warehouse id="{$i/WAREHOUSE_ID}">
<Location>
{for $j in fn:collection("oradb:/HR/LOCATIONS")/ROW
where $j/LOCATION_ID eq $i/LOCATION_ID
return ($j/STREET_ADDRESS, $j/CITY, $j/STATE_PROVINCE)}
</Location>
</Warehouse>');
これにより例5-6と同じ結果が生成されます。ただし、すべてのWarehouse要素が統合され単一の行に出力されるのでなく、各Warehouse要素が別々の行として出力される点が異なります。
COLUMN_VALUE
--------------------------------------------------------
<Warehouse id="1">
<Location>
<STREET_ADDRESS>2014 Jabberwocky Rd</STREET_ADDRESS>
<CITY>Southlake</CITY>
<STATE_PROVINCE>Texas</STATE_PROVINCE>
</Location>
</Warehouse>
<Warehouse id="2">
<Location>
<STREET_ADDRESS>2011 Interiors Blvd</STREET_ADDRESS>
<CITY>South San Francisco</CITY>
<STATE_PROVINCE>California</STATE_PROVINCE>
</Location>
</Warehouse>
. . .
9 rows selected.
XQueryを使用してXMLTypeデータを問い合せる例を示します。
例5-8の問合せでは関数XMLQueryでPASSING句を使用して、XMLType列warehouse_specをコンテキスト項目としてXQueryに渡します。これにより、領域が80,000を超えるウェアハウス(/Warehouse/ Area > 80000)のそれぞれについてDetails要素が作成されます。
例5-8では、関数XMLQueryは表warehousesの各行のwarehouse_spec列に適用されます。各FLWOR句は次の操作を実行します。
forは列warehouse_spec (渡されたコンテキスト項目)の各行のWarehouse要素について反復を実行します。各要素はそれぞれ$iにバインドされます。反復は順序付けられていません。
whereはWarehouse要素をフィルタリングして、Area子の値が80,000を超えるもののみを残します。
returnはDetailsの要素のXQueryシーケンスを構成します。各要素は子要素DocksおよびRailを含みます。構成されたDocks要素のnum属性はWarehouseの子Docksのtext()値に設定されます。Railのtext()コンテンツは、要素WarehouseのRailAccess属性の値に応じて、trueまたはfalseに設定されます。
例5-8のSELECT文は、表warehousesの各行に適用されます。XMLQuery式は、XQuery式に一致しない行については空のシーケンスを戻します。New JerseyとSeattleの倉庫のみがXQuery問合せに適合するので、それらの倉庫についてのみ<Details>...</Details>が戻されます。
例5-9はSQL/XML関数XMLTableを使用して、XML Schemaに基づくデータを含むXMLType表oe.purchaseorderを問い合せます。PASSING句を使用して、XMLTableのXQuery式の引数のコンテキスト項目としてpurchaseorder表を提供します。結果として得られる仮想表の疑似列COLUMN_VALUEは、CostCenter要素が値A10を持ち、User要素が値SMCCAINを持つReference情報を含む、構成された要素A10poを保持します。この問合せは、仮想表とデータベース表purchaseorderの間の結合を実行します。
関数XMLTableのPASSING句により、XMLTypeのOBJECT_VALUEがpurchaseorder表に渡され、XPathコンテキストとして使用されます。したがって、XMLTable式はpurchaseorder表に依存します。このため、表purchaseorderは、FROMリストのXMLTable式よりも前に記述する必要があります。これは、データの依存関係が存在する状況では一般的な要件です。
注意:
問合せにおいて、PASSING句がXMLType表の列の1つを参照する場合は、常に、その表は問合せのFROMリストのXMLTable式よりも前に記述する必要があります。これは、XMLTable式がXMLType表に依存しているためです。XMLType表のアクセス行と、XMLTableによってそこから生成された行との間に1対多(1:N)の関係を保証するためには、左側(相関)結合が必要です。
例5-10で得られる結果は例5-9に類似しています。XMLTableのかわりにXMLQueryを使用してoe.purchaseorderを問い合せます。次の2つの例では、XQuery式により戻される空のシーケンスの処理が異なります。例5-9では、これらの空のシーケンスはpurchaseorder表を使用して結合されないため、SQL問合せの結果セットは全体で10行のみとなります。例5-10では、これらの空のシーケンスは、132行が含まれ、それぞれが表purchaseorderの各行に対応するSQL問合せの結果セットの一部となります。10行を除いて行はすべて空で、出力でも空行となります。紙数を節約するために、ここでは空行は省略してあります。
例5-11では、XMLTable句PASSINGおよびCOLUMNSを使用します。XQuery式は最上位のPurchaseOrder要素について反復を実行し、コスト・センターA10を含む発注書に対応するPO要素を構成します。結果のPO要素は続いてXMLTableに渡されて処理されます。
例5-11では、PurchaseOrderの子から取得したデータは、POの子、Ref、TypeおよびNameを構成するのに使用されます。Typeのコンテンツは/PurchaseOrder/SpecialInstructionsのコンテンツから取得されますが、SpecialInstructionsのクラスはTypeに対応して異なる方法で分割されます。
関数XMLTableはXQuery評価の結果を、poref、priority、およびcontactの仮想表の3つのVARCHAR2列に分割します。DEFAULT句は、Regularのデフォルトのpriorityを指定するのに使用されます。
例5-11ではRETURNING SEQUENCE BY REF句を使用していません。COLUMNS句によって戻されて使用されるXQueryシーケンスが、参照渡しではなく値渡しされます。すなわち、実ノードへの参照ではなく、ターゲット・ノードのコピーが戻されます。
戻されたシーケンスが値渡しされている場合、COLUMNS句で指定された列は、その戻されたコピーにないデータは参照できません。特に、ソース・データ内のターゲット・ノードの前にあるデータは参照できません。
COLUMNS句の列仕様からソース・データの任意の部分を参照できるようにするには、RETURNING SEQUENCE BY REF句を使用する必要があります。これを使用すると、XQuery式で生成されたシーケンスが参照によって戻されます。
例5-12に、RETURNING SEQUENCE BY REF句の使用方法を示します。この句を使用すると、reference列は、XQuery式でターゲットとなっているノード外のノードを参照できます。LineItemノードのシーケンスは参照によって戻されるため、コードはノードの完全なノード・ツリーにアクセスできます。このため、ノードReferenceを上方向、次に下方向に移動できます。
RETURNING SEQUENCE BY REF句を使用すると、XMLTableの行の生成に使用した最上位のXQuery式の評価結果を、参照によって戻すように指定できます。同様の選択を、COLUMNS句のPATH式の評価結果にも使用できます。このような結果を参照によって戻すように指定するために、XMLType (SEQUENCE) BY REFを列のデータ型として使用できます。
例5-13に、これを示します。参照によってソース文書からXMLデータを戻す、t1およびt2の2つのXMLTable表を連鎖しています。
最上位の表t1の列referenceの場合、要素LineItem外のノードに対応するため(例5-12と同様)
表t1の列partの場合、列itemがノードPart外のデータをターゲットにする表t2に渡されるため
表t1では、列partに使用される型はXMLType (SEQUENCE) BY REFであるため、partデータはPATH式、LineItem/Partでターゲットとなっているソース・データへの参照です。これが必要なのでは、表t2の列itemのPATH式は要素Part、LineItemの親の属性ItemNumberをターゲットにしているためです。partが参照であると指定しない場合、単にPart要素のコピーになるため、PATH式../@ItemNumberを使用するとエラーが発生します。
例5-14では、SQL/XML関数XMLTableを使用して、XMLTypeコレクション要素LineItemのXMLデータを、仮想表の個別の列に分割します。
関連項目:
XMLTableを複数の文書レベルに適用する具体例(マルチレベル連鎖)は、「XMLのリレーショナル・ビューの作成: XMLノードの列へのマップ」を参照してください。
例5-8 XMLQueryでPASSING句を使用したXMLType列の問合せ
SELECT warehouse_name,
XMLQuery(
'for $i in /Warehouse
where $i/Area > 80000
return <Details>
<Docks num="{$i/Docks}"/>
<Rail>{if ($i/RailAccess = "Y") then "true" else "false"}
</Rail>
</Details>'
PASSING warehouse_spec RETURNING CONTENT) big_warehouses
FROM oe.warehouses;
これによって、次の出力が生成されます。
WAREHOUSE_NAME -------------- BIG_WAREHOUSES -------------- Southlake, Texas San Francisco New Jersey <Details><Docks num=""></Docks><Rail>false</Rail></Details> Seattle, Washington <Details><Docks num="3"></Docks><Rail>true</Rail></Details> Toronto Sydney Mexico City Beijing Bombay 9 rows selected.
例5-9 XMLTABLEでXML Schemaに基づくデータを使用する
SELECT xtab.COLUMN_VALUE
FROM purchaseorder, XMLTable('for $i in /PurchaseOrder
where $i/CostCenter eq "A10"
and $i/User eq "SMCCAIN"
return <A10po pono="{$i/Reference}"/>'
PASSING OBJECT_VALUE) xtab;
COLUMN_VALUE
---------------------------------------------------
<A10po pono="SMCCAIN-20021009123336151PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336341PDT"></A10po>
<A10po pono="SMCCAIN-20021009123337173PDT"></A10po>
<A10po pono="SMCCAIN-20021009123335681PDT"></A10po>
<A10po pono="SMCCAIN-20021009123335470PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336972PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336842PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336512PDT"></A10po>
<A10po pono="SMCCAIN-2002100912333894PDT"></A10po>
<A10po pono="SMCCAIN-20021009123337403PDT"></A10po>
10 rows selected.
例5-10 XMLQUERYでのXML Schemaに基づくデータの使用
SELECT XMLQuery('for $i in /PurchaseOrder
where $i/CostCenter eq "A10"
and $i/User eq "SMCCAIN"
return <A10po pono="{$i/Reference}"/>'
PASSING OBJECT_VALUE
RETURNING CONTENT)
FROM purchaseorder;
XMLQUERY('FOR$IIN/PURCHASEORDERWHERE$I/COSTCENTEREQ"A10"AND$I/USEREQ"SMCCAIN"RET
--------------------------------------------------------------------------------
<A10po pono="SMCCAIN-20021009123336151PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336341PDT"></A10po>
<A10po pono="SMCCAIN-20021009123337173PDT"></A10po>
<A10po pono="SMCCAIN-20021009123335681PDT"></A10po>
<A10po pono="SMCCAIN-20021009123335470PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336972PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336842PDT"></A10po>
<A10po pono="SMCCAIN-20021009123336512PDT"></A10po>
<A10po pono="SMCCAIN-2002100912333894PDT"></A10po>
<A10po pono="SMCCAIN-20021009123337403PDT"></A10po>
132 rows selected.
例5-11 XMLTABLEでのPASSINGおよびCOLUMNS句の使用
SELECT xtab.poref, xtab.priority, xtab.contact
FROM purchaseorder,
XMLTable('for $i in /PurchaseOrder
let $spl := $i/SpecialInstructions
where $i/CostCenter eq "A10"
return <PO>
<Ref>{$i/Reference}</Ref>
{if ($spl eq "Next Day Air" or $spl eq "Expedite") then
<Type>Fastest</Type>
else if ($spl eq "Air Mail") then
<Type>Fast</Type>
else ()}
<Name>{$i/Requestor}</Name>
</PO>'
PASSING OBJECT_VALUE
COLUMNS poref VARCHAR2(20) PATH 'Ref',
priority VARCHAR2(8) PATH 'Type' DEFAULT 'Regular',
contact VARCHAR2(20) PATH 'Name') xtab;
POREF PRIORITY CONTACT
-------------------- -------- --------------------
SKING-20021009123336 Fastest Steven A. King
SMCCAIN-200210091233 Regular Samuel B. McCain
SMCCAIN-200210091233 Fastest Samuel B. McCain
JCHEN-20021009123337 Fastest John Z. Chen
JCHEN-20021009123337 Regular John Z. Chen
SKING-20021009123337 Regular Steven A. King
SMCCAIN-200210091233 Regular Samuel B. McCain
JCHEN-20021009123338 Regular John Z. Chen
SMCCAIN-200210091233 Regular Samuel B. McCain
SKING-20021009123335 Regular Steven X. King
SMCCAIN-200210091233 Regular Samuel B. McCain
SKING-20021009123336 Regular Steven A. King
SMCCAIN-200210091233 Fast Samuel B. McCain
SKING-20021009123336 Fastest Steven A. King
SKING-20021009123336 Fastest Steven A. King
SMCCAIN-200210091233 Regular Samuel B. McCain
JCHEN-20021009123335 Regular John Z. Chen
SKING-20021009123336 Regular Steven A. King
JCHEN-20021009123336 Regular John Z. Chen
SKING-20021009123336 Regular Steven A. King
SMCCAIN-200210091233 Regular Samuel B. McCain
SKING-20021009123337 Regular Steven A. King
SKING-20021009123338 Fastest Steven A. King
SMCCAIN-200210091233 Regular Samuel B. McCain
JCHEN-20021009123337 Regular John Z. Chen
JCHEN-20021009123337 Regular John Z. Chen
JCHEN-20021009123337 Regular John Z. Chen
SKING-20021009123337 Regular Steven A. King
JCHEN-20021009123337 Regular John Z. Chen
SKING-20021009123337 Regular Steven A. King
SKING-20021009123337 Regular Steven A. King
SMCCAIN-200210091233 Fast Samuel B. McCain
32 rows selected.
例5-12 XMLTABLEでのRETURNING SEQUENCE BY REFの使用
SELECT t.*
FROM purchaseorder,
XMLTable('/PurchaseOrder/LineItems/LineItem' PASSING OBJECT_VALUE
RETURNING SEQUENCE BY REF
COLUMNS reference VARCHAR2(30) PATH '../../Reference',
item VARCHAR2(4) PATH '@ItemNumber',
description VARCHAR2(45) PATH 'Description') t
WHERE item = 5;
REFERENCE ITEM DESCRIPTION
------------------------------ ---- ------------------------------------
AMCEWEN-20021009123336171PDT 5 Coup De Torchon (Clean Slate)
AMCEWEN-20021009123336271PDT 5 The Unbearable Lightness Of Being
PTUCKER-20021009123336191PDT 5 The Scarlet Empress
PTUCKER-20021009123336291PDT 5 The Unbearable Lightness Of Being
SBELL-20021009123336231PDT 5 Black Narcissus
SBELL-20021009123336331PDT 5 Fishing With John 1 -3
SKING-20021009123336321PDT 5 The Red Shoes
SMCCAIN-20021009123336151PDT 5 Wages of Fear
SMCCAIN-20021009123336341PDT 5 The Most Dangerous Game
VJONES-20021009123336301PDT 5 Le Trou
10 rows selected.
例5-13 連鎖XMLTABLEでの参照によるアクセスの使用
SELECT t1.reference, t2.id, t2.item
FROM purchaseorder,
XMLTable('/PurchaseOrder/LineItems' PASSING OBJECT_VALUE
RETURNING SEQUENCE BY REF
COLUMNS part XMLType (SEQUENCE) BY REF PATH 'LineItem/Part',
reference VARCHAR2(30) PATH '../Reference') t1,
XMLTable('.' PASSING t1.part
RETURNING SEQUENCE BY REF
COLUMNS id VARCHAR2(12) PATH '@Id',
item NUMBER PATH '../@ItemNumber') t2;
例5-14 XMLTABLEを使用したXMLコレクション要素のリレーショナル・データへの分解
SELECT lines.lineitem, lines.description, lines.partid,
lines.unitprice, lines.quantity
FROM purchaseorder,
XMLTable('for $i in /PurchaseOrder/LineItems/LineItem
where $i/@ItemNumber >= 8
and $i/Part/@UnitPrice > 50
and $i/Part/@Quantity > 2
return $i'
PASSING OBJECT_VALUE
COLUMNS lineitem NUMBER PATH '@ItemNumber',
description VARCHAR2(30) PATH 'Description',
partid NUMBER PATH 'Part/@Id',
unitprice NUMBER PATH 'Part/@UnitPrice',
quantity NUMBER PATH 'Part/@Quantity') lines;
LINEITEM DESCRIPTION PARTID UNITPRICE QUANTITY
-------- ------------------------------ ------------- --------- --------
11 Orphic Trilogy 37429148327 80 3
22 Dreyer Box Set 37429158425 80 4
11 Dreyer Box Set 37429158425 80 3
16 Dreyer Box Set 37429158425 80 3
8 Dreyer Box Set 37429158425 80 3
12 Brazil 37429138526 60 3
18 Eisenstein: The Sound Years 37429149126 80 4
24 Dreyer Box Set 37429158425 80 3
14 Dreyer Box Set 37429158425 80 4
10 Brazil 37429138526 60 3
17 Eisenstein: The Sound Years 37429149126 80 3
16 Orphic Trilogy 37429148327 80 4
13 Orphic Trilogy 37429148327 80 4
10 Brazil 37429138526 60 4
12 Eisenstein: The Sound Years 37429149126 80 3
12 Dreyer Box Set 37429158425 80 4
13 Dreyer Box Set 37429158425 80 4
17 rows selected.
XQuery declare namespace宣言をXQuery式のプロローグで使用して、名前空間の接頭辞を定義することができます。declare default namespaceを使用して、式のデフォルトの名前空間として名前空間を確立できます。
名前空間宣言を記述した行の末尾にセミコロン(;)を置いた場合、SQL*Plusでは、そのセミコロンがSQLの終端文字として解釈されます。SQL*Plusを使用する場合は、この点に注意が必要です。これを避けるには、次のいずれかを実行します。
同じ行のセミコロンの後にテキストを挿入する。
同じ行のセミコロンの後に、(: :)のようなコメントを挿入する。
SQL*PlusコマンドSET SQLTERMINATORを使用して、SQLの終端文字の認識を無効にする。
例5-15は、XQuery式の名前空間宣言の使用方法を示しています。
XQuery名前空間宣言は、XQuery式の外部では無効です。XMLTable式で使用する名前空間接頭辞をXQuery式の外部で宣言するにはXMLNAMESPACES句を使用します。この句はXMLTableのXQuery式の引数でも使用できるため、XQueryプロローグを別個に宣言する必要はありません。
例5-16では、XMLNAMESPACESは名前空間http://example.comに対応する接頭辞eを定義するために使用されます。このネームスペースはCOLUMNS句とXMLTable式のXQuery式で使用されます。
例5-15 XMLQUERYでの名前空間宣言の使用
SELECT XMLQuery('declare namespace e = "http://example.com";
ERROR:
ORA-01756: quoted string not properly terminated
for $i in doc("/public/empsns.xml")/e:emps/e:emp
SP2-0734: unknown command beginning "for $i in ..." - rest of line ignored.
...
-- This works - do not end the line with ";".
SELECT XMLQuery('declare namespace e = "http://example.com"; for
$i in doc("/public/empsns.xml")/e:emps/e:emp
let $d :=
doc("/public/depts.xml")//dept[@deptno=$i/@deptno]/@dname
where $i/@salary > 100000
order by $i/@empno
return <emp ename="{$i/@ename}" dept="{$d}"/>'
RETURNING CONTENT) FROM DUAL;
XMLQUERY('DECLARENAMESPACEE="HTTP://EXAMPLE.COM";FOR$IINDOC("/PUBLIC/EMPSNS.XML"
--------------------------------------------------------------------------------
<emp ename="Jack" dept=""></emp><emp ename="Jill" dept=""></emp>
-- This works too - add a comment after the ";".
SELECT XMLQuery('declare namespace e = "http://example.com"; (: :)
for $i in doc("/public/empsns.xml")/e:emps/e:emp
let $d := doc("/public/depts.xml")//dept[@deptno=$i/@deptno]/@dname
where $i/@salary > 100000
order by $i/@empno
return <emp ename="{$i/@ename}" dept="{$d}"/>'
RETURNING CONTENT) FROM DUAL;
XMLQUERY('DECLARENAMESPACEE="HTTP://EXAMPLE.COM";(::)FOR$IINDOC("/PUBLIC/EMPSNS.
--------------------------------------------------------------------------------
<emp ename="Jack" dept=""></emp><emp ename="Jill" dept=""></emp>
1 row selected.
-- This works too - tell SQL*Plus to ignore the ";".
SET SQLTERMINATOR OFF
SELECT XMLQuery('declare namespace e = "http://example.com";
for $i in doc("/public/empsns.xml")/e:emps/e:emp
let $d :=
doc("/public/depts.xml")//dept[@deptno=$i/@deptno]/@dname
where $i/@salary > 100000
order by $i/@empno
return <emp ename="{$i/@ename}" dept="{$d}"/>'
RETURNING CONTENT) FROM DUAL
/
XMLQUERY('DECLARENAMESPACEE="HTTP://EXAMPLE.COM";FOR$IINDOC("/PUBLIC/EMPSNS.XML"
--------------------------------------------------------------------------------
<emp ename="Jack" dept=""></emp><emp ename="Jill" dept=""></emp>
例5-16 XMLTABLEでのXMLNAMESPACES句の使用
SELECT * FROM XMLTable(XMLNAMESPACES ('http://example.com' AS "e"), 'for $i in doc("/public/empsns.xml") return $i/e:emps/e:emp' COLUMNS name VARCHAR2(6) PATH '@ename', id NUMBER PATH '@empno');
これによって、次の結果が戻されます。
NAME ID ------ ---------- John 1 Jack 2 Jill 3 3 rows selected.
修飾名e:enameおよびe:empnoがCOLUMNS句に存在することにより、XMLNAMESPACES句を使用する必要が生じています。そうでなければ、XQuery式のみのためにはプロローグ名前空間宣言(declare namespace e = "http://example.com")で十分です。
XMLTable式で同一の名前空間が使用されているので、デフォルトの名前空間XMLNAMESPACES (DEFAULT 'http://example.com')を使用できます。このため、修飾名$i/e:emps/e:empを、明示的接頭辞を使用せずに$i/emps/empと書いてかまいません。
様々な方法で、XMLType列および表からXMLデータを問い合せることができます。
SQL、PL/SQLまたはJavaからXMLTypeデータを選択します。
XMLQueryなどのSQL/XML関数を使用してXMLTypeデータを問い合せます。「XQueryを使用したXMLTypeデータの問合せ」を参照してください。
XQuery Full TextまたはOracle Text演算子を使用して全文検索を実行します。詳細は、「XQuery Full Textのサポート」、「XMLTypeデータの索引」および「XQueryを使用しないXMLデータの全文検索」を参照してください。
この項の各例では、SQLおよびPL/SQLを使用してXMLデータを問い合せる各種の方法を示します。例5-17では、purchaseorder表に2行を挿入してから、SQL/XML関数XMLCast、XMLQueryおよびXMLExistsを使用してそれらの行のデータを問い合せます。
例5-18では、PL/SQLカーソルを使用してXMLデータを問い合せます。ローカルのXMLTypeインスタンスを使用して、一時データが格納されます。
例5-19および例5-20はいずれも、SQL/XML関数XMLTable使用して、XML発注書からデータを抽出します。次に、そのデータをリレーショナル表に挿入します。例5-19ではSQL、例5-20ではPL/SQLを使用しています。
例5-20では、PL/SQLプロシージャを定義および使用して、XML発注書からデータを抽出し、そのデータをリレーショナル表に挿入します。
例5-21は、出荷先に文字列"Shores"が含まれ、名前に文字列"ll" (Lが2つ)が含まれている顧客から要求された発注書を一覧で表示します。これらの発注書は、顧客別にグループ化してカウントされます。この例では、XQuery Full Textを使用して全文検索を実行します。
例5-22では、XPath式で指定された文書のフラグメントを抽出します。XMLQueryにより戻されるXMLTypeインスタンスは、ノードの集合、単一のノードまたはテキスト値のいずれかになります。例5-22では、XMLTypeメソッドisFragment()を使用して、結果がフラグメントかどうかを判別します。
注意:
フラグメントはXMLType列に挿入できません。SQL/XML関数XMLQueryを使用すると、フラグメントを整形式の文書に変換できます。
例5-17 XMLTYPEデータの問合せ
INSERT INTO purchaseorder
VALUES (XMLType(bfilename('XMLDIR', 'SMCCAIN-2002091213000000PDT.xml'),
nls_charset_id('AL32UTF8')));
INSERT INTO purchaseorder
VALUES (XMLType(bfilename('XMLDIR', 'VJONES-20020916140000000PDT.xml'),
nls_charset_id('AL32UTF8')));
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Reference'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(30)) reference,
XMLCast(XMLQuery('$p/PurchaseOrder/*//User'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(30)) userid,
CASE
WHEN XMLExists('$p/PurchaseOrder/Reject/Date'
PASSING po.OBJECT_VALUE AS "p")
THEN 'Rejected'
ELSE 'Accepted'
END "STATUS",
XMLCast(XMLQuery('$p//Date'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(12)) status_date
FROM purchaseorder po
WHERE XMLExists('$p//Date' PASSING po.OBJECT_VALUE AS "p")
ORDER BY XMLCast(XMLQuery('$p//Date' PASSING po.OBJECT_VALUE AS "p"
RETURNING CONTENT)
AS VARCHAR2(12));
REFERENCE USERID STATUS STATUS_DATE -------------------------------- -------- -------- ------------ VJONES-20020916140000000PDT SVOLLMAN Accepted 2002-10-11 SMCCAIN-2002091213000000PDT SKING Rejected 2002-10-12 2 rows selected.
例5-18 PL/SQLカーソルを使用した一時XMLTYPEデータの問合せ
DECLARE
xNode XMLType;
vText VARCHAR2(256);
vReference VARCHAR2(32);
CURSOR getPurchaseOrder(reference IN VARCHAR2) IS
SELECT OBJECT_VALUE XML
FROM purchaseorder
WHERE XMLExists('$p/PurchaseOrder[Reference=$r]'
PASSING OBJECT_VALUE AS "p",
reference AS "r");
BEGIN
vReference := 'EABEL-20021009123335791PDT';
FOR c IN getPurchaseOrder(vReference) LOOP
xNode := c.XML.extract('//Requestor');
SELECT XMLSerialize(CONTENT
XMLQuery('//text()' PASSING xNode RETURNING CONTENT))
INTO vText FROM DUAL;
DBMS_OUTPUT.put_line('The Requestor for Reference '
|| vReference || ' is '|| vText);
END LOOP;
vReference := 'PTUCKER-20021009123335430PDT';
FOR c IN getPurchaseOrder(vReference) LOOP
xNode := c.XML.extract('//LineItem[@ItemNumber="1"]/Description');
SELECT XMLSerialize(CONTENT
XMLQuery('//text()' PASSING xNode RETURNING CONTENT))
INTO vText FROM DUAL;
DBMS_OUTPUT.put_line('The Description of LineItem[1] for Reference '
|| vReference || ' is '|| vText);
END LOOP;
END;
/
The Requestor for Reference EABEL-20021009123335791PDT is Ellen S. Abel
The Description of LineItem[1] for Reference PTUCKER-20021009123335430PDT is
Picnic at
Hanging Rock
PL/SQL procedure successfully completed.
例5-19 SQLを使用したXMLデータの抽出およびリレーショナル表への挿入
CREATE TABLE purchaseorder_table (reference VARCHAR2(28) PRIMARY KEY,
requestor VARCHAR2(48),
actions XMLType,
userid VARCHAR2(32),
costcenter VARCHAR2(3),
shiptoname VARCHAR2(48),
address VARCHAR2(512),
phone VARCHAR2(32),
rejectedby VARCHAR2(32),
daterejected DATE,
comments VARCHAR2(2048),
specialinstructions VARCHAR2(2048));
CREATE TABLE purchaseorder_lineitem (reference,
FOREIGN KEY ("REFERENCE")
REFERENCES "PURCHASEORDER_TABLE" ("REFERENCE") ON DELETE CASCADE,
lineno NUMBER(10), PRIMARY KEY ("REFERENCE", "LINENO"),
upc VARCHAR2(14),
description VARCHAR2(128),
quantity NUMBER(10),
unitprice NUMBER(12,2));
INSERT INTO purchaseorder_table (reference, requestor, actions, userid, costcenter, shiptoname, address,
phone, rejectedby, daterejected, comments, specialinstructions)
SELECT t.reference, t.requestor, t.actions, t.userid, t.costcenter, t.shiptoname, t.address,
t.phone, t.rejectedby, t.daterejected, t.comments, t.specialinstructions
FROM purchaseorder p,
XMLTable('/PurchaseOrder' PASSING p.OBJECT_VALUE
COLUMNS reference VARCHAR2(28) PATH 'Reference',
requestor VARCHAR2(48) PATH 'Requestor',
actions XMLType PATH 'Actions',
userid VARCHAR2(32) PATH 'User',
costcenter VARCHAR2(3) PATH 'CostCenter',
shiptoname VARCHAR2(48) PATH 'ShippingInstructions/name',
address VARCHAR2(512) PATH 'ShippingInstructions/address',
phone VARCHAR2(32) PATH 'ShippingInstructions/telephone',
rejectedby VARCHAR2(32) PATH 'Reject/User',
daterejected DATE PATH 'Reject/Date',
comments VARCHAR2(2048) PATH 'Reject/Comments',
specialinstructions VARCHAR2(2048) PATH 'SpecialInstructions') t
WHERE t.reference = 'EABEL-20021009123336251PDT';
INSERT INTO purchaseorder_lineitem (reference, lineno, upc, description, quantity, unitprice)
SELECT t.reference, li.lineno, li.upc, li.description, li.quantity, li.unitprice
FROM purchaseorder p,
XMLTable('/PurchaseOrder' PASSING p.OBJECT_VALUE
COLUMNS reference VARCHAR2(28) PATH 'Reference',
lineitem XMLType PATH 'LineItems/LineItem') t,
XMLTable('LineItem' PASSING t.lineitem
COLUMNS lineno NUMBER(10) PATH '@ItemNumber',
upc VARCHAR2(14) PATH 'Part/@Id',
description VARCHAR2(128) PATH 'Description',
quantity NUMBER(10) PATH 'Part/@Quantity',
unitprice NUMBER(12,2) PATH 'Part/@UnitPrice') li
WHERE t.reference = 'EABEL-20021009123336251PDT';
SELECT reference, userid, shiptoname, specialinstructions FROM purchaseorder_table; REFERENCE USERID SHIPTONAME SPECIALINSTRUCTIONS -------------------------------- -------- ------------------------------------------------ ------------------- EABEL-20021009123336251PDT EABEL Ellen S. Abel Counter to Counter SELECT reference, lineno, upc, description, quantity FROM purchaseorder_lineitem; REFERENCE LINENO UPC DESCRIPTION QUANTITY -------------------------------- ---------- -------------- ---------------------------------- ---------- EABEL-20021009123336251PDT 1 37429125526 Samurai 2: Duel at Ichijoji Temple 3 EABEL-20021009123336251PDT 2 37429128220 The Red Shoes 4 EABEL-20021009123336251PDT 3 715515009058 A Night to Remember 1
例5-20 PL/SQLを使用したXMLデータの抽出および表への挿入
CREATE OR REPLACE PROCEDURE insertPurchaseOrder(purchaseorder XMLType) AS reference VARCHAR2(28);
BEGIN
INSERT INTO purchaseorder_table (reference, requestor, actions, userid, costcenter, shiptoname, address,
phone, rejectedby, daterejected, comments, specialinstructions)
SELECT * FROM XMLTable('$p/PurchaseOrder' PASSING purchaseorder AS "p"
COLUMNS reference VARCHAR2(28) PATH 'Reference',
requestor VARCHAR2(48) PATH 'Requestor',
actions XMLType PATH 'Actions',
userid VARCHAR2(32) PATH 'User',
costcenter VARCHAR2(3) PATH 'CostCenter',
shiptoname VARCHAR2(48) PATH 'ShippingInstructions/name',
address VARCHAR2(512) PATH 'ShippingInstructions/address',
phone VARCHAR2(32) PATH 'ShippingInstructions/telephone',
rejectedby VARCHAR2(32) PATH 'Reject/User',
daterejected DATE PATH 'Reject/Date',
comments VARCHAR2(2048) PATH 'Reject/Comments',
specialinstructions VARCHAR2(2048) PATH 'SpecialInstructions');
INSERT INTO purchaseorder_lineitem (reference, lineno, upc, description, quantity, unitprice)
SELECT t.reference, li.lineno, li.upc, li.description, li.quantity, li.unitprice
FROM XMLTable('$p/PurchaseOrder' PASSING purchaseorder AS "p"
COLUMNS reference VARCHAR2(28) PATH 'Reference',
lineitem XMLType PATH 'LineItems/LineItem') t,
XMLTable('LineItem' PASSING t.lineitem
COLUMNS lineno NUMBER(10) PATH '@ItemNumber',
upc VARCHAR2(14) PATH 'Part/@Id',
description VARCHAR2(128) PATH 'Description',
quantity NUMBER(10) PATH 'Part/@Quantity',
unitprice NUMBER(12,2) PATH 'Part/@UnitPrice') li;
END;
CALL insertPurchaseOrder(XMLType(bfilename('XMLDIR', 'purchaseOrder.xml'), nls_charset_id('AL32UTF8')));
SELECT reference, userid, shiptoname, specialinstructions FROM purchaseorder_table; REFERENCE USERID SHIPTONAME SPECIALINSTRUCTIONS -------------------------------- -------- ------------------------------------------------ ------------------- SBELL-2002100912333601PDT SBELL Sarah J. Bell Air Mail SELECT reference, lineno, upc, description, quantity FROM purchaseorder_lineitem; REFERENCE LINENO UPC DESCRIPTION QUANTITY ------------------------- ------ ------------ ---------------------------------- -------- SBELL-2002100912333601PDT 1 715515009058 A Night to Remember 2 SBELL-2002100912333601PDT 2 37429140222 The Unbearable Lightness Of Being 2 SBELL-2002100912333601PDT 3 715515011020 Sisters 4
例5-21 SQL/XML関数を使用したXMLデータの検索
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Requestor' PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT) AS VARCHAR2(128)) name, count(*) FROM purchaseorder po WHERE XMLExists( 'declare namespace ora="http://xmlns.oracle.com/xdb"; (: :) $p/PurchaseOrder/ShippingInstructions[address/text() contains text "Shores"]' PASSING po.OBJECT_VALUE AS "p") AND XMLCast(XMLQuery('$p/PurchaseOrder/Requestor/text()' PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT) AS VARCHAR2(128)) LIKE '%ll%' GROUP BY XMLCast(XMLQuery('$p/PurchaseOrder/Requestor' PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT) AS VARCHAR2(128)); NAME COUNT(*) -------------------- ---------- Allan D. McEwen 9 Ellen S. Abel 4 Sarah J. Bell 13 William M. Smith 7
例5-22 XMLQUERYを使用したフラグメントの抽出
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Reference' PASSING po.OBJECT_VALUE AS "p"
RETURNING CONTENT)
AS VARCHAR2(30)) reference,
count(*)
FROM purchaseorder po, XMLTable('$p//LineItem[Part/@Id="37429148327"]' PASSING OBJECT_VALUE AS "p")
WHERE XMLQuery('$p/PurchaseOrder/LineItems/LineItem[Part/@Id="37429148327"]'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT).isFragment() = 1
GROUP BY XMLCast(XMLQuery('$p/PurchaseOrder/Reference' PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(30))
ORDER BY XMLCast(XMLQuery('$p/PurchaseOrder/Reference' PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(30));
REFERENCE COUNT(*)
-------------------------------- ----------
TFOX-20021009123337784PDT 3
SQL*Plus XQUERYコマンドを使用して、XQuery式を評価できます。
例5-23は、SQL*PlusコマンドXQUERYの後にXQuery式、式の後にスラッシュ(/)のみを含む行を入力することにより、SQL*PlusコマンドラインでXQuery式を直接入力する方法を示します。Oracle Databaseでは、このコマンドで送信されたXQuery式を、SQL/XML関数XMLQueryおよびXMLTable内のXQuery式と同様に処理します。実行は同一で、同じ最適化を使用します。
XQueryに固有の設定で使用できるSQL*Plus SETコマンドもいくつかあります。現在の設定を参照するにはSHOW XQUERYを使用します。
SET XQUERY BASEURI: XQUERYのベースURIを設定します。XQuery式内のURIは、このURIに相対的です。
SET XQUERY CONTEXT: 以降のXQUERY評価で使用するコンテキスト項目を指定します。
関連項目:
『SQL*Plusユーザーズ・ガイドおよびリファレンス』
例5-23 SQL*Plus XQUERYコマンドの使用
SQL> XQUERY for $i in fn:collection("oradb:/HR/DEPARTMENTS")
2 where $i/ROW/DEPARTMENT_ID < 50
3 return $i
4 /
Result Sequence
--------------------------------------------------------------------------------
<ROW><DEPARTMENT_ID>10</DEPARTMENT_ID><DEPARTMENT_NAME>Administration</DEPARTMEN
T_NAME><MANAGER_ID>200</MANAGER_ID><LOCATION_ID>1700</LOCATION_ID></ROW>
<ROW><DEPARTMENT_ID>20</DEPARTMENT_ID><DEPARTMENT_NAME>Marketing</DEPARTMENT_NAM
E><MANAGER_ID>201</MANAGER_ID><LOCATION_ID>1800</LOCATION_ID></ROW>
<ROW><DEPARTMENT_ID>30</DEPARTMENT_ID><DEPARTMENT_NAME>Purchasing</DEPARTMENT_NA
ME><MANAGER_ID>114</MANAGER_ID><LOCATION_ID>1700</LOCATION_ID></ROW>
<ROW><DEPARTMENT_ID>40</DEPARTMENT_ID><DEPARTMENT_NAME>Human Resources</DEPARTME
NT_NAME><MANAGER_ID>203</MANAGER_ID><LOCATION_ID>2400</LOCATION_ID></ROW>
XQuery API for Java (XQJ)(JSR-225とも呼ばれる)は、JavaプログラムからXQueryを使用してXMLデータにアクセスするための業界標準の方法を提供します。これにより、XMLのデータ・ソースに対してXQuery式を評価したり、その結果をXMLデータとして処理できます。
OracleではXQuery式を評価するための2つのXQueryエンジンを提供しています。1つは、Oracle XML DBにあり、データベース内のXMLデータに使用し、もう1つは、Oracle XML Developer's Kit内にあり、データベース外部のXMLデータに使用します。
同様に、中間層でのXQJ実装を2種類提供し、これら2つのXQueryエンジンへのアクセスに対応します。いずれの実装も、Oracle XML Developer's Kit (XDK)に含まれています。データがデータベースの内部または外部のどちらに存在するかに関係なく、XDKを使用すればXQJ経由でXMLデータにアクセスできます。
特に、XDKおよびXQJを使用して、Oracle XML DBのXMLデータにアクセスできます。この機能の典型的なユースケースは、ローカルのJavaプログラムからリモート・データベースに格納されているデータにアクセスすることです。
関連項目:
XQuery API for Java (XQJ) 1.0仕様(2009年3月) https://jcp.org/aboutJava/communityprocess/final/jsr225/index.html
この仕様には、わかりやすい例が含まれており、非常に具体的で役に立ちます。
Oracle XML Developer's KitでのXQJの使用の詳細は、『Oracle XML Developer's Kitプログラマーズ・ガイド』を参照してください。
XDKでXQJを使用してデータベースのXMLデータにアクセスする方法およびその例については、『Oracle XML Developer's Kitプログラマーズ・ガイド』を参照してください。
XQueryは、PL/SQL、JDBC、およびOracle Data Provider for .NET (ODP.NET)用のOracle APIとともに使用できます。
例5-24は、XQueryをPL/SQLとともに使用する方法、特に、XMLQueryのPASSING句を使用して動的変数をXQuery式にバインドする方法を示します。バインド変数:1および:2は、PL/SQLバインド変数nbitemsおよびpartidに、それぞれバインドされます。その後、それぞれXQuery変数itemnoおよびidとしてXQueryに渡されます。
例5-25は、XQueryをJDBCとともに使用して、変数をその位置によってSQL/XML関数XMLTableのPASSING句にバインドします。
例5-26は、XQueryをODP.NETおよびC#言語とともに使用する方法を示します。C#の入力パラメータ:nbitemsおよび:partidは、それぞれXQuery変数itemnoおよびidとしてXQueryに渡されます。
例5-24 XQueryをPL/SQLとともに使用する
DECLARE sql_stmt VARCHAR2(2000); -- Dynamic SQL statement to execute nbitems NUMBER := 3; -- Number of items partid VARCHAR2(20):= '715515009058'; -- Part ID result XMLType; doc DBMS_XMLDOM.DOMDocument; ndoc DBMS_XMLDOM.DOMNode; buf VARCHAR2(20000); BEGIN sql_stmt := 'SELECT XMLQuery( ''for $i in fn:collection("oradb:/OE/PURCHASEORDER") ' || 'where count($i/PurchaseOrder/LineItems/LineItem) = $itemno ' || 'and $i/PurchaseOrder/LineItems/LineItem/Part/@Id = $id ' || 'return $i/PurchaseOrder/LineItems'' ' || 'PASSING :1 AS "itemno", :2 AS "id" ' || 'RETURNING CONTENT) FROM DUAL'; EXECUTE IMMEDIATE sql_stmt INTO result USING nbitems, partid; doc := DBMS_XMLDOM.newDOMDocument(result); ndoc := DBMS_XMLDOM.makeNode(doc); DBMS_XMLDOM.writeToBuffer(ndoc, buf); DBMS_OUTPUT.put_line(buf); END; /
これによって、次の出力が生成されます。
<LineItems>
<LineItem ItemNumber="1">
<Description>Samurai 2: Duel at Ichijoji Temple</Description>
<Part Id="37429125526" UnitPrice="29.95" Quantity="3"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>The Red Shoes</Description>
<Part Id="37429128220" UnitPrice="39.95" Quantity="4"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>A Night to Remember</Description>
<Part Id="715515009058" UnitPrice="39.95" Quantity="1"/>
</LineItem>
</LineItems>
<LineItems>
<LineItem ItemNumber="1">
<Description>A Night to Remember</Description>
<Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>The Unbearable Lightness Of Being</Description>
<Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>Sisters</Description>
<Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
</LineItem>
</LineItems>
PL/SQL procedure successfully completed.
例5-25 XQueryをJDBCとともに使用する
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
import oracle.xdb.XMLType;
import java.util.*;
public class QueryBindByPos
{
public static void main(String[] args) throws Exception, SQLException
{
System.out.println("*** JDBC Access of XQuery using Bind Variables ***");
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
OracleConnection conn
= (OracleConnection)
DriverManager.getConnection("jdbc:oracle:oci8:@localhost:1521:ora11gR1", "oe", "oe");
String xqString
= "SELECT COLUMN_VALUE" +
"FROM XMLTable('for $i in fn:collection(\"oradb:/OE/PURCHASEORDER\") " +
"where $i/PurchaseOrder/Reference= $ref " +
"return $i/PurchaseOrder/LineItems' " +
"PASSING ? AS \"ref\")";
OraclePreparedStatement stmt = (OraclePreparedStatement)conn.prepareStatement(xqString);
String refString = "EABEL-20021009123336251PDT"; // Set the filter value
stmt.setString(1, refString); // Bind the string
ResultSet rs = stmt.executeQuery();
while (rs.next())
{
XMLType desc = (XMLType) rs.getObject(1);
System.out.println("LineItem Description: " + desc.getStringVal());
desc.close();
}
rs.close();
stmt.close();
}
}
これによって、次の出力が生成されます。
*** JDBC Access of Database XQuery with Bind Variables *** LineItem Description: Samurai 2: Duel at Ichijoji Temple LineItem Description: The Red Shoes LineItem Description: A Night to Remember
例5-26 XQueryをODP.NETおよびC#とともに使用する
using System;
using System.Data;
using System.Text;
using System.IO;
using System.Xml;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace XQuery
{
/// <summary>
/// Demonstrates how to bind variables for XQuery calls
/// </summary>
class XQuery
{
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main(string[] args)
{
int rows = 0;
StreamReader sr = null;
// Create the connection.
string constr = "User Id=oe;Password=***********;Data Source=ora11gr2"; // Replace with real password.
OracleConnection con = new OracleConnection(constr);
con.Open();
// Create the command.
OracleCommand cmd = new OracleCommand("", con);
// Set the XML command type to query.
cmd.CommandType = CommandType.Text;
// Create the SQL query with the XQuery expression.
StringBuilder blr = new StringBuilder();
blr.Append("SELECT COLUMN_VALUE FROM XMLTable");
blr.Append("(\'for $i in fn:collection(\"oradb:/OE/PURCHASEORDER\") ");
blr.Append(" where count($i/PurchaseOrder/LineItems/LineItem) = $itemno ");
blr.Append(" and $i/PurchaseOrder/LineItems/LineItem/Part/@Id = $id ");
blr.Append(" return $i/PurchaseOrder/LineItems\' ");
blr.Append(" PASSING :nbitems AS \"itemno\", :partid AS \"id\")");
cmd.CommandText = blr.ToString();
cmd.Parameters.Add(":nbitems", OracleDbType.Int16, 3, ParameterDirection.Input);
cmd.Parameters.Add(":partid", OracleDbType.Varchar2, "715515009058", ParameterDirection.Input);
// Get the XML document as an XmlReader.
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
// Get the XMLType column as an OracleXmlType
OracleXmlType xml = dr.GetOracleXmlType(0);
// Print the XML data in the OracleXmlType object
Console.WriteLine(xml.Value);
xml.Dispose();
// Clean up.
cmd.Dispose();
con.Close();
con.Dispose();
}
}
}
これによって、次の出力が生成されます。
<LineItems>
<LineItem ItemNumber="1">
<Description>Samurai 2: Duel at Ichijoji Temple</Description>
<Part Id="37429125526" UnitPrice="29.95" Quantity="3"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>The Red Shoes</Description>
<Part Id="37429128220" UnitPrice="39.95" Quantity="4"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>A Night to Remember</Description>
<Part Id="715515009058" UnitPrice="39.95" Quantity="1"/>
</LineItem>
</LineItems>
XMLデータが一時データか、データベース表に格納されているかに関係なく、Oracle XML DB機能を使用してXMLデータを更新できる方法は複数あります。
XML文書全体を更新するには、SQLのUPDATE文を使用します。
UPDATE文のSET句の右側にはXMLTypeインスタンスを指定する必要があります。これを作成するには、次のいずれかの方法を使用します。
XMLインスタンスを戻すSQL関数またはXMLコンストラクタを使用する方法
既存のXMLインスタンスを変更およびバインドするPL/SQL DOM APIs for XMLTypeを使用する方法
既存のXMLインスタンスを変更およびバインドするJava PL/SQL DOM APIを使用する方法
バイナリXMLとして格納されているXML Schemaに基づかないXML文書は、ピース単位で更新できます。
例5-27では、SQL UPDATE文を使用してXMLTypeインスタンスを更新します。
例5-27 SQL UPDATEを使用したXMLTypeデータの更新
SELECT t.reference, li.lineno, li.description
FROM purchaseorder po,
XMLTable('$p/PurchaseOrder' PASSING po.OBJECT_VALUE AS "p"
COLUMNS reference VARCHAR2(28) PATH 'Reference',
lineitem XMLType PATH 'LineItems/LineItem') t,
XMLTable('$l/LineItem' PASSING t.lineitem AS "l"
COLUMNS lineno NUMBER(10) PATH '@ItemNumber',
description VARCHAR2(128) PATH 'Description') li
WHERE t.reference = 'DAUSTIN-20021009123335811PDT' AND ROWNUM < 6;
REFERENCE LINENO DESCRIPTION
-------------------------------- ------- -----------------
DAUSTIN-20021009123335811PDT 1 Nights of Cabiria
DAUSTIN-20021009123335811PDT 2 For All Mankind
DAUSTIN-20021009123335811PDT 3 Dead Ringers
DAUSTIN-20021009123335811PDT 4 Hearts and Minds
DAUSTIN-20021009123335811PDT 5 Rushmore
UPDATE purchaseorder po
SET po.OBJECT_VALUE = XMLType(bfilename('XMLDIR','NEW-DAUSTIN-20021009123335811PDT.xml'),
nls_charset_id('AL32UTF8'))
WHERE XMLExists('$p/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]'
PASSING po.OBJECT_VALUE AS "p");
SELECT t.reference, li.lineno, li.description
FROM purchaseorder po,
XMLTable('$p/PurchaseOrder' PASSING po.OBJECT_VALUE AS "p"
COLUMNS reference VARCHAR2(28) PATH 'Reference',
lineitem XMLType PATH 'LineItems/LineItem') t,
XMLTable('$l/LineItem' PASSING t.lineitem AS "l"
COLUMNS lineno NUMBER(10) PATH '@ItemNumber',
description VARCHAR2(128) PATH 'Description') li
WHERE t.reference = 'DAUSTIN-20021009123335811PDT';
REFERENCE LINENO DESCRIPTION
-------------------------------- ------- --------------------------------
DAUSTIN-20021009123335811PDT 1 Dead Ringers
DAUSTIN-20021009123335811PDT 2 Getrud
DAUSTIN-20021009123335811PDT 3 Branded to Kill
新しいXML文書を作成するのではなく、SQL UPDATE文とともにXQuery Updateを使用して既存のXML文書を更新できます。選択された部分のみではなく、文書全体が更新されます。
例5-28では、SQL文字列リテラル'SKING'をXQuery式に変数($p2)として渡します。この簡単な例では、値が文字列リテラルなので、単純にreplace value of node $j with "SKING"と記述することもできました。つまり、ここでは、SQLからXQueryにリテラル文字列を渡すかわりに、リテラルなXQuery文字列を使用することもできます。実際には、実行時にのみ使用可能な値を渡すのが一般的です。例5-28にその方法を示します。これは他の例にも当てはまります。
例5-29では、複数のテキスト・ノードと属性ノードを更新します。
例5-30では、コレクション内で選択されたノードを更新します。
例5-31では、コレクション内で複数回出現するノードを、値の置換操作を使用して更新する際に発生する一般的なエラーを示します。UPDATE文で、Description要素のテキスト・ノードの値をThe Wizard of Ozに設定します。この現在のテキスト・ノードの値はSistersです。この文のWHERE句にはXMLExists式が含まれ、更新する一連のノードを識別します。
例5-31では、目的のノードのみを更新するのではなく、Description要素に属するすべてのテキスト・ノードの値を更新します。これは意図した結果と異なります。
WHERE句は、更新が必要な文書を識別するためにのみ使用でき、その文書内で更新が必要なノードの識別には使用できません。
文書が選択されると、XQuery Updateに渡されるXQuery式により、文書内で更新が必要なノードが判別されます。この例では、XQuery式によって3つすべてのDescriptionノードが識別されるため、関連する3つのテキスト・ノードすべてが更新されています。
コレクション内で複数回出現するノードを正しく更新するには、XQuery Updateに渡されるXQuery式を使用して、XML文書内の更新対象のノードを特定します。XQuery式に適切な述語を設定することによって、文書内の更新対象のノードを限定できます。例5-32では、コレクション内の1つのノードを更新する正しい方法を示します。
例5-28 SQL UPDATEおよびXQuery Updateを使用したXMLTypeデータの更新
SELECT XMLQuery('$p/PurchaseOrder/Actions/Action[1]' PASSING po.OBJECT_VALUE AS "p"
RETURNING CONTENT) action
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING po.OBJECT_VALUE AS "p");
ACTION
--------------------------------
<Action>
<User>SVOLLMAN</User>
</Action>
UPDATE purchaseorder po
SET po.OBJECT_VALUE =
XMLQuery('copy $i := $p1 modify
(for $j in $i/PurchaseOrder/Actions/Action[1]/User
return replace value of node $j with $p2)
return $i' PASSING po.OBJECT_VALUE AS "p1",
'SKING' AS "p2" RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING po.OBJECT_VALUE AS "p");
SELECT XMLQuery('$p/PurchaseOrder/Actions/Action[1]' PASSING po.OBJECT_VALUE AS "p"
RETURNING CONTENT) action
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING po.OBJECT_VALUE AS "p");
ACTION
---------------------------------
<Action>
<User>SKING</User>
</Action>
例5-29 複数のテキスト・ノードと属性ノードの更新
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Requestor'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(30)) name,
XMLQuery('$p/PurchaseOrder/LineItems'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT) lineitems
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING po.OBJECT_VALUE AS "p");
NAME LINEITEMS
---------------- ------------------------------------------------------------------------
Sarah J. Bell <LineItems>
<LineItem ItemNumber="1">
<Description>A Night to Remember</Description>
<Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>The Unbearable Lightness Of Being</Description>
<Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>Sisters</Description>
<Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
</LineItem>
</LineItems>
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery('copy $i := $p1 modify
((for $j in $i/PurchaseOrder/Requestor
return replace value of node $j with $p2),
(for $j in $i/PurchaseOrder/LineItems/LineItem[1]/Part/@Id
return replace value of node $j with $p3),
(for $j in $i/PurchaseOrder/LineItems/LineItem[1]/Description
return replace value of node $j with $p4),
(for $j in $i/PurchaseOrder/LineItems/LineItem[3]
return replace node $j with $p5))
return $i'
PASSING OBJECT_VALUE AS "p1",
'Stephen G. King' AS "p2",
'786936150421' AS "p3",
'The Rock' AS "p4",
XMLType('<LineItem ItemNumber="99">
<Description>Dead Ringers</Description>
<Part Id="715515009249" UnitPrice="39.95" Quantity="2"/>
</LineItem>') AS "p5"
RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING OBJECT_VALUE AS "p");
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Requestor'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(30)) name,
XMLQuery('$p/PurchaseOrder/LineItems'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT) lineitems
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING po.OBJECT_VALUE AS "p");
NAME LINEITEMS
---------------- ------------------------------------------------------------------
Stephen G. King <LineItems>
<LineItem ItemNumber="1">
<Description>The Rock</Description>
<Part Id="786936150421" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>The Unbearable Lightness Of Being</Description>
<Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="99">
<Description>Dead Ringers</Description>
<Part Id="715515009249" UnitPrice="39.95" Quantity="2"/>
</LineItem>
</LineItems>
例5-30 コレクション内で選択されたノードの更新
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Requestor'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(30)) name,
XMLQuery('$p/PurchaseOrder/LineItems'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT) lineitems
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING po.OBJECT_VALUE AS "p");
NAME LINEITEMS
---------------- ----------------------------------------------------------------
Sarah J. Bell <LineItems>
<LineItem ItemNumber="1">
<Description>A Night to Remember</Description>
<Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>The Unbearable Lightness Of Being</Description>
<Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>Sisters</Description>
<Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
</LineItem>
</LineItems>
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery(
'copy $i := $p1 modify
((for $j in $i/PurchaseOrder/Requestor
return replace value of node $j with $p2),
(for $j in $i/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]/@Quantity
return replace value of node $j with $p3),
(for $j in $i/PurchaseOrder/LineItems/LineItem
[Description/text()="The Unbearable Lightness Of Being"]
return replace node $j with $p4))
return $i'
PASSING OBJECT_VALUE AS "p1",
'Stephen G. King' AS "p2",
25 AS "p3",
XMLType('<LineItem ItemNumber="99">
<Part Id="786936150421" Quantity="5" UnitPrice="29.95"/>
<Description>The Rock</Description>
</LineItem>') AS "p4"
RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING OBJECT_VALUE AS "p");
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Requestor'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(30)) name,
XMLQuery('$p/PurchaseOrder/LineItems'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT) lineitems
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING po.OBJECT_VALUE AS "p");
NAME LINEITEMS
---------------- -------------------------------------------------------------
Stephen G. King <LineItems>
<LineItem ItemNumber="1">
<Description>A Night to Remember</Description>
<Part Id="715515009058" UnitPrice="39.95" Quantity="25"/>
</LineItem>
<LineItem ItemNumber="99">
<Part Id="786936150421" Quantity="5" UnitPrice="29.95"/>
<Description>The Rock</Description>
</LineItem>
<LineItem ItemNumber="3">
<Description>Sisters</Description>
<Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
</LineItem>
</LineItems>
例5-31 コレクション内で複数回出現するノードの間違った更新
SELECT XMLCast(des.COLUMN_VALUE AS VARCHAR2(256))
FROM purchaseorder,
XMLTable('$p/PurchaseOrder/LineItems/LineItem/Description'
PASSING OBJECT_VALUE AS "p") des
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING OBJECT_VALUE AS "p");
XMLCAST(DES.COLUMN_VALUEASVARCHAR2(256))
----------------------------------------
The Lady Vanishes
The Unbearable Lightness Of Being
Sisters
3 rows selected.
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery('copy $i := $p1 modify
(for $j in $i/PurchaseOrder/LineItems/LineItem/Description
return replace value of node $j with $p2)
return $i'
PASSING OBJECT_VALUE AS "p1", 'The Wizard of Oz' AS "p2"
RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder/LineItems/LineItem[Description="Sisters"]'
PASSING OBJECT_VALUE AS "p")
AND XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING OBJECT_VALUE AS "p");
1 row updated.
SELECT XMLCast(des.COLUMN_VALUE AS VARCHAR2(256))
FROM purchaseorder,
XMLTable('$p/PurchaseOrder/LineItems/LineItem/Description'
PASSING OBJECT_VALUE AS "p") des
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING OBJECT_VALUE AS "p");
XMLCAST(DES.COLUMN_VALUEASVARCHAR2(256))
----------------------------------------
The Wizard of Oz
The Wizard of Oz
The Wizard of Oz
3 rows selected.
例5-32 コレクション内で複数回出現するノードの正しい更新
SELECT XMLCast(des.COLUMN_VALUE AS VARCHAR2(256))
FROM purchaseorder,
XMLTable('$p/PurchaseOrder/LineItems/LineItem/Description'
PASSING OBJECT_VALUE AS "p") des
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING OBJECT_VALUE AS "p");
XMLCAST(DES.COLUMN_VALUEASVARCHAR2(256))
----------------------------------------
A Night to Remember
The Unbearable Lightness Of Being
Sisters
3 rows selected.
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery('copy $i := $p1 modify
(for $j in $i/PurchaseOrder/LineItems/LineItem/Description
[text()="Sisters"]
return replace value of node $j with $p2)
return $i'
PASSING OBJECT_VALUE AS "p1",
'The Wizard of Oz' AS "p2" RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING OBJECT_VALUE AS "p");
1 row updated.
SELECT XMLCast(des.COLUMN_VALUE AS VARCHAR2(256))
FROM purchaseorder,
XMLTable('$p/PurchaseOrder/LineItems/LineItem/Description'
PASSING OBJECT_VALUE AS "p") des
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING OBJECT_VALUE AS "p");
XMLCAST(DES.COLUMN_VALUEASVARCHAR2(256))
----------------------------------------
A Night to Remember
The Unbearable Lightness Of Being
The Wizard of Oz
3 rows selected.
XMLデータをNULL値に更新する場合は、特定の考慮事項が適用されます。
XML要素をNULLに更新すると、要素の属性および子が削除され、要素は空になります。要素のタイプおよび名前空間プロパティは保持されます。例5-33を参照してください。
属性値をNULLに更新した場合、値は空の文字列として表示されます。例5-33を参照してください。
要素のテキスト・ノードをNULLに更新すると、その要素のコンテンツ(テキスト)は削除されます。要素自体は残りますが、空になります。 例5-34を参照してください。
例5-33では、次のものすべてをNULLに更新します。
Description要素と、Part要素の属性Id値が715515009058のLineItem要素のQuantity属性
Description要素のコンテンツ(テキスト)が「The Unbearable Lightness Of Being」であるLineItem要素
例5-33は、ノードの値を削除する2つの異なる、ただし同等の方法を示しています。要素Descriptionおよび属性Quantityでは、既存の値がリテラルな空のXQueryシーケンス()で直接置換されます。要素LineItemでは、SQL NULLがXQuery式に渡され、空のノード値が提供されます。使用されている値がリテラルなので、SQLからXQueryに値を渡さないため、より簡単です。ただし、実際には、実行時にのみ使用可能な値を渡すのが一般的です。例5-33では、空のXQueryシーケンスでこれを行う(SQL NULL値を渡す)方法を示しています。
例5-34では、Description属性の値が「A Night to Remember」であるPart要素のテキスト・ノードをNULLに更新します。この例のXMLデータは、修正された別の発注書XML Schemaに対応しています(「コピーに基づく拡張の使用例」を参照)。そのXML Schemaでは、DescriptionはPart要素の属性であり、兄弟要素ではありません。
関連項目:
例5-33 NULL更新: 要素および属性
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Requestor'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(30)) name,
XMLQuery('$p/PurchaseOrder/LineItems'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT) lineitems
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING po.OBJECT_VALUE AS "p");
NAME LINEITEMS
---------------- -------------------------------------------------------------------
Sarah J. Bell <LineItems>
<LineItem ItemNumber="1">
<Description>A Night to Remember</Description>
<Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>The Unbearable Lightness Of Being</Description>
<Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>Sisters</Description>
<Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
</LineItem>
</LineItems>
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery(
'copy $i := $p1 modify
((for $j in $i/PurchaseOrder/LineItems/LineItem[Part/@Id="715515009058"]/Description
return replace value of node $j with ()) ,
(for $j in $i/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]/@Quantity
return replace value of node $j with ()) ,
(for $j in $i/PurchaseOrder/LineItems/LineItem
[Description/text()= "The Unbearable Lightness Of Being"]
return replace node $j with $p2))
return $i'
PASSING OBJECT_VALUE AS "p1", NULL AS "p2"
RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING OBJECT_VALUE AS "p");
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Requestor'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(30)) name,
XMLQuery('$p/PurchaseOrder/LineItems'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT) lineitems
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING po.OBJECT_VALUE AS "p");
NAME LINEITEMS
---------------- ----------------------------------------------------------------
Sarah J. Bell <LineItems>
<LineItem ItemNumber="1">
<Description/>
<Part Id="715515009058" UnitPrice="39.95" Quantity=""/>
</LineItem>
<LineItem/>
<LineItem ItemNumber="3">
<Description>Sisters</Description>
<Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
</LineItem>
</LineItems>
例5-34 NULL更新: テキスト・ノード
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/LineItems/LineItem/Part[@Description="A Night to Remember"]'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(128)) part
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[@Reference="SBELL-2003030912333601PDT"]'
PASSING po.OBJECT_VALUE AS "p");
PART
----
<Part Description="A Night to Remember" UnitCost="39.95">715515009058</Part>
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery(
'copy $i := $p1 modify
(for $j in $i/PurchaseOrder/LineItems/LineItem/Part[@Description="A Night to Remember"]
return replace value of node $j with $p2)
return $i
PASSING OBJECT_VALUE AS "p1", NULL AS "p2" RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[@Reference="SBELL-2003030912333601PDT"]'
PASSING OBJECT_VALUE AS "p");
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/LineItems/LineItem/Part[@Description="A Night to Remember"]'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(128)) part
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[@Reference="SBELL-2003030912333601PDT"]'
PASSING po.OBJECT_VALUE AS "p");
PART
----
<Part Description="A Night to Remember" UnitCost="39.95"/>
XQuery Updateを使用して、親のXML要素の下に新しい子(単一の属性または同じ型の1つ以上の要素)を挿入できます。挿入のターゲットであるXML文書は、XML Schemaに基づく文書でも、XML Schemaに基づかない文書でもかまいません。
例5-35では、新しいLineItem要素を、要素LineItemsの子として挿入します。Oracle XQueryプラグマora:child-element-nameを使用して、挿入された子要素の名前をLineItemと指定することに注意してください。
更新するXMLデータがXMLスキーマに基づいていて、ネームスペースを参照する場合、挿入するデータも同じネームスペースを参照する必要があります。参照しない場合は、挿入されるデータがXMLスキーマに準拠しないため、エラーが発生します。
注意:
XML Schemaに基づくデータの更新にXQuery Updateを使用する場合は、更新されたデータをXML Schemaに基づく列または表に戻すことを試みるとエラーが発生することに注意してください。このことを回避するには、XQueryプラグマora:transform_keep_schemaを使用します。「Oracle XQuery拡張式プラグマ」を参照してください。
例5-36は例5-35と似ていますが、挿入されるLineItem要素が名前空間を参照しています。これは、関連するXML Schemaでこの要素に対する名前空間が必要であることを前提としています。
例5-37では、1番目のLineItem要素の前に、LineItem要素を挿入します。
例5-38では、Date要素を、Action要素の最後の子として挿入します。
例5-35 コレクションへの要素の挿入
SELECT XMLQuery('$p/PurchaseOrder/LineItems/LineItem[@ItemNumber=222]'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING po.OBJECT_VALUE AS "p");
XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER=222]'
---------------------------------------------------------------
1 row selected.
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery('copy $i := $p1 modify
(for $j in $i/PurchaseOrder/LineItems
return (# ora:child-element-name LineItem #)
{insert node $p2 into $j})
return $i'
PASSING OBJECT_VALUE AS "p1",
XMLType('<LineItem ItemNumber="222">
<Description>The Harder They Come</Description>
<Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
</LineItem>') AS "p2"
RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING OBJECT_VALUE AS "p");
SELECT XMLQuery('$p/PurchaseOrder/LineItems/LineItem[@ItemNumber=222]'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING po.OBJECT_VALUE AS "p");
XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER=222]'
---------------------------------------------------------------
<LineItem ItemNumber="222">
<Description>The Harder They Come</Description>
<Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
</LineItem>
1 row selected.
例5-36 名前空間を使用する要素の挿入
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery('declare namespace e = "films.xsd"; (: :)
copy $i := $p1 modify
(for $j in $i/PurchaseOrder/LineItems
return (# ora:child-element-name e:LineItem #)
{insert node $p2 into $j})
return $i'
PASSING OBJECT_VALUE AS "p1",
XMLType('<e:LineItem ItemNumber="222">
<Description>The Harder They Come</Description>
<Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
</e:LineItem>') AS "p2"
RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING OBJECT_VALUE AS "p");
例5-37 要素の前への要素の挿入
SELECT XMLQuery('$p/PurchaseOrder/LineItems/LineItem[1]'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING po.OBJECT_VALUE AS "p");
XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[1]'PASSINGPO.OBJECT_
------------------------------------------------------------------
<LineItem ItemNumber="1">
<Description>Salesman</Description>
<Part Id="37429158920" UnitPrice="39.95" Quantity="2"/>
</LineItem>
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery('copy $i := $p1 modify
(for $j in $i/PurchaseOrder/LineItems/LineItem[1]
return insert node $p2 before $j)
return $i'
PASSING OBJECT_VALUE AS "p1",
XMLType('<LineItem ItemNumber="314">
<Description>Brazil</Description>
<Part Id="314159265359" UnitPrice="69.95"
Quantity="2"/>
</LineItem>') AS "p2"
RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING OBJECT_VALUE AS "p");
SELECT XMLQuery('$p/PurchaseOrder/LineItems/LineItem[position() <= 2]'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING po.OBJECT_VALUE AS "p");
XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[POSITION()<=2]'PASSINGPO.OBJECT_
------------------------------------------------------------------------------
<LineItem ItemNumber="314">
<Description>Brazil</Description>
<Part Id="314159265359" UnitPrice="69.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="1">
<Description>Salesman</Description>
<Part Id="37429158920" UnitPrice="39.95" Quantity="2"/>
</LineItem>
例5-38 最後の子要素としての要素の挿入
SELECT XMLQuery('$p/PurchaseOrder/Actions/Action[1]'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING po.OBJECT_VALUE AS "p");
XMLQUERY('$P/PURCHASEORDER/ACTIONS/ACTION[1]'PASSINGPO.OBJECT_VALUE
-------------------------------------------------------------------
<Action>
<User>KPARTNER</User>
</Action>
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery('copy $i := $p1 modify
(for $j in $i/PurchaseOrder/Actions/Action[1]
return insert nodes $p2 as last into $j)
return $i'
PASSING OBJECT_VALUE AS "p1",
XMLType('<Date>2002-11-04</Date>') AS "p2"
RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING OBJECT_VALUE AS "p");
SELECT XMLQuery('$p/PurchaseOrder/Actions/Action[1]'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING po.OBJECT_VALUE AS "p");
XMLQUERY('$P/PURCHASEORDER/ACTIONS/ACTION[1]'PASSINGPO.OBJECT_VALUE
-------------------------------------------------------------------
<Action>
<User>KPARTNER</User>
<Date>2002-11-04</Date>
</Action>
例では、XQuery Updateを使用してXMLノードを削除します。
例5-39では、ItemNumber属性に値222が設定されているLineItem要素を削除します。
例5-39 要素の削除
SELECT XMLQuery('$p/PurchaseOrder/LineItems/LineItem[@ItemNumber=222]'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING po.OBJECT_VALUE AS "p");
XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER=222]'PASSINGPO
------------------------------------------------------------------------
<LineItem ItemNumber="222">
<Description>The Harder They Come</Description>
<Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
</LineItem>
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery('copy $i := $p modify
delete nodes $i/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]
return $i'
PASSING OBJECT_VALUE AS "p" RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING OBJECT_VALUE AS "p");
SELECT XMLQuery('$p/PurchaseOrder/LineItems/LineItem[@ItemNumber=222]'
PASSING po.OBJECT_VALUE AS "p" RETURNING CONTENT)
FROM purchaseorder po
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING po.OBJECT_VALUE AS "p");
XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER=222]'PASSINGPO
------------------------------------------------------------------------
1 row selected.
XQuery Updateを使用して、XMLデータの新しいビューを作成できます。
例5-40では、表purchaseorderのビューを作成します。
例5-40 更新されたXMLデータを使用したビューの作成
CREATE OR REPLACE VIEW purchaseorder_summary OF XMLType AS
SELECT XMLQuery('copy $i := $p1 modify
((for $j in $i/PurchaseOrder/Actions
return replace value of node $j with ()),
(for $j in $i/PurchaseOrder/ShippingInstructions
return replace value of node $j with ()),
(for $j in $i/PurchaseOrder/LineItems
return replace value of node $j with ()))
return $i'
PASSING OBJECT_VALUE AS "p1" RETURNING CONTENT)
FROM purchaseorder p;
SELECT OBJECT_VALUE FROM purchaseorder_summary
WHERE XMLExists('$p/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]'
PASSING OBJECT_VALUE AS "p");
OBJECT_VALUE
---------------------------------------------------------------------------
<PurchaseOrder
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation=
"http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
<Reference>DAUSTIN-20021009123335811PDT</Reference>
<Actions/>
<Reject/>
<Requestor>David L. Austin</Requestor>
<User>DAUSTIN</User>
<CostCenter>S30</CostCenter>
<ShippingInstructions/>
<SpecialInstructions>Courier</SpecialInstructions>
<LineItems/>
</PurchaseOrder>
XQuery式を使用するSQL問合せは、多くの場合、1つ以上の方法で自動的にリライト(最適化)できます。このような最適化は、XML問合せのリライトまたは最適化と呼ばれます。これが発生すると、XQuery式はXML文書に対して直接評価されますが、メモリー内でDOMは構成されません。
XPath式は、XQuery式の適切なサブセットです。XPathリライトは、XML問合せリライトのサブセットであり、XPath式を使用する問合せがリライトされます。
XPathリライトには、次のものがすべて含まれます。
バイナリXMLとして格納されているXMLTypeデータのシングルパス・ストリーミング - 一連のXPath式が、データの単一のスキャンで評価されます。
XMLIndex最適化 - XPath式を使用するSQL文が同等のSQL文にリライトされますが、リライト後の文ではXPath式を使用せずにリレーショナルXMLIndex表を参照します。リライトされたSQL文も、基礎となるXMLIndex表で任意のBツリー索引を使用できます。
オブジェクト・リレーショナル形式で格納されているXMLTypeデータ、またはXMLTypeビューの最適化 - XPath式を使用するSQL文が、XPath式を使用しないかわりにXMLTypeデータの基礎となるオブジェクト・リレーショナルまたはリレーショナル・データ構造を参照する同等の文にリライトされます。リライトされたSQL文も、基礎となるデータ構造で任意のBツリー索引を使用できます。これは、問合せでも更新操作でも発生することがあります。
問合せのチューニングは、SQLのパフォーマンスと同様に、XQueryのパフォーマンスも改善することができます。XQueryのパフォーマンスをチューニングするには、該当するXML記憶域モデルと索引を選択します。
一般のデータベースの問合せと同様に、チューニングが必要かどうかは、問合せの実行計画をチェックして決定します。計画が最適でない場合は、次のマニュアルで個別のチューニング情報を参照してください。
オブジェクト・リレーショナル記憶域: オブジェクト・リレーショナル記憶域のXPathリライト
バイナリXML記憶域: XMLTypeデータの索引
また、次の式は処理コストがかかることがあり、そのため、大量のデータを処理する場合にパフォーマンスにオーバーヘッドが発生する可能性があることに注意してください。
非推奨の次のOracle SQL関数を使用するSQL式(XPath式引数を受け入れる):
appendChildXML (かわりにinsertChildXMLafterを使用)
insertXMLafter (かわりにinsertChildXMLafterを使用)
insertXMLbefore (かわりにinsertChildXMLbeforeを使用)
次の軸を使用するXQuery式(かわりにforwardおよびdescendentの軸を使用):
ancestor
ancestor-or-self
descendant-or-self
following
following-sibling
namespace
parent
preceding
preceding-sibling
ノード・アイデンティティを伴うXQuery式(順序比較演算子<<および>>の使用など)
次の各項では、XQueryとOracle XML DBで示した例の一部に対する実行計画を示し、それがどのように実行されるかを説明します。
「リレーショナル・データに関するXQueryの最適化」: fn:collectionおよびURIスキームoradbを使用して、その場で作成されたXMLデータをターゲットとするXQuery式の例。
「XML Schemaに基づくXMLTypeデータに対するXQueryの最適化」: オブジェクト・リレーショナル形式でXML Schemaに基づくXMLType表をターゲットとするXQuery式の例を示します
XQuery式を使用する問合せに対しては、使用されているXMLType記憶域モデルや索引付けなどの様々な要因によって、競合する最適化候補が複数あります。
デフォルトでは、Oracle XML DBは、優先順位付きのルール・セットに従って、特定の問合せおよびコンテキストに対して使用する最適化候補を決定します。この動作は、ルールベースのXML問合せのリライトと呼ばれます。
また、Oracle XML DBはコストベースのXML問合せのリライトを使用することもできます。このモードでは、Oracle XML DBは、特定の問合せに対する様々なXML最適化候補のパフォーマンスを評価し、最もパフォーマンスが高いと予測される組合せを選択します。
オプティマイザ・ヒント/*+ COST_XML_QUERY_REWRITE */を使用すると、特定のSQL文に対してコストベースの最適化を強制できます。
リレーショナル・データに対するSQL/XML関数XMLQueryおよびXMLTableの使用は、最適化できます。
例5-41は、XMLとしてアクセスされるリレーショナル・データに対するXMLQueryの最適化を示します。例5-42に、同じコンテキストにおけるXMLTableの最適化を示します。
例5-41 リレーショナル・データに関するXMLQueryの最適化
例5-6の問合せと、その実行計画をもう一度見てみます。この例は、問合せが最適化されていることを示しています。
SELECT XMLQuery(
'for $i in fn:collection("oradb:/OE/WAREHOUSES")/ROW
return <Warehouse id="{$i/WAREHOUSE_ID}">
<Location>
{for $j in fn:collection("oradb:/HR/LOCATIONS")/ROW
where $j/LOCATION_ID eq $i/LOCATION_ID
return ($j/STREET_ADDRESS, $j/CITY, $j/STATE_PROVINCE)}
</Location>
</Warehouse>'
RETURNING CONTENT) FROM DUAL;
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- Plan hash value: 3341889589 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 41 | | | | 2 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 | 41 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | | 0 (0)| 00:00:01 | | 4 | SORT AGGREGATE | | 1 | 6 | | | | 5 | TABLE ACCESS FULL | WAREHOUSES | 9 | 54 | 2 (0)| 00:00:01 | | 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("LOCATION_ID"=:B1) 18 rows selected.
例5-42 リレーショナル・データに関するXMLTableの最適化
例5-7の問合せと、その実行計画をもう一度見てみます。この例は、問合せが最適化されていることを示しています。
SELECT *
FROM XMLTable(
'for $i in fn:collection("oradb:/OE/WAREHOUSES")/ROW
return <Warehouse id="{$i/WAREHOUSE_ID}">
<Location>
{for $j in fn:collection("oradb:/HR/LOCATIONS")/ROW
where $j/LOCATION_ID eq $i/LOCATION_ID
return ($j/STREET_ADDRESS, $j/CITY, $j/STATE_PROVINCE)}
</Location>
</Warehouse>');
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------- Plan hash value: 1021775546 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 54 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 41 | | | | 2 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 | 41 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | | 0 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | WAREHOUSES | 9 | 54 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("LOCATION_ID"=:B1) 16 rows selected.
SQL/XML関数XMLQueryおよびXMLTableのXML Schemaに基づくデータの使用は、最適化できます。
例5-43に、XML Schemaに基づくXMLType表に関するXMLQueryの最適化を示します。例5-44に、同じコンテキストにおけるXMLTableの最適化を示します。
例5-43 XML Schemaに基づくXMLTypeデータを含むXMLQueryの最適化
例5-10の問合せと、その実行計画をもう一度見てみます。この例は、問合せが最適化されていることを示しています。
SELECT XMLQuery('for $i in /PurchaseOrder
where $i/CostCenter eq "A10"
and $i/User eq "SMCCAIN"
return <A10po pono="{$i/Reference}"/>'
PASSING OBJECT_VALUE
RETURNING CONTENT)
FROM purchaseorder;
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------- Plan hash value: 3611789148 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 530 | 5 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | |* 2 | FILTER | | | | | | | 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL| PURCHASEORDER | 1 | 530 | 5 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(:B1='SMCCAIN' AND :B2='A10') 4 - filter(SYS_CHECKACL("ACLOID","OWNERID",xmltype('<privilege xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd DAV:http://xmlns.oracle.com/xdb/dav.xsd"> <read-properties/><read-contents/></privilege>'))=1) 22 rows selected.
例5-44 XML Schemaに基づくXMLTypeデータを含むXMLTableの最適化
例5-14の問合せと、その実行計画をもう一度見てみます。この例は、問合せが最適化されていることを示しています。XQueryの結果は生成されません。結果セット全体を生成するにはXMLのコレクション要素LineItemの基礎となる記憶域の列が使用されます。
SELECT lines.lineitem, lines.description, lines.partid,
lines.unitprice, lines.quantity
FROM purchaseorder,
XMLTable('for $i in /PurchaseOrder/LineItems/LineItem
where $i/@ItemNumber >= 8
and $i/Part/@UnitPrice > 50
and $i/Part/@Quantity > 2
return $i'
PASSING OBJECT_VALUE
COLUMNS lineitem NUMBER PATH '@ItemNumber',
description VARCHAR2(30) PATH 'Description',
partid NUMBER PATH 'Part/@Id',
unitprice NUMBER PATH 'Part/@UnitPrice',
quantity NUMBER PATH 'Part/@Quantity') lines;
----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 384 | 7 (0)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 4 | 384 | 7 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL | PURCHASEORDER | 1 | 37 | 5 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | SYS_C005478 | 17 | | 1 (0)| 00:00:01 | |* 5 | TABLE ACCESS BY INDEX ROWID| LINEITEM_TABLE | 3 | 177 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(SYS_CHECKACL("ACLOID","OWNERID",xmltype('<privilege xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd DAV:http://xmlns.oracle.com/xdb/dav.xsd"><read-prop erties/><read-contents/></privilege>'))=1) 4 - access("NESTED_TABLE_ID"="PURCHASEORDER"."SYS_NC0003400035$") 5 - filter("SYS_NC00013$">50 AND "SYS_NC00012$">2 AND "ITEMNUMBER">=8 AND "SYS_NC_TYPEID$" IS NOT NULL) 25 rows selected.
この例は、表oe.purchaseorderを完全に検索します。それぞれの発注文書について、XMLTable式が評価されます。purchaseorder表でなくXMLTable式にSQL問合せの実行をまかせる方が効率的です。
XQuery式はリレーショナル式にリライトされていますが、基礎となるリレーショナル・データに基づいて索引を作成することによって最適化を改善できます。この問合せは、純粋なSQL問合せを最適化する場合と同じ方法で最適化できます。これは、Oracle XML DBのXQueryには常にあてはまります。SQLで使用する最適化テクニックをそのまま使用できます。
コレクション要素LineItemのUnitPrice属性は、適切な索引ターゲットです。適用されるXMLスキーマが、Ordered Collection Table(OCT)を使用してLineItem要素を格納することを指定しています。
ただし、このOCTの名前は、XML発注書がXML Schemaに基づくデータとして分解されたときに、Oracle XML DBにより生成されます。サンプル・データベース・スキーマHRの表purchaseorderを使用するかわりに、プロパティとデータが同じで、OCTにわかりやすい名前が付いた新しいpurchaseorder表を(異なるデータベース・スキーマに)手動で作成します。
これが完了していると仮定して、次の文が適切な索引を作成します。
CREATE INDEX unitprice_index ON lineitem_table("PART"."UNITPRICE");
この索引が定義されている場合、例5-14の問合せにより、XMLTable式が全体の評価を実行することを示す実行結果が結果として得られます。
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- Plan hash value: 1578014525 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 624 | 8 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 3 | 624 | 8 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN| SYS_IOT_TOP_49323 | 3 | 564 | 5 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN| UNITPRICE_INDEX | 20 | | 2 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN| SYS_C004411 | 1 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SYS_NC00013$">50) filter("ITEMNUMBER">=8 AND "SYS_NC00012$">2) 3 - access("SYS_NC00013$">50) 4 - access("NESTED_TABLE_ID"="PURCHASEORDER"."SYS_NC0003400035$") Note ----- - dynamic sampling used for this statement 23 rows selected.
SQLコードの実行計画を調べて、XQueryの最適化が行われるか、計画が部分的に最適化されるかを判断できます。
後者の場合は、計画の直後に次のような注記が表示されます。
Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
また、実行計画の出力をオプティマイザ・ヒントNO_XML_QUERY_REWRITEの後に表示される計画の出力と比較することもできます。これによってXQueryの最適化がオフになります。
さらに、SQL*PlusのSETコマンドでシステム変数XMLOptimizationCheckを使用して、SQLのXML診断能力モードをオンにすることができます。
SET XMLOptimizationCheck ON
このモードがオンの場合、XQuery最適化の実行計画が自動的にチェックされ、計画が部分的な最適化の場合は、エラーが発生して診断情報がトレース・ファイルに書き込まれ、どの演算子が書きなおされていないのかが示されます。
XMLOptimizationCheckの主な利点は、潜在的な問題がただちに明らかになることです。このため、常にこのモードをオンにしておくのが望ましいと言えます。そして、なんらかの理由でアプリケーションやデータベースに変更が生じたため、SQL操作で書きなおしができない場合、原因に気がつかないうちにパフォーマンスが低下するかわりに実行が停止します。
注意:
XMLOptimizationCheckは、Oracle Database 11gリリース2(11.2.0.2)以前は利用できませんでした。旧リリースのユーザーは、イベント19201を直接操作して、XQueryの最適化情報を取得していました。
OCIユーザーはOCIStmtExecuteまたはイベント19201を使用できます。Javaユーザーが使用できるのはイベントのみです。
関連項目:
「XML Schemaに基づくXMLTypeデータに対するXQueryの最適化」: オブジェクト・リレーショナル形式でXML Schemaに基づくXMLType表をターゲットとするXQuery式の例を示します
Oracle XML DBリポジトリに対するfn:docとfn:collectionの問合せのパフォーマンスを向上するには、問い合せるリポジトリ・データを保持する実際のデータベースの表にそれらをリンクします。
Oracle XML DBでは、XQuery関数fn:docおよびfn:collectionを使用して、Oracle XML DBリポジトリ内の文書やコレクションを参照できます。
リポジトリのXMLTypeデータがオブジェクト・リレーショナル形式またはバイナリXML形式で格納されている場合、fn:docおよびfn:collectionを使用する問合せは関数として評価されます。つまり、これらは基礎となる記憶域表に直接アクセスできるように最適化されていません。こうした問合せのパフォーマンスを向上するには、問い合せるリポジトリ・データを保持する実際のデータベースの表にそれらをリンクする必要があります。これを行うには、次のいずれかの方法を実行します。
RESOURCE_VIEWビューを、データを保持するXMLType表と結合し、XQuery関数fn:docおよびfn:collectionのかわりに、Oracle SQL関数equals_pathおよびunder_pathをそれぞれ使用します。これらのSQL関数は、パフォーマンスに優れた方法でリポジトリ・リソースを参照します。
Oracle XQuery拡張式プラグマora:defaultTableを使用します。
どちらの方法を使用しても結果は同じです。XQueryの標準関数fn:docとfn:collectionを引き続き使用できるほか、コードが簡略化されるため、ora:defaultTableプラグマの使用をお薦めします。
この項の例で、これらの2つの方法について説明しています。
XQuery関数fn:docとfn:collectionのかわりに、Oracle SQL関数equals_pathおよびunder_pathを使用すると、パフォーマンスを向上できます。
SQL関数equals_pathおよびunder_pathは、それぞれ指定したリポジトリ・パスにあるリソースを参照します。例5-45および例5-46に、関数fn:docおよびequals_pathでのこの処理を示します。関数fn:collectionおよびunder_pathが同じように処理されます。
例5-45 fn:docを使用した、最適化されていないリポジトリの問合せ
SELECT XMLQuery('let $val :=
fn:doc("/home/OE/PurchaseOrders/2002/Sep/VJONES-20021009123337583PDT.xml")
/PurchaseOrder/LineItems/LineItem[@ItemNumber =19]
return $val' RETURNING CONTENT)
FROM DUAL;
例5-46 EQUALS_PATHを使用した、最適化されたリポジトリの問合せ
SELECT XMLQuery('let $val := $DOC/PurchaseOrder/LineItems/LineItem[@ItemNumber = 19]
return $val' PASSING OBJECT_VALUE AS "DOC" RETURNING CONTENT)
FROM RESOURCE_VIEW rv, purchaseorder p
WHERE ref(p) = XMLCast(XMLQuery('declare default element namespace
"http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
fn:dataFoot 1(/Resource/XMLRef)' PASSING rv.RES RETURNING CONTENT)
AS REF XMLType)
AND equals_path(rv.RES, '/home/OE/PurchaseOrders/2002/Sep/VJONES-20021009123337583PDT.xml')
= 1;
Oracle XQuery拡張式プラグマora:defaultTableを使用して、リポジトリ・データの問合せのパフォーマンスを向上できます。
Oracle XQuery拡張式プラグマora:defaultTableを使用すると、問い合せるリポジトリ・データを格納するデフォルトの表を指定できます。問合せは、デフォルトのテーブルをRESOURCE_VIEWビューに自動的に結合し、XQuery関数fn:docとfn:collectionのかわりに、Oracle SQL関数equals_pathおよびunder_pathを使用するようにリライトされます。このため結果は、手動で問合せのコードを記述して、明示的な結合およびequals_pathやunder_pathを使用する場合と同じです。例5-47に、この詳細を説明します。問合せが自動的にリライトされ、例5-46のようになります。
範囲を明確にするため、プラグマora:defaultTableを、大きい式ではなく適切な文書やコレクションの式、fn:docまたはfn:collectionに直接適用することをお薦めします。
例5-47 Oracle XQueryプラグマora:defaultTableを使用したリポジトリの問合せ
SELECT XMLQuery('for $doc in (#ora:defaultTable PURCHASEORDER #)
{fn:doc("/home/OE/PurchaseOrders/2002/Sep/VJONES-20021009123337583PDT.xml")}
let $val := $doc/PurchaseOrder/LineItems/LineItem[@ItemNumber = 19]
return $val}'
RETURNING CONTENT)
FROM DUAL;
脚注の凡例
脚注1:XQuery関数fn:dataは、その引数の分解に使用されます。この場合は、XMLRefノード型指定されたアトミック値を戻します。
