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
ノード型指定されたアトミック値を戻します。