| Oracle XML DB開発者ガイド 11gリリース1（11.1） E05669-02 |
|
 戻る |
 次へ |
この章では、データベースのリレーショナル・データからXMLデータを生成（構成）するためのOracle XML DB機能を説明します。また、リレーショナル・コンテンツからXMLデータを生成するためのSQL/XML標準関数およびOracle Databaseが提供する関数とパッケージについて説明します。
この章の内容は次のとおりです。
この項では、Oracle DatabaseによりXMLデータを生成する様々な方法の概要を説明します。
Oracle XML DBでサポートされている次の標準SQL/XML関数のいずれかを使用してXMLデータを生成できます。これは、「SQL関数を使用したXMLの生成」で説明します。
次のいずれかのOracle Database SQL関数を使用してXMLデータを生成できます。
XMLSEQUENCE SQL関数。この関数のカーソル・バージョンのみがXMLを生成します。
SQL関数SYS_XMLGENを使用したXMLの生成。この関数は、行を操作し、XML文書を生成します。
SQL関数SYS_XMLAGGを使用したXMLの生成。この関数は、行のグループを操作し、複数のXML文書を1つに集約します。
PL/SQLパッケージDBMS_XMLGENを使用して、SQL問合せからXMLを作成できます。これは、「DBMS_XMLGENを使用したXMLの生成」で説明します。
XSQLサーブレットとも呼ばれる、XSQLページ・パブリッシング・フレームワークを使用してXMLを生成できます。これは、「XSQLページ・パブリッシング・フレームワークを使用したXMLの生成」で説明します。XSQLページ・パブリッシング・フレームワークはOracle XML Developer's Kit for Javaの一部です。
XML SQL Utility（XSU）を使用すると、XMLType表および列のデータに対して、次のタスクを実行できます。
オブジェクト・リレーショナル・データベースの表またはビューから取り出したデータをXMLに変換する。
XML文書からデータを抽出し、正規マッピングを使用して、表またはビューの適切な列または属性にデータを挿入する。
XML文書からデータを抽出し、このデータを適用して適切な列または属性の値を更新または削除する。
DBURITypeのインスタンスを使用して、データベース・データを含み、データベースの構造を反映した構造を持つXML文書を構成できます。これは、第20章「URIを介したデータのアクセス」で説明します。
この項では、XMLデータの構成に使用するSQL関数について説明します。この関数の多くは、XML用のSQL標準であるSQL/XML標準に属しています。
これらのXML生成関数はXMLパブリッシング関数とも呼ばれます。
SQL/XML標準は、ISO/IEC 9075-14:2005(E)『Information technology - Database languages - SQL - Part 14: XML-Related Specifications (SQL/XML)』です。これもSQL標準の一部なので、SQL:2003との間で調整されています。この開発を支援している標準化団体は、次の2団体です。
ISO/IEC JTC1/SC32（International Organization for StandardizationおよびInternational Electrotechnical Committee Joint Technical Committee 1, Information technology, Subcommittee 32, Data ManagementおよびInterchange）
INCITS Technical Committee H2（INCITSはInternational Committee for Information Technology Standardsの略）。INCITSは、ANSI（米国規格協会）の方針と手続きに基づいて開発作業を行う公認の標準開発組織です。Committee H2は、SQLとSQL/MMを担当する委員会です。
この標準化プロセスは進行中です。XMLQueryおよびXMLTableの最新情報についてはhttp://www.sqlx.orgを参照してください。
この項で説明する、XMLを生成する他のSQL関数は、Oracle Database専用です。
XMLSEQUENCE SQL関数。この関数のカーソル・バージョンのみがXMLを生成します。
SQL関数SYS_XMLGENを使用したXMLの生成。この関数は、リレーショナル行を操作し、XML文書を生成します。
SQL関数SYS_XMLAGGを使用したXMLの生成。この関数は、リレーショナル行のグループを操作し、複数のXML文書を1つに集約します。
すべてのXML生成SQL関数では、スカラーおよびユーザーのデータ型インスタンスが正規のXML形式に変換されます。この正規マッピングでは、ユーザー定義のデータ型の属性はXML要素にマップされます。
SQL/XML標準関数XMLElementを使用して、リレーショナル・データからXML要素を構成します。この関数は、要素名、その要素の属性のコレクション（オプション）、およびその要素のコンテンツを構成する0（ゼロ）個以上の引数を取ります。戻り値はXMLTypeインスタンスです。
キーワードENTITYESCAPINGおよびNOENTITYESCAPINGの説明は、「生成されたXMLデータにおける文字のエスケープ」を参照してください。
関数XMLElementの最初の引数は、作成されるルートXML要素名を定義する識別子です。ルート要素識別子の引数は、リテラル識別子（図17-1のidentifier）またはEVALNAMEに識別子を評価する拡張子（value_expr）を続けた識別子を使用して定義できます。識別子は、定義されてもNULLにできません。NULLの場合、エラーが発生します。
関数XMLElementのオプションのXML-attributes-clause引数は、生成されるルート要素の属性を指定します。この関数の構文を図17-2に示します。
オプションのXML-attributes-clause引数の他に、関数XMLElementは、ルート要素のコンテンツ（子要素とテキスト・コンテンツ）を構成する0（ゼロ）個以上のvalue_expr引数を取ります。XML-attributes-clause引数も存在する場合、XML-attributes-clause引数の後にこれらのコンテンツ引数が続く必要があります。個々のコンテンツ引数式が評価され、結果がXMLフォーマットに変換されます。値引数の評価結果がNULLだった場合、その引数にはコンテンツが作成されません。
オプションのXML-attributes-clause引数は、SQL/XML標準関数XML-attributes-clauseを使用してルート要素の属性を指定します。関数XMLAttributesは、関数XMLElementのコールでのみ使用できます。単独では使用できません。
キーワードENTITYESCAPINGおよびNOENTITYESCAPINGの説明は、「生成されたXMLデータにおける文字のエスケープ」を参照してください。
キーワードSCHEMACHECKおよびNOSCHEMACHECKは、生成された属性のいずれかがOracle XML DBで登録されているXML Schemaに対応するスキーマの場所を指定するか確認し、指定している場合は、それに対応するXML Schemaに基づくXMLデータを生成するか確認するため、それら属性で実行時チェックが行われるかどうか判断します。NOSCHEMACHECKで提供されているデフォルト動作では、チェックは行いません。11gリリース1（11.1）以前のリリースでは、デフォルト動作ではチェックを行います。SCHEMACHECKキーワードを使用して、下位互換性を得ることができます。
NOSCHEMACHECKの有無にかかわらず、同様のチェックをコンパイル時に行います。つまり、特にXML Schemaの場所属性値の指定に文字列リテラルを使用している場合、（コンパイル時）チェックが行われ、適切な場合、XML Schemaに基づくデータがそれに伴って生成されます。
|
注意: XMLデータの生成にビューが作成されると、XMLAttributes関数を使用してXML Schemaの場所参照が追加されます。ターゲットXML SchemaはOracle XML DBに登録されないため、生成されたXMLデータはXML Schemaに基づきません。XML Schemaが引き続き登録されると、それ以降生成されたXMLデータもXML Schemaに基づきません。XML Schemaに基づくデータを作成するには、ビューをコンパイルする必要があります。 |
引数XML-attributes-clause自身には、関数XMLAttributesへの引数として、1つ以上のvalue_expr式が含まれます。それらはルート要素の属性の値を取得するために評価されます。（関数XMLAttributesへのvalue_expr引数を、ルート要素のコンテンツを指定する、関数XMLElementへのvalue_expr引数と混同しないでください。）各value_exprのオプションのAS c_alias句は、属性名がc_aliasであることを指定しています。この属性名は文字列リテラルまたはEVALNAMEに文字列リテラルを評価する式が続く名前になります。
属性値式がNULLであると評価された場合、その式に対応する属性は作成されません。属性値式のデータ型は、オブジェクト型またはコレクションにできません。
SQL/XML標準で規定されているとおり、明示された識別子に含まれる文字はいかなる場合もエスケープされません。必ず、有効なXML名を使用してください。これはすべてのSQL/XML関数、特に、XMLElementのルート要素識別子（図17-1のidentifier）やXMLAttributesのAS句で名前を指定された属性識別子エイリアスに当てはまります（図17-2を参照）。
ただし、生成される他のXMLデータはデフォルトではエスケープされるため、有効なXML NameChar文字のみが生成されます。SQL識別子から有効なXML要素や属性名を生成する際、XML名として許容されない文字は、元の文字の16進数Unicode表現の前後にアンダースコア（_）を付けたものに置換されます。たとえば、コロン（:）は_003A_に置換されてエスケープされます。003Aは16進数Unicode表現です。
エスケープは、XMLElementやXMLAttributesなど、すべての関数に対する、評価されたvalue_expr引数に含まれる文字に適用されます。また、AS句の前でないXMLAttributes属性値式で暗黙的に定義されている属性識別子の文字にも適用されます。SQL列名のエスケープされた形が属性の名前として使用されます。
文字のエスケープが不要な場合もあります。たとえば、生成中のXMLデータが整形式であることがわかっている場合、エスケープをしないことで処理時間を節約できます。SQL関数XMLElementおよびXMLAttributesにNOENTITYESCAPINGキーワードを指定して処理時間を節約できます。ENTITYESCAPINGキーワードでエスケープを制限します。これはデフォルトの動作ではありません。
XMLデータの日付とタイムスタンプは標準フォーマットで表記するようにXMLスキーマ標準で規定されています。Oracle XML DBにおけるXML生成関数では、この標準に従ってXMLの日付とタイムスタンプが生成されます。
Oracle Database 10gリリース2より前のリリースでは、XML Schemaの標準フォーマットでなく、日付やタイムスタンプのフォーマットに関するデータベース設定がXMLに対して使用されていました。データベース・イベント19119、レベル0x8を次のように設定すると、こうした以前の動作を再現できます。
ALTER SESSION SET EVENTS '19119 TRACE NAME CONTEXT FOREVER, LEVEL 0x8';
この他のXML非標準の日付とタイムスタンプの形式を生成する必要がある場合は、SQL関数to_charを使用します。例17-1を参照してください。
|
関連項目: http://www.w3.org/TR/2004/REC-xmlschema-2-20041028/datatypes.html#isoformats にある、XMLの日付およびタイムスタンプのフォーマットに関するXMLスキーマの仕様 |
この項では、SQL関数XMLElementを使用する例を示します。
例17-1 XMLELEMENT: 日付のフォーマット
この例は、XMLスキーマの標準フォーマットと異なるフォーマットを持つXMLの日付を生成する方法を示しています。
-- With standard XML date format:
SELECT XMLElement("Date", hire_date)
FROM hr.employees
WHERE employee_id = 203;
XMLELEMENT("DATE",HIRE_DATE)
----------------------------
<Date>1994-06-07</Date>
1 row selected.
-- With an alternative date format:
SELECT XMLElement("Date", to_char(hire_date))
FROM hr.employees
WHERE employee_id = 203;
XMLELEMENT("DATE",TO_CHAR(HIRE_DATE))
-------------------------------------
<Date>07-JUN-94</Date>
1 row selected.
例17-2 XMLELEMENT: 各従業員の要素の生成
次の例では、従業員の名前をコンテンツとして持つXMLのEmp要素を各従業員に1つ作成します。
SELECT e.employee_id,
XMLELEMENT ("Emp", e.first_name ||' '|| e.last_name) AS "RESULT"
FROM hr.employees e
WHERE employee_id > 200;
この問合せにより生成される典型的な結果は次のとおりです。
EMPLOYEE_ID RESULT
----------- -----------------------------------
201 <Emp>Michael Hartstein</Emp>
202 <Emp>Pat Fay</Emp>
203 <Emp>Susan Mavris</Emp>
204 <Emp>Hermann Baer</Emp>
205 <Emp>Shelley Higgins</Emp>
206 <Emp>William Gietz</Emp>
6 rows selected.
SQL関数XMLElementは、ネストすることにより、ネスト構造を持つXMLデータを生成することもできます。
例17-3 XMLELEMENT: ネストされたXMLの生成
各従業員のEmp要素を、従業員名と雇用日を提供する要素とともに作成するには、次の問合せを行います。
SELECT XMLElement("Emp",
XMLElement("name", e.first_name ||' '|| e.last_name),
XMLElement("hiredate", e.hire_date)) AS "RESULT"
FROM hr.employees e
WHERE employee_id > 200 ;
この問合せによって、次の典型的なXMLが結果として戻されます。
RESULT ----------------------------------------------------------------------- <Emp><name>Michael Hartstein</name><hiredate>1996-02-17</hiredate></Emp> <Emp><name>Pat Fay</name><hiredate>1997-08-17</hiredate></Emp> <Emp><name>Susan Mavris</name><hiredate>1994-06-07</hiredate></Emp> <Emp><name>Hermann Baer</name><hiredate>1994-06-07</hiredate></Emp> <Emp><name>Shelley Higgins</name><hiredate>1994-06-07</hiredate></Emp> <Emp><name>William Gietz</name><hiredate>1994-06-07</hiredate></Emp> 6 rows selected.
例17-4 XMLELEMENT: IDおよびname属性を持つ従業員要素の生成
この例では、idおよびname属性を含むEmp要素を各従業員に1つ作成します。
SELECT XMLElement("Emp", XMLAttributes(
e.employee_id as "ID",
e.first_name ||' ' || e.last_name AS "name"))
AS "RESULT"
FROM hr.employees e
WHERE employee_id > 200;
この問合せによって、次の典型的なXMLフラグメントが結果として戻されます。
RESULT ----------------------------------------------- <Emp ID="201" name="Michael Hartstein"></Emp> <Emp ID="202" name="Pat Fay"></Emp> <Emp ID="203" name="Susan Mavris"></Emp> <Emp ID="204" name="Hermann Baer"></Emp> <Emp ID="205" name="Shelley Higgins"></Emp> <Emp ID="206" name="William Gietz"></Emp> 6 rows selected.
「生成されたXMLデータにおける文字のエスケープ」で前述のとおり、ルート要素名、およびAS句により定義される属性の名前に含まれる文字はエスケープされません。識別指名に含まれる文字は、その名前が、評価された式から作成される場合にのみエスケープされます（列の参照と同様）。次の問合せは、ルート要素名と属性名がエスケープされないことを示します。大なり記号（>）とカンマ（,）はXML要素や属性名では許容されないため、無効なXMLが作成されます。
SELECT XMLElement("Emp->Special",
XMLAttributes(e.last_name || ', ' || e.first_name
AS "Last,First"))
AS "RESULT"
FROM hr.employees e
WHERE employee_id = 201;
この問合せにより作成される結果は次のとおりで、整形式のXMLではありません。
RESULT -------------------------------------------------------------------- <Emp->Special Last,First="Hartstein, Michael"></Emp->Special> 1 row selected.
文字のエスケープに関する詳細説明は、SQL/XML標準に含まれています。
例17-5 XMLELEMENT: 名前空間を使用したXML Schemaに基づく文書の作成
この例では、名前空間を使用してXML Schemaに基づく文書を作成する方法を示します。XML Schema http://www.oracle.com/Employee.xsdが存在し、ターゲットの名前空間が存在しない場合、次の問合せによってそのスキーマに準拠したXMLTypeインスタンスが作成されます。
SELECT XMLElement("Employee",
XMLAttributes('http://www.w3.org/2001/XMLSchema' AS
"xmlns:xsi",
'http://www.oracle.com/Employee.xsd' AS
"xsi:nonamespaceSchemaLocation"),
XMLForest(employee_id, last_name, salary)) AS "RESULT"
FROM hr.employees
WHERE department_id = 10;
これにより、XML Schema Employee.xsdに準拠している、次のXML文書が作成されます。（ここではわかりやすいように、この結果をフォーマット出力しています。）
RESULT
-----------------------------------------------------------------------------
<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema"
xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd">
<EMPLOYEE_ID>200</EMPLOYEE_ID>
<LAST_NAME>Whalen</LAST_NAME>
<SALARY>4400</SALARY>
</Employee>
1 row selected.
例17-6 XMLELEMENT: ユーザー定義のデータ型インスタンスからの要素の生成
例17-10 に、従業員情報を含むXML文書を示します。従業員情報と部門情報を含む階層XML文書を、次のようにして生成できます。
CREATE OR REPLACE TYPE emp_t AS OBJECT ("@EMPNO" NUMBER(4),
ENAME VARCHAR2(10));
/
Type created.
CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t;
/
Type created.
CREATE OR REPLACE TYPE dept_t AS OBJECT ("@DEPTNO" NUMBER(2),
DNAME VARCHAR2(14),
EMP_LIST emplist_t);
/
Type created.
SELECT XMLElement("Department",
dept_t(department_id,
department_name,
CAST(MULTISET(SELECT employee_id, last_name
FROM hr.employees e
WHERE e.department_id = d.department_id)
AS emplist_t)))
AS deptxml
FROM hr.departments d
WHERE d.department_id = 10;
これによって、Department要素およびdept_t型の正規マッピングを含むXML文書が生成されます。
DEPTXML
-------------
<Department>
<DEPT_T DEPTNO="10">
<DNAME>ACCOUNTING</DNAME>
<EMPLIST>
<EMP_T EMPNO="7782">
<ENAME>CLARK</ENAME>
</EMP_T>
<EMP_T EMPNO="7839">
<ENAME>KING</ENAME>
</EMP_T>
<EMP_T EMPNO="7934">
<ENAME>MILLER</ENAME>
</EMP_T>
</EMPLIST>
</DEPT_T>
</Department>
1 row selected.
SQL/XML標準関数XMLForestを使用して、XML要素のフォレストを構成します。その引数は評価する式で、オプションで別名も付けられます。図17-3 は、XMLForestの構文を説明するものです。
値の式（図17-3のvalue_expr）がXMLフォーマットに変換され、識別子c_aliasが属性識別子として使用されます（c_aliasは文字列リテラルまたはEVALNAMEに文字列リテラルを評価する式が続く名前になります）。
オブジェクト型またはコレクションの場合、AS句は必須です。それ以外の方の場合、AS句はオプションです。指定された式でAS句が省略されている場合は、評価された値の式に含まれる文字はエスケープされ、要素の囲みタグ名を形成します。エスケープの方式は「生成されたXMLデータにおける文字のエスケープ」での定義によります。値の式の評価結果がNULLだった場合、その式には要素が作成されません。
例17-7 XMLFOREST: 属性と子要素を持つ要素の生成
次の例では、name属性、雇用日および部門を要素として持つEmp要素を各従業員に1つ作成します。
SELECT XMLElement("Emp",
XMLAttributes(e.first_name ||' '|| e.last_name AS "name"),
XMLForest(e.hire_date, e.department AS "department"))
AS "RESULT"
FROM employees e WHERE e.department_id = 20;
（ここでWHERE句は、例を簡潔にするために使用されています。）この問合せにより生成されるXMLは次のとおりです。
RESULT ------------------------------------- <Emp name="Michael Hartstein"> <HIRE_DATE>1996-02-17</HIRE_DATE> <department>20</department> </Emp> <Emp name="Pat Fay"> <HIRE_DATE>1997-08-17</HIRE_DATE> <department>20</department> </Emp> 2 rows selected.
例17-8 XMLFOREST: ユーザー定義のデータ型インスタンスからの要素の生成
SQL関数XMLForestを使用してユーザー定義のデータ型インスタンスから階層XMLを生成することもできます。
SELECT XMLForest(
dept_t(department_id,
department_name,
CAST (MULTISET (SELECT employee_id, last_name
FROM hr.employees e
WHERE e.department_id = d.department_id)
AS emplist_t))
AS "Department")
AS deptxml
FROM hr.departments d
WHERE department_id=10;
これにより、属性DEPTNOおよび子要素DNAMEを含む要素Departmentが作成されます。
DEPTXML
---------------------------------
<Department DEPTNO="10">
<DNAME>Administration</DNAME>
<EMP_LIST>
<EMP_T EMPNO="200">
<ENAME>Whalen</ENAME>
</EMP_T>
</EMP_LIST>
</Department>
1 row selected.
Oracle SQL関数XMLSequenceはXMLSequenceType値を戻します（XMLTypeインスタンスのVARRAY）。コレクションを戻すため、この関数はSQL問合せのFROM句で使用できます。図17-4を参照してください。
例17-9 最上位の要素ノードのみが戻されるXMLSEQUENCE
関数XMLSequenceでは、最上位の要素ノードのみが戻されます。つまり、属性またはテキスト・ノードは断片化されません。
SELECT value(T).getstringval() Attribute_Value
FROM table(XMLSequence(extract(XMLType('<A><B>V1</B><B>V2</B><B>V3</B></A>'),
'/A/B'))) T;
ATTRIBUTE_VALUE
----------------------
<B>V1</B>
<B>V2</B>
<B>V3</B>
3 rows selected.
関数XMLSequenceには次の2つの形式があります。
1つ目の形式では、XMLTypeインスタンスを入力に取り、最上位のノードのVARRAYを戻します。この形式は、XMLフラグメントを複数行に断片化するために使用できます。
2つ目の形式では、REFCURSOR引数とXMLFormatオブジェクトのインスタンス（オプション）を入力に取り、カーソルの各行に対応するXMLTypeのVARRAYを戻します。この形式は、任意のSQL問合せからXMLTypeインスタンスを作成するために使用できます。XMLFormatのこの用法は、XMLスキーマをサポートしていません。
1つ目の形式は実質的に、SQLコードを読みやすくするためのXMLTable標準SQL/XML関数に置き換えられます。Oracle Database 10gリリース2より前のリリースでは、一部の処理の実行にSQL関数tableでXMLSequenceが使用されていました。これらの処理は現在、標準関数XMLTableを使用してより効率的に実行できるようになっています。
例17-10 XMLSEQUENCE: XML文書からの別のXML文書の生成
従業員情報を持つXML文書を含む次のXMLType表について考えます。
CREATE TABLE emp_xml_tab OF XMLType;
Table created.
INSERT INTO emp_xml_tab VALUES( XMLType('<EMPLOYEES>
<EMP>
<EMPNO>112</EMPNO>
<EMPNAME>Joe</EMPNAME>
<SALARY>50000</SALARY>
</EMP>
<EMP>
<EMPNO>217</EMPNO>
<EMPNAME>Jane</EMPNAME>
<SALARY>60000</SALARY>
</EMP>
<EMP>
<EMPNO>412</EMPNO>
<EMPNAME>Jack</EMPNAME>
<SALARY>40000</SALARY>
</EMP>
</EMPLOYEES>'));
1 row created.
COMMIT;
収入が$50,000以上の従業員のみを含む新しいXML文書を作成するには、次の問合せを使用します。
SELECT sys_XMLAgg(value(em), XMLFormat('EMPLOYEES'))
FROM emp_xml_tab doc, table(XMLSequence(extract(value(doc),
'/EMPLOYEES/EMP'))) em
WHERE extractValue(value(em), '/EMP/SALARY') >= 50000;
これは、この問合せに必要な手順です。
関数extractはEMP要素のフラグメントを返します。
関数XMLSequenceは、これらの最上位要素のコレクションをXMLTypeインスタンスに収集して戻します。
関数tableは、コレクションから表の値を作成します。表の値は、続いて問合せのFROM句で使用されます。
この問合せによって、次のXML文書が戻されます。
SYS_XMLAGG(VALUE(EM),XMLFORMAT('EMPLOYEES'))
--------------------------------------------
<?xml version="1.0"?>
<EMPLOYEES>
<EMP>
<EMPNO>112</EMPNO>
<EMPNAME>Joe</EMPNAME>
<SALARY>50000</SALARY>
</EMP>
<EMP>
<EMPNO>217</EMPNO>
<EMPNAME>Jane</EMPNAME>
<SALARY>60000</SALARY>
</EMP>
</EMPLOYEES>
1 row selected.
例17-11 XMLSEQUENCE: カーソルの各行に対する文書の生成
この例では、SQL関数XMLSequenceは、カーソル式の各行に対するXML文書を作成するのに使用され、値がXMLSequenceType として戻されます（XMLTypeインスタンスのVARRAY）。
SELECT value(em).getCLOBVal() AS "XMLTYPE"
FROM table(XMLSequence(Cursor(SELECT *
FROM hr.employees
WHERE employee_id = 104))) em;
この問合せによって、次のXMLが戻されます。
XMLTYPE -------------------------------------------------- <ROW> <EMPLOYEE_ID>104</EMPLOYEE_ID> <FIRST_NAME>Bruce</FIRST_NAME> <LAST_NAME>Ernst</LAST_NAME> <EMAIL>BERNST</EMAIL> <PHONE_NUMBER>590.423.4568</PHONE_NUMBER> <HIRE_DATE>21-MAY-91</HIRE_DATE> <JOB_ID>IT_PROG</JOB_ID> <SALARY>6000</SALARY> <MANAGER_ID>103</MANAGER_ID> <DEPARTMENT_ID>60</DEPARTMENT_ID> </ROW> 1 row selected.
各行に使用されているタグは、XMLFormatオブジェクトを使用して変更できます。
例17-12 XMLSEQUENCE: XML文書内のコレクションのSQL列へのネスト解除
SQL関数XMLSequenceは表関数なので、XML文書内の要素をネスト解除するのに使用できます。例として、XML文書を含む次のXMLType表dept_xml_tabを考えます。
CREATE TABLE dept_xml_tab OF XMLType;
Table created.
INSERT INTO dept_xml_tab
VALUES(
XMLType('<Department deptno="100">
<DeptName>Sports</DeptName>
<EmployeeList>
<Employee empno="200"><Ename>John</Ename><Salary>33333</Salary>
</Employee>
<Employee empno="300"><Ename>Jack</Ename><Salary>333444</Salary>
</Employee>
</EmployeeList>
</Department>'));
1 row created.
INSERT INTO dept_xml_tab
VALUES (
XMLType('<Department deptno="200">
<DeptName>Sports</DeptName>
<EmployeeList>
<Employee empno="400"><Ename>Marlin</Ename><Salary>20000</Salary>
</Employee>
</EmployeeList>
</Department>'));
1 row created.
COMMIT;
SQL関数XMLSequenceを使用してEmployeeリスト項目を最上位のSQL行にネスト解除できます。
SELECT extractValue(OBJECT_VALUE, '/Department/@deptno') AS deptno,
extractValue(value(em), '/Employee/@empno') AS empno,
extractValue(value(em), '/Employee/Ename') AS ename
FROM dept_xml_tab,
table(XMLSequence(extract(OBJECT_VALUE,
'/Department/EmployeeList/Employee'))) em;
これによって、次の結果が戻されます。
DEPTNO EMPNO ENAME --------------------------------- 100 200 John 100 300 Jack 200 400 Marlin 3 rows selected
表dept_xml_tabの各行について、関数tableが適用されます。ここで、extract関数によって、すべての従業員要素のフラグメントを含む新しいXMLTypeインスタンスが作成されます。これが、すべての従業員のコレクションを作成するSQL関数XMLSequenceに送られます。
次に、関数TABLEによって、そのコレクション要素が、親表dept_xml_tabに関連付けられている複数の行に分解されます。そのため、dept_xml_tabの親であるすべての行のリストおよび関連付けられた従業員が戻されます。
関数extractValueによって、部門番号、従業員番号および名前のスカラー値が抽出されます。
SQL/XML標準関数XMLConcatを使用して、複数のXMLTypeインスタンスを連結することにより、XMLフラグメントを構成します。図17-5はXMLConcatの構文を説明するものです。関数XMLConcatには次の2つの形式があります。
1つ目の形式では、XMLTypeのVARRAYであるXMLSequenceTypeを取り、VARRAYのすべての要素を連結して単一のXMLTypeインスタンスを戻します。この形式は、XMLTypeインスタンスのリストを単一のインスタンスに縮小する場合に有効です。
2つ目の形式では、任意の数のXMLTypeインスタンスを取り、それらの値を連結します。値の1つがNULLである場合、その値は結果に出力されません。すべての値がNULLである場合、結果はNULLになります。この形式は、任意の数のXMLTypeインスタンスを同じ行に連結するために使用できます。関数XMLAggを使用すると、複数行にわたってXMLTypeインスタンスを連結できます。
例17-13 XMLCONCAT: 順序で使用されるXMLTypeインスタンスの連結
この例では、関数XMLConcatを使用して、XMLSequenceType値（XMLTypeインスタンスのVALLAY）のXMLTypeインスタンスを連結して戻します。
SELECT XMLConcat(XMLSequenceType(
XMLType('<PartNo>1236</PartNo>'),
XMLType('<PartName>Widget</PartName>'),
XMLType('<PartPrice>29.99</PartPrice>'))).getCLOBVal()
AS "RESULT"
FROM DUAL;
この問合せによりXMLフラグメントが戻されます。（ここではわかりやすいように、この結果をフォーマット出力しています。）
RESULT --------------- <PartNo>1236</PartNo> <PartName>Widget</PartName> <PartPrice>29.99</PartPrice> 1 row selected.
例17-14 XMLCONCAT: XML要素の連結
次の例では、名前および姓のXML要素を作成し、結果を連結します。
SELECT XMLConcat(XMLElement("first", e.first_name),
XMLElement("last", e.last_name))
AS "RESULT"
FROM employees e;
この問合せにより生成されるXMLフラグメントは次のとおりです。
RESULT -------------------------------------------- <first>Den</first><last>Raphaely</last> <first>Alexander</first><last>Khoo</last> <first>Shelli</first><last>Baida</last> <first>Sigal</first><last>Tobias</last> <first>Guy</first><last>Himuro</last> <first>Karen</first><last>Colmenares</last> 6 rows selected.
SQL/XML標準関数XMLAggを使用して、XML要素のコレクションからXML要素のフォレストを構成します。これは集計関数です。
図17-6はXMLAggの構文を示しています。この構文でorder_by_clauseは次のとおりです。
ORDER BY [list of: expr [ASC|DESC] [NULLS {FIRST|LAST}]]
数値リテラルは、列の位置として解釈されません。たとえば、ORDER BY 1は、最初の列での順序付けを意味しません。数値リテラルは他のリテラルと同様に解釈されます。
SQL関数XMLConcatと同様に、NULLである引数は結果から削除されます。関数XMLAggは関数sys_XMLAggに類似していますが、ノードのフォレストを戻し、XMLFormatパラメータを取らない点で異なります。関数XMLAggを使用すると、複数行にわたってXMLTypeインスタンスを連結できます。また、オプションのORDER BY句を使用して、集計するXML値を順序付けできます。
関数XMLAggは各グループについて1つの集計XMLを生成します。問合せでGROUP BYが指定されていない場合、問合せのすべての行に対する結果が集計され、1つのXMLが戻されます。
例17-15 XMLAGG: 従業員要素のリストを含む部門要素の生成
次の例では、従業員のジョブIDおよび姓を要素のコンテンツとして持つEmployee要素を含むDepartment要素を作成します。また、その部門に属する従業員のXML要素を従業員の姓で順序付けします。（ここではわかりやすいように、この結果をフォーマット出力しています。）
SELECT XMLElement("Department", XMLAgg(XMLElement("Employee",
e.job_id||' '||e.last_name)
ORDER BY e.last_name))
AS "Dept_list"
FROM hr.employees e
WHERE e.department_id = 30 OR e.department_id = 40;
Dept_list
------------------
<Department>
<Employee>PU_CLERK Baida</Employee>
<Employee>PU_CLERK Colmenares</Employee>
<Employee>PU_CLERK Himuro</Employee>
<Employee>PU_CLERK Khoo</Employee>
<Employee>HR_REP Mavris</Employee>
<Employee>PU_MAN Raphaely</Employee>
<Employee>PU_CLERK Tobias</Employee>
</Department>
1 row selected.
XMLAggによって行が集計されるため、単一の行が戻されます。GROUP BY句を使用すると、戻された一連の行を複数のグループにグループ化できます。（ここではわかりやすいように、この結果をフォーマット出力しています。）
SELECT XMLElement("Department", XMLAttributes(department_id AS "deptno"),
XMLAgg(XMLElement("Employee", e.job_id||' '||e.last_name)))
AS "Dept_list"
FROM hr.employees e
GROUP BY e.department_id;
Dept_list
------------------
<Department deptno="30">
<Employee>PU_MAN Raphaely</Employee>
<Employee>PU_CLERK Khoo</Employee>
<Employee>PU_CLERK Baida</Employee>
<Employee>PU_CLERK Himuro</Employee>
<Employee>PU_CLERK Colmenares</Employee>
<Employee>PU_CLERK Tobias</Employee>
</Department>
<Department deptno="40">
<Employee>HR_REP Mavris</Employee>
</Department>
2 rows selected.
XMLAgg式でORDER BY句を使用すると、各部門内で従業員を順序付けできます。
|
注意: ORDER BY句にかぎり、他のOracle Databaseでの使用方法とは異なり、数値リテラルは列の位置として解釈されません。 |
例17-16 XMLAGG: ネストされた要素の生成
関数XMLAggを使用すると、表に存在するいくつかの階層関係を反映できます。この例では、部門30の部門要素を生成します。この要素内に、部門の各従業員に対応する子要素があります。各従業員要素内に、その従業員の各依存に対応するdependent要素があります。
まず、この問合せは部門30の従業員を表示します。
SELECT last_name, employee_id FROM employees WHERE department_id = 30; LAST_NAME EMPLOYEE_ID ------------------------- ----------- Raphaely 114 Khoo 115 Baida 116 Tobias 117 Himuro 118 Colmenares 119 6 rows selected.
dependents表が作成され、各従業員の依存を保持します。
CREATE TABLE hr.dependents (id NUMBER(4) PRIMARY KEY,
employee_id NUMBER(4),
name VARCHAR2(10));
Table created.
INSERT INTO dependents VALUES (1, 114, 'MARK');
1 row created.
INSERT INTO dependents VALUES (2, 114, 'JACK');
1 row created.
INSERT INTO dependents VALUES (3, 115, 'JANE');
1 row created.
INSERT INTO dependents VALUES (4, 116, 'HELEN');
1 row created.
INSERT INTO dependents VALUES (5, 116, 'FRANK');
1 row created.
COMMIT;
Commit complete.
この問合せは、依存についての情報を含む部門のXMLデータを生成します。（ここではわかりやすいように、この結果をフォーマット出力しています。）
SELECT
XMLElement(
"Department",
XMLAttributes(d.department_name AS "name"),
(SELECT
XMLAgg(XMLElement("emp",
XMLAttributes(e.last_name AS name),
(SELECT XMLAgg(XMLElement("dependent",
XMLAttributes(de.name AS "name")))
FROM dependents de
WHERE de.employee_id = e.employee_id)))
FROM employees e
WHERE e.department_id = d.department_id)) AS "dept_list"
FROM departments d
WHERE department_id = 30;
dept_list
--------------------------------------------------------------------------------
<Department name="Purchasing">
<emp NAME="Raphaely">
<dependent name="MARK"></dependent>
<dependent name="JACK"></dependent>
</emp><emp NAME="Khoo">
<dependent name="JANE"></dependent>
</emp>
<emp NAME="Baida">
<dependent name="HELEN"></dependent>
<dependent name="FRANK"></dependent>
</emp><emp NAME="Tobias"></emp>
<emp NAME="Himuro"></emp>
<emp NAME="Colmenares"></emp>
</Department>
1 row selected.
SQL/XML標準関数XMLPIを使用して、XML処理命令（PI）を構成します。図17-7に構文を示します。
引数value_exprが評価され、文字列の結果が、空白で区切られてオプションの識別子（identifier）に追加されます。この連結は、次に「<?」と「?>」で囲まれて処理命令になります。つまり、string-resultがvalue_exprの評価結果だとすると、生成された処理命令は<?identifier string-result?>となります。string-resultが空の文字列''の場合、関数は<?identifier?>を戻します。
キーワードNAMEにリテラル文字列identifierを続ける方法の他に、キーワードEVALNAMEに、評価結果が識別子として使用される文字列になる式を続ける方法もあります。
構成されたXMLが有効なXML処理命令でない場合はエラーが発生します。特に、次の点に注意してください。
identifierには「xml」という語は使用できません（大文字、小文字、混合のいずれも）。
string-resultには「?>」という文字の並びを含めることはできません。
関数XMLPIはXMLTypeのインスタンスを戻します。string-resultがNULLの場合はNULLが戻されます。
SQL/XML標準関数XMLCommentを使用して、XMLコメントを構成します。図17-8に構文を示します。
引数value_exprの評価結果は文字列で、生成されるXMLコメントの本文として使用されます。つまり、結果は<!--string-result-->で、ここでstring-resultがvalue_exprの文字列評価結果です。 string-resultが空の文字列の場合、次のようにコメントは空になります。<!---->
構成されたXMLが有効なXMLコメントでない場合はエラーが発生します。特に、 string-resultに、ハイフン（-）が連続して2つ含まれてはいけないことに注意してください（--）。
関数XMLCommentはXMLTypeのインスタンスを戻します。string-resultがNULLの場合、関数はNULLを戻します。
SQL関数XMLRootはSQL/XML標準関数である時期がありましたが、SQL/XML 2005時点で標準関数として推奨されなくなりました。Oracle関数としてOracle XML DBでは使用できます。
XMLRootは、XML値のルート情報項目へのVERSIONプロパティの追加や、オプションのSTANDALONEプロパティの追加に使用します。通常、これはデータモデル準拠の確認に使用します。図17-9にXMLRootの構文を示します。
最初の引数xml-expressionが評価され、示されたプロパティ（VERSION、STANDALONE）およびその値が、結果のXMLTypeインスタンスの新しいプロローグに追加されます。評価されたxml-expressionがすでにプロローグに含まれている場合、エラーが発生します。
2番目の引数string-valued-expression（キーワードVERSIONの次）が評価され、結果の文字列がプロローグのversionプロパティの値として使用されます。プロローグのstandaloneプロパティ（小文字）がオプションの3番目の引数STANDALONEのYESまたはNOの値から取得されます。VERSIONでNOVALUEが使用されている場合、結果のプロローグでは「version=1.0」が使用されます。STANDALONEでNOVALUEが使用されている場合、結果のプロローグではstandaloneプロパティが省略されます。
関数XMLRootはXMLTypeのインスタンスを戻します。最初の引数xml-expressionの評価結果がNULLの場合、関数はNULLを戻します。
SQL/XML標準関数XMLSerializeは、文字列またはXMLデータのLOB表現を取得するために使用します。
図17-10にXMLSerializeの構文を示します。
引数value_expr/が評価され、結果のXMLTypeインスタンスがシリアライズされて、作成された文字列またはLOBのコンテンツを生成します。存在する場合は脚注1、指定されたdatatypeは、次のいずれかであることが必要です（デフォルトのデータ型はCLOB）。
VARCHAR2
VARCHAR
CLOB
BLOB
DOCUMENTを指定した場合、value_exprの評価の結果は整形式の文書である必要があります。特に、ルートは単独にしてください。結果が整形式の文書でない場合、エラーが発生します。ただし、CONTENTを指定した場合は、value_exprの結果は整形であるかどうかがチェックされません。
value_expr の評価結果がNULLまたは空の文字列（''）の場合、関数 XMLSerializeはNULLを戻します。
ENCODING句は、BLOBインスタンスとしてシリアライズされたXMLデータの文字エンコーディングを指定します。xml_encoding_specは、XMLエンコーディング宣言（encoding="..."）です。datatypeがBLOBの場合、ENCODING句を指定すると、指定したとおりに出力がエンコードされ、BLOBエンコーディングを示すためにxml_encoding_specがプロローグに追加されます。ENCODING句をBLOB以外のdatatypeとともに指定した場合、エラーが発生します。
VERSIONを指定した場合、XML宣言（<?xml version="..." ...?>）でそのバージョンが使用されます。
NO INDENTを指定した場合、無意味な空白は出力に表示されないようにすべて削除されます。INDENT SIZE = N（Nは数字）を指定した場合、出力はN個の空白の相対インデントを使用してフォーマット出力されます。Nが0の場合、フォーマット出力で各要素の後に改行文字が挿入されます。行の各要素はそれ自身で置き換えられますが、出力に他の無意味な空白は含まれません。SIZEを指定せずにINDENTを指定した場合、2個の空白を使用したインデントが使用されます。NO INDENTもINDENT指定しない場合、動作（フォーマット出力かどうか）は不確定です。
HIDE DEFAULTSおよびSHOW DEFAULTSは、XML Schemaに基づくデータにのみ適用されます。SHOW DEFAULTSを指定した場合、XML Shemaでデフォルト値が定義されているオプションの要素や属性が入力データに存在しないと、それらの要素や属性はそのデフォルト値とともに出力に含められます。HIDE DEFAULTSを指定した場合、このような要素や属性は出力に含められません。HIDE DEFAULTSがデフォルトの動作です。
SQL/XML標準関数XMLParseは、XMLデータを含む文字列を解析してXMLTypeインスタンスの対応する値を構成するために使用します。図17-11に構文を示します。
引数value_exprが評価され、解析された文字列が生成されます。DOCUMENTを指定した場合、value_exprは、整形式のXML文書に対応していて、ルートが単独である必要があります。CONTENTを指定した場合、value_exprは整形式のXMLフラグメントに対応していれば問題ありません。ルートが単独である必要はありません。
キーワードWELLFORMEDは、SQL/XML標準に向けたOracle XML DBの拡張です。WELLFORMEDを指定する場合、パーサーに対して、引数value_exprが整形式であると通知することになるため、Oracle XML DBで、本当に整形式かどうかを確認する必要がありません。
関数XMLParseはXMLTypeのインスタンスを戻します。value_expr の評価結果がNULLの場合、関数はNULLを戻します。
例17-21 XMLPARSEの使用
SELECT XMLParse(CONTENT
'124 <purchaseOrder poNo="12435">
<customerName> Acme Enterprises</customerName>
<itemNo>32987457</itemNo>
</purchaseOrder>'
WELLFORMED)
AS po FROM DUAL d;
これによって、次の出力が生成されます。
PO ----------------------------------------------- 124 <purchaseOrder poNo="12435"> <customerName>Acme Enterprises</customerName> <itemNo>32987457</itemNo> </purchaseOrder>
Oracle DatabaseのSQL関数XMLColAttValは、渡された引数の値を含むXMLのcolumn要素のフォレストを生成します。この関数は、SQL/XML ANSI-ISO標準関数に対応するためのOracle Databaseの拡張です。図17-12はXMLColAttValの構文を説明するものです。
引数はcolumn要素のname属性の値として使用されます。c_alias値は属性識別子として使用されます。
キーワードASにリテラル文字列c_aliasを続ける方法の他に、AS EVALNAMEに、評価結果が属性識別子として使用される文字列になる式を続ける方法もあります。
引数値value_exprは属性値としてのみ使用されているため、いずれにしてもエスケープする必要はありません。XMLForestとは対照的です。つまり、XMLColAttValを使用すると、SQL列と値をエスケープせずに転送できます。
例17-22 XMLCOLATTVAL: 属性と子要素を持つ要素の生成
次の例では、name属性、雇用日および部門を要素として持つEmp要素を各従業員に1つ作成します。
SELECT XMLElement("Emp",
XMLAttributes(e.first_name ||' '||e.last_name AS "fullname" ),
XMLColAttVal(e.hire_date, e.department_id AS "department"))
AS "RESULT"
FROM hr.employees e
WHERE e.department_id = 30;
この問合せにより生成されるXMLは次のとおりです。（ここではわかりやすいように、この結果をフォーマット出力しています。）
RESULT ----------------------------------------------------------- <Emp fullname="Den Raphaely"> <column name = "HIRE_DATE">1994-12-07</column> <column name = "department">30</column> </Emp> <Emp fullname="Alexander Khoo"> <column name = "HIRE_DATE">1995-05-18</column> <column name = "department">30</column> </Emp> <Emp fullname="Shelli Baida"> <column name = "HIRE_DATE">1997-12-24</column> <column name = "department">30</column> </Emp> <Emp fullname="Sigal Tobias"> <column name = "HIRE_DATE">1997-07-24</column> <column name = "department">30</column> </Emp> <Emp fullname="Guy Himuro"> <column name = "HIRE_DATE">1998-11-15</column> <column name = "department">30</column> </Emp> <Emp fullname="Karen Colmenares"> <column name = "HIRE_DATE">1999-08-10</column> <column name = "department">30</column> </Emp> 6 rows selected.
Oracle DatabaseのSQL関数XMLCDATAを使用して、XML CDATAセクションを生成します。図17-13に構文を示します。
引数value_exprの評価結果は文字列で、生成されるXMLCDATAセクション<![CDATA[string-result]]>の本文として使用されます。ここで、string-resultは、value_exprを評価した結果です。 string-resultが空の文字列の場合、CDATAセクションは空（<![CDATA[]]>）です。
構成されたXMLが有効なXMLのCDATAセクションと異なる場合はエラーが発生します。特に、 string-resultに、左開き大カッコ（]）が連続して2つ含まれてはいけないことに注意してください（]]）。
関数XMLCDATAはXMLTypeのインスタンスを戻します。string-resultがNULLの場合、関数はNULLを戻します。
例17-23 XMLCDATAの使用
SELECT XMLElement("PurchaseOrder",
XMLElement("Address",
XMLCDATA('100 Pennsylvania Ave.'),
XMLElement("City", 'Washington, D.C.')))
AS RESULT FROM DUAL;
これによって、次の出力が生成されます。（ここではわかりやすいように、この結果をフォーマット出力しています。）
RESULT
--------------------------
<PurchaseOrder>
<Address>
<![CDATA[100 Pennsylvania Ave.]]>
<City>Washington, D.C.</City>
</Address>
</PurchaseOrder>
PL/SQLパッケージDBMS_XMLGENは、SQL問合せの結果からXML文書を作成します。XML文書はCLOBまたはXMLType値として取得されます。
fetchインタフェースが用意されるので、それを使用して、取得する行の最大数とスキップする行数を指定できます。たとえば、最初のフェッチで最大10行を取り出し、最初の4行をスキップできます。これは特にWebアプリケーションでのページ区切り要件を満たすために有効です。
DBMS_XMLGENには、ROW、ROWSETなどのタグ名を変更するオプションもあります。DBMS_XMLGENパッケージのパラメータは、取り出す行の数を制限し、タグ名を囲みます。
|
関連項目:
|
図17-14は、パッケージDBMS_XMLGENの使用方法を示しています。手順は次のとおりです。
SQL問合せを実行し、PL/SQL関数newContextをコールすることによって、パッケージからコンテキストを取得します。
そのコンテキストをパッケージ内のすべてのプロシージャまたはファンクションに渡して、様々なオプションを設定します。たとえば、ROW要素の名前を設定するには、setRowTag(ctx)を使用します。このctxは、前のnewContext()コールから取得したコンテキストです。
PL/SQL関数getXMLまたはgetXMLTypeを使用してXML結果を取得します。PL/SQLプロシージャsetMaxRowsを使用してフェッチごとに取り出す行の最大数を設定すると、この関数を繰り返しコールして、コールごとに最大数までの行を取得できます。この関数はXMLデータを（それぞれCLOB値およびXMLTypeのインスタンスとして）戻します。取得された行がない場合、NULLを戻します。取得する行数を決めるには、PL/SQL関数getNumRowsProcessedを使用します。
問合せをリセットして再度開始し、手順3を繰り返すことができます。
PL/SQLプロシージャcloseContextをコールして、すでに割り当てられているリソースを解放します。
SQL問合せと組み合せることにより、メソッドDBMS_XMLGEN.getXMLは通常、次のような結果をCLOB値として戻します。
<?xml version="1.0"?> <ROWSET> <ROW> <EMPLOYEE_ID>100</EMPLOYEE_ID> <FIRST_NAME>Steven</FIRST_NAME> <LAST_NAME>King</LAST_NAME> <EMAIL>SKING</EMAIL> <PHONE_NUMBER>515.123.4567</PHONE_NUMBER> <HIRE_DATE>17-JUN-87</HIRE_DATE> <JOB_ID>AD_PRES</JOB_ID> <SALARY>24000</SALARY> <DEPARTMENT_ID>90</DEPARTMENT_ID> </ROW> <ROW> <EMPLOYEE_ID>101</EMPLOYEE_ID> <FIRST_NAME>Neena</FIRST_NAME> <LAST_NAME>Kochhar</LAST_NAME> <EMAIL>NKOCHHAR</EMAIL> <PHONE_NUMBER>515.123.4568</PHONE_NUMBER> <HIRE_DATE>21-SEP-89</HIRE_DATE> <JOB_ID>AD_VP</JOB_ID> <SALARY>17000</SALARY> <MANAGER_ID>100</MANAGER_ID> <DEPARTMENT_ID>90</DEPARTMENT_ID> </ROW> </ROWSET>
リレーショナル・データとXMLデータの間のデフォルト・マッピングは次のとおりです。
SQL問合せによって戻される各行は、デフォルトの要素名ROWを使用してXML要素にマップします。
SQL問合せによって戻される各列は、ROW要素の子要素にマップします。
結果全体は、ROWSET要素で囲まれます。
バイナリ・データは、16進数表現に変換されます。
要素名ROWおよびROWSETは、それぞれDBMS_XMLGENプロシージャsetRowTagNameおよびsetRowSetTagNameを使用して選択した名前に置換できます。
getXMLにより戻されたCLOB値は、データベースのキャラクタ・セットと同じエンコーディングを持ちます。データベースのキャラクタ・セットがSHIFTJISの場合、戻されるXML文書もSHIFTJISです。
表17-1に、DBMS_XMLGENのファンクションおよびプロシージャの概要を示します。
表17-1 DBMS_XMLGENのファンクションおよびプロシージャ
例17-24 DBMS_XMLGEN: 単純なXMLの生成
この例では、オブジェクト・リレーショナル表から従業員データを選択してXML文書を作成し、結果のCLOBを表に挿入します。
CREATE TABLE temp_clob_tab(result CLOB);
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
result CLOB;
BEGIN
qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM hr.employees');
-- Set the row header to be EMPLOYEE
DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE');
-- Get the result
result := DBMS_XMLGEN.getXML(qryCtx);
INSERT INTO temp_clob_tab VALUES(result);
--Close context
DBMS_XMLGEN.closeContext(qryCtx);
END;
/
この問合せによって、次のXMLが戻されます（表示されるのは結果の一部のみです）。
SELECT * FROM temp_clob_tab WHERE ROWNUM = 1;
RESULT
-------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<EMPLOYEE>
<EMPLOYEE_ID>100</EMPLOYEE_ID>
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>King</LAST_NAME>
<EMAIL>SKING</EMAIL>
<PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
<HIRE_DATE>17-JUN-87</HIRE_DATE>
<JOB_ID>AD_PRES</JOB_ID>
<SALARY>24000</SALARY>
<DEPARTMENT_ID>90</DEPARTMENT_ID>
</EMPLOYEE>
...
1 row selected.
例17-25 DBMS_XMLGEN: ページ区切りを使用した単純なXMLの生成（フェッチ）
すべての行に対してXML全体を生成するかわりに、DBMS_XMLGENが提供するfetchインタフェースを使用して、1回に固定の行数を取り出すことができます。これによって特に行数が多い場合に、応答時間が短縮され、また結果のXMLにドキュメント・オブジェクト・モデル（DOM）Application Program Interface（API）を使用する必要があるアプリケーションのスケーリングに有効です。
次の例は、DBMS_XMLGENを使用してhr.employees表から結果を取り出す方法を示します。
-- Create a table to hold the results
CREATE TABLE temp_clob_tab(result clob);
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
result CLOB;
BEGIN
-- Get the query context;
qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM hr.employees');
-- Set the maximum number of rows to be 2
DBMS_XMLGEN.setMaxRows(qryCtx, 2);
LOOP
-- Get the result
result := DBMS_XMLGEN.getXML(qryCtx);
-- If no rows were processed, then quit
EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
-- Do some processing with the lob data
-- Here, we insert the results into a table.
-- You can print the lob out, output it to a stream,
-- put it in a queue, or do any other processing.
INSERT INTO temp_clob_tab VALUES(result);
END LOOP;
--close context
DBMS_XMLGEN.closeContext(qryCtx);
END;
/
SELECT * FROM temp_clob_tab WHERE rownum <3;
RESULT
----------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPLOYEE_ID>100</EMPLOYEE_ID>
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>King</LAST_NAME>
<EMAIL>SKING</EMAIL>
<PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
<HIRE_DATE>17-JUN-87</HIRE_DATE>
<JOB_ID>AD_PRES</JOB_ID>
<SALARY>24000</SALARY>
<DEPARTMENT_ID>90</DEPARTMENT_ID>
</ROW>
<ROW>
<EMPLOYEE_ID>101</EMPLOYEE_ID>
<FIRST_NAME>Neena</FIRST_NAME>
<LAST_NAME>Kochhar</LAST_NAME>
<EMAIL>NKOCHHAR</EMAIL>
<PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
<HIRE_DATE>21-SEP-89</HIRE_DATE>
<JOB_ID>AD_VP</JOB_ID>
<SALARY>17000</SALARY>
<MANAGER_ID>100</MANAGER_ID>
<DEPARTMENT_ID>90</DEPARTMENT_ID>
</ROW>
</ROWSET>
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPLOYEE_ID>102</EMPLOYEE_ID>
<FIRST_NAME>Lex</FIRST_NAME>
<LAST_NAME>De Haan</LAST_NAME>
<EMAIL>LDEHAAN</EMAIL>
<PHONE_NUMBER>515.123.4569</PHONE_NUMBER>
<HIRE_DATE>13-JAN-93</HIRE_DATE>
<JOB_ID>AD_VP</JOB_ID>
<SALARY>17000</SALARY>
<MANAGER_ID>100</MANAGER_ID>
<DEPARTMENT_ID>90</DEPARTMENT_ID>
</ROW>
<ROW>
<EMPLOYEE_ID>103</EMPLOYEE_ID>
<FIRST_NAME>Alexander</FIRST_NAME>
<LAST_NAME>Hunold</LAST_NAME>
<EMAIL>AHUNOLD</EMAIL>
<PHONE_NUMBER>590.423.4567</PHONE_NUMBER>
<HIRE_DATE>03-JAN-90</HIRE_DATE>
<JOB_ID>IT_PROG</JOB_ID>
<SALARY>9000</SALARY>
<MANAGER_ID>102</MANAGER_ID>
<DEPARTMENT_ID>60</DEPARTMENT_ID>
</ROW>
</ROWSET>
2 rows selected.
例17-26 DBMS_XMLGEN: オブジェクト型を持つネストされたXMLの生成
この例では、オブジェクト型を使用してネスト構造を表現しています。
CREATE TABLE new_departments(department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(20));
CREATE TABLE new_employees(employee_id NUMBER PRIMARY KEY,
last_name VARCHAR2(20),
department_id NUMBER REFERENCES new_departments);
CREATE TYPE emp_t AS OBJECT("@employee_id" NUMBER,
last_name VARCHAR2(20));
/
INSERT INTO new_departments VALUES(10, 'SALES');
INSERT INTO new_departments VALUES(20, 'ACCOUNTING');
INSERT INTO new_employees VALUES(30, 'Scott', 10);
INSERT INTO new_employees VALUES(31, 'Mary', 10);
INSERT INTO new_employees VALUES(40, 'John', 20);
INSERT INTO new_employees VALUES(41, 'Jerry', 20);
COMMIT;
CREATE TYPE emplist_t AS TABLE OF emp_t;
/
CREATE TYPE dept_t AS OBJECT("@department_id" NUMBER,
department_name VARCHAR2(20),
emplist emplist_t);
/
CREATE TABLE temp_clob_tab(result CLOB);
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
result CLOB;
BEGIN
DBMS_XMLGEN.setRowTag(qryCtx, NULL);
qryCtx := DBMS_XMLGEN.newContext
('SELECT dept_t(department_id,
department_name,
CAST(MULTISET
(SELECT e.employee_id, e.last_name
FROM new_employees e
WHERE e.department_id = d.department_id)
AS emplist_t))
AS deptxml
FROM new_departments d');
-- now get the result
result := DBMS_XMLGEN.getXML(qryCtx);
INSERT INTO temp_clob_tab VALUES (result);
-- close context
DBMS_XMLGEN.closeContext(qryCtx);
END;
/
SELECT * FROM temp_clob_tab;
結果として戻されるXMLは、次のとおりです。
RESULT
--------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPTXML department_id="10">
<DEPARTMENT_NAME>SALES</DEPARTMENT_NAME>
<EMPLIST>
<EMP_T employee_id="30">
<LAST_NAME>Scott</LAST_NAME>
</EMP_T>
<EMP_T employee_id="31">
<LAST_NAME>Mary</LAST_NAME>
</EMP_T>
</EMPLIST>
</DEPTXML>
</ROW>
<ROW>
<DEPTXML department_id="20">
<DEPARTMENT_NAME>ACCOUNTING</DEPARTMENT_NAME>
<EMPLIST>
<EMP_T employee_id="40">
<LAST_NAME>John</LAST_NAME>
</EMP_T>
<EMP_T employee_id="41">
<LAST_NAME>Jerry</LAST_NAME>
</EMP_T>
</EMPLIST>
</DEPTXML>
</ROW>
</ROWSET>
1 row selected.
リレーショナル・データの場合、結果はネストされた要素がないXML文書になります。ネストしたXML構造にするには、オブジェクト・リレーショナル・データを使用します。この場合のマッピングは次のとおりです。
オブジェクト型はXML要素をマップします。第6章「XML Schemaの格納と問合せ: 基本」を参照してください。
型の属性は、親要素のサブ要素にマップされます。
|
注意: 複雑な構造にするには、オブジェクト型を使用し、オブジェクト・ビューまたはオブジェクト表を作成します。正規マッピングを使用して、オブジェクト・インスタンスをXMLにマップしてください。アットマーク（ |
例17-27 DBMS_XMLGEN: ユーザー定義のデータ型インスタンスを使用した、ネストされたXMLの生成
ユーザー定義のデータ型インスタンスをDBMS_XMLGENファンクションに入力すると、ユーザー定義データ型は正規マッピングを使用してXML文書にマップされます。ユーザー定義のデータ型の属性はXML要素にマップされます。アットマーク「@」で始まる名前の属性は、その前にある要素の属性にマップされます。
ユーザー定義のデータ型インスタンスを使用して、結果のXML文書内でネストできます。たとえば、EMPおよびDEPTという2つの表を考えてみます。
CREATE TABLE dept(deptno NUMBER PRIMARY KEY, dname VARCHAR2(20));
CREATE TABLE emp(empno NUMBER PRIMARY KEY, ename VARCHAR2(20),
deptno NUMBER REFERENCES dept);
この場合、部門とその従業員のデータの階層ビューを生成するには、適切なオブジェクト型を定義してデータベース内に構造を作成できます。次に例を示します。
-- empno is preceded by an at-sign (@) to indicate that it must
-- be mapped as an attribute of the enclosing Employee element.
CREATE TYPE emp_t AS OBJECT("@empno" NUMBER, -- empno defined as attribute
ename VARCHAR2(20));
/
INSERT INTO DEPT VALUES(10, 'Sports');
INSERT INTO DEPT VALUES(20, 'Accounting');
INSERT INTO EMP VALUES(200, 'John', 10);
INSERT INTO EMP VALUES(300, 'Jack', 10);
INSERT INTO EMP VALUES(400, 'Mary', 20);
INSERT INTO EMP VALUES(500, 'Jerry', 20);
COMMIT;
CREATE TYPE emplist_t AS TABLE OF emp_t;
/
CREATE TYPE dept_t AS OBJECT("@deptno" NUMBER,
dname VARCHAR2(20),
emplist emplist_t);
/
-- Department type dept_t contains a list of employees.
-- We can now query the employee and department tables and get
-- the result as an XML document, as follows:
CREATE TABLE temp_clob_tab(result CLOB);
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
RESULT CLOB;
BEGIN
-- get query context
qryCtx := DBMS_XMLGEN.newContext(
'SELECT dept_t(deptno,
dname,
CAST(MULTISET(SELECT empno, ename
FROM emp e
WHERE e.deptno = d.deptno)
AS emplist_t))
AS deptxml
FROM dept d');
-- set maximum number of rows to 5
DBMS_XMLGEN.setMaxRows(qryCtx, 5);
-- set no row tag for this result, since there is a single ADT column
DBMS_XMLGEN.setRowTag(qryCtx, NULL);
LOOP
-- get result
result := DBMS_XMLGEN.getXML(qryCtx);
-- if there were no rows processed, then quit
EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
-- do something with the result
INSERT INTO temp_clob_tab VALUES (result);
END LOOP;
END;
/
関数MULTISET演算子は、その部門に勤務する従業員をリストとして扱い、関数CASTがリストを適切なコレクション型に割り当てます。部門インスタンスが作成され、DBMS_XMLGENルーチンがオブジェクト・インスタンスのXMLを作成します。
SELECT * FROM temp_clob_tab;
RESULT
---------------------------------
<?xml version="1.0"?>
<ROWSET>
<DEPTXML deptno="10">
<DNAME>Sports</DNAME>
<EMPLIST>
<EMP_T empno="200">
<ENAME>John</ENAME>
</EMP_T>
<EMP_T empno="300">
<ENAME>Jack</ENAME>
</EMP_T>
</EMPLIST>
</DEPTXML>
<DEPTXML deptno="20">
<DNAME>Accounting</DNAME>
<EMPLIST>
<EMP_T empno="400">
<ENAME>Mary</ENAME>
</EMP_T>
<EMP_T empno="500">
<ENAME>Jerry</ENAME>
</EMP_T>
</EMPLIST>
</DEPTXML>
</ROWSET>
1 row selected.
デフォルト名ROWは、NULLに設定しているため存在しません。deptnoおよびempnoは囲み要素の属性になっています。
例17-28 DBMS_XMLGEN: XML発注書の生成
この例ではDBMS_XMLGEN.getXMLType を使用し、オブジェクト・ビューによって発注書をXML形式で生成します。
-- Create relational schema and define object views
-- DBMS_XMLGEN maps user-defined data-type attribute names that start
-- with an at-sign (@) to XML attributes
-- Purchase Order Object View Model
-- PhoneList varray object type
CREATE TYPE phonelist_vartyp AS VARRAY(10) OF VARCHAR2(20)
/
-- Address object type
CREATE TYPE address_typ AS OBJECT(Street VARCHAR2(200),
City VARCHAR2(200),
State CHAR(2),
Zip VARCHAR2(20))
/
-- Customer object type
CREATE TYPE customer_typ AS OBJECT(CustNo NUMBER,
CustName VARCHAR2(200),
Address address_typ,
PhoneList phonelist_vartyp)
/
-- StockItem object type
CREATE TYPE stockitem_typ AS OBJECT("@StockNo" NUMBER,
Price NUMBER,
TaxRate NUMBER)
/
-- LineItems object type
CREATE TYPE lineitem_typ AS OBJECT("@LineItemNo" NUMBER,
Item stockitem_typ,
Quantity NUMBER,
Discount NUMBER)
/
-- LineItems ordered collection table
CREATE TYPE lineitems_ntabtyp AS TABLE OF lineitem_typ
/
-- Purchase Order object type
CREATE TYPE po_typ AUTHID CURRENT_USER
AS OBJECT(PONO NUMBER,
Cust_ref REF customer_typ,
OrderDate DATE,
ShipDate TIMESTAMP,
LineItems_ntab lineitems_ntabtyp,
ShipToAddr address_typ)
/
-- Create Purchase Order relational model tables
-- Customer table
CREATE TABLE customer_tab(CustNo NUMBER NOT NULL,
CustName VARCHAR2(200),
Street VARCHAR2(200),
City VARCHAR2(200),
State CHAR(2),
Zip VARCHAR2(20),
Phone1 VARCHAR2(20),
Phone2 VARCHAR2(20),
Phone3 VARCHAR2(20),
CONSTRAINT cust_pk PRIMARY KEY (CustNo));
-- Purchase Order table
CREATE TABLE po_tab (PONo NUMBER, /* purchase order number */
Custno NUMBER /* foreign KEY referencing customer */
CONSTRAINT po_cust_fk REFERENCES customer_tab,
OrderDate DATE, /* date of order */
ShipDate TIMESTAMP, /* date to be shipped */
ToStreet VARCHAR2(200), /* shipto address */
ToCity VARCHAR2(200),
ToState CHAR(2),
ToZip VARCHAR2(20),
CONSTRAINT po_pk PRIMARY KEY(PONo));
--Stock Table
CREATE TABLE stock_tab (StockNo NUMBER CONSTRAINT stock_uk UNIQUE,
Price NUMBER,
TaxRate NUMBER);
--Line Items table
CREATE TABLE lineitems_tab(LineItemNo NUMBER,
PONo NUMBER
CONSTRAINT li_po_fk REFERENCES po_tab,
StockNo NUMBER,
Quantity NUMBER,
Discount NUMBER,
CONSTRAINT li_pk PRIMARY KEY (PONo, LineItemNo));
-- Create Object views
-- Customer Object View
CREATE OR REPLACE VIEW customer OF customer_typ
WITH OBJECT IDENTIFIER(CustNo)
AS SELECT c.custno, c.custname,
address_typ(c.street, c.city, c.state, c.zip),
phonelist_vartyp(phone1, phone2, phone3)
FROM customer_tab c;
--Purchase order view
CREATE OR REPLACE VIEW po OF po_typ
WITH OBJECT IDENTIFIER (PONo)
AS SELECT p.pono, make_ref(Customer, P.Custno), p.orderdate, p.shipdate,
CAST(MULTISET(
SELECT lineitem_typ(l.lineitemno, stockitem_typ(l.stockno,
s.price,
s.taxrate),
l.quantity, l.discount)
FROM lineitems_tab l, stock_tab s
WHERE l.pono = p.pono AND s.stockno=l.stockno)
AS lineitems_ntabtyp),
address_typ(p.tostreet,p.tocity, p.tostate, p.tozip)
FROM po_tab p;
-- Create table with XMLType column to store purchase order in XML format
CREATE TABLE po_xml_tab(poid NUMBER, podoc XMLType)
/
-- Populate data
-------------------
-- Establish Inventory
INSERT INTO stock_tab VALUES(1004, 6750.00, 2);
INSERT INTO stock_tab VALUES(1011, 4500.23, 2);
INSERT INTO stock_tab VALUES(1534, 2234.00, 2);
INSERT INTO stock_tab VALUES(1535, 3456.23, 2);
-- Register Customers
INSERT INTO customer_tab
VALUES (1, 'Jean Nance', '2 Avocet Drive',
'Redwood Shores', 'CA', '95054',
'415-555-1212', NULL, NULL);
INSERT INTO customer_tab
VALUES (2, 'John Nike', '323 College Drive',
'Edison', 'NJ', '08820',
'609-555-1212', '201-555-1212', NULL);
-- Place orders
INSERT INTO po_tab
VALUES (1001, 1, '10-APR-1997', '10-MAY-1997',
NULL, NULL, NULL, NULL);
INSERT INTO po_tab
VALUES (2001, 2, '20-APR-1997', '20-MAY-1997',
'55 Madison Ave', 'Madison', 'WI', '53715');
-- Detail line items
INSERT INTO lineitems_tab VALUES(01, 1001, 1534, 12, 0);
INSERT INTO lineitems_tab VALUES(02, 1001, 1535, 10, 10);
INSERT INTO lineitems_tab VALUES(01, 2001, 1004, 1, 0);
INSERT INTO lineitems_tab VALUES(02, 2001, 1011, 2, 1);
-- Use package DBMS_XMLGEN to generate purchase order in XML format
-- and store XMLType in table po_xml
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
pxml XMLType;
cxml CLOB;
BEGIN
-- get query context;
qryCtx := DBMS_XMLGEN.newContext('SELECT pono,deref(cust_ref) customer,
p.orderdate,
p.shipdate,
lineitems_ntab lineitems,
shiptoaddr
FROM po p');
-- set maximum number of rows to be 1,
DBMS_XMLGEN.setMaxRows(qryCtx, 1);
-- set ROWSET tag to NULL and ROW tag to PurchaseOrder
DBMS_XMLGEN.setRowSetTag(qryCtx, NULL);
DBMS_XMLGEN.setRowTag(qryCtx, 'PurchaseOrder');
LOOP
-- get purchase order in XML format
pxml := DBMS_XMLGEN.getXMLType(qryCtx);
-- if there were no rows processed, then quit
EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
-- Store XMLType po in po_xml table (get the pono out)
INSERT INTO po_xml_tab(poid, poDoc)
VALUES(pxml.extract('//PONO/text()').getNumberVal(), pxml);
END LOOP;
END;
/
この問合せによって、2件の発注書が生成されます。
SELECT x.podoc.getCLOBVal() xpo FROM po_xml_tab x;
XPO
---------------------------------------------------
<PurchaseOrder>
<PONO>1001</PONO>
<CUSTOMER>
<CUSTNO>1</CUSTNO>
<CUSTNAME>Jean Nance</CUSTNAME>
<ADDRESS>
<STREET>2 Avocet Drive</STREET>
<CITY>Redwood Shores</CITY>
<STATE>CA</STATE>
<ZIP>95054</ZIP>
</ADDRESS>
<PHONELIST>
<VARCHAR2>415-555-1212</VARCHAR2>
</PHONELIST>
</CUSTOMER>
<ORDERDATE>10-APR-97</ORDERDATE>
<SHIPDATE>10-MAY-97 12.00.00.000000 AM</SHIPDATE>
<LINEITEMS>
<LINEITEM_TYP LineItemNo="1">
<ITEM StockNo="1534">
<PRICE>2234</PRICE>
<TAXRATE>2</TAXRATE>
</ITEM>
<QUANTITY>12</QUANTITY>
<DISCOUNT>0</DISCOUNT>
</LINEITEM_TYP>
<LINEITEM_TYP LineItemNo="2">
<ITEM StockNo="1535">
<PRICE>3456.23</PRICE>
<TAXRATE>2</TAXRATE>
</ITEM>
<QUANTITY>10</QUANTITY>
<DISCOUNT>10</DISCOUNT>
</LINEITEM_TYP>
</LINEITEMS>
<SHIPTOADDR/>
</PurchaseOrder>
<PurchaseOrder>
<PONO>2001</PONO>
<CUSTOMER>
<CUSTNO>2</CUSTNO>
<CUSTNAME>John Nike</CUSTNAME>
<ADDRESS>
<STREET>323 College Drive</STREET>
<CITY>Edison</CITY>
<STATE>NJ</STATE>
<ZIP>08820</ZIP>
</ADDRESS>
<PHONELIST>
<VARCHAR2>609-555-1212</VARCHAR2>
<VARCHAR2>201-555-1212</VARCHAR2>
</PHONELIST>
</CUSTOMER>
<ORDERDATE>20-APR-97</ORDERDATE>
<SHIPDATE>20-MAY-97 12.00.00.000000 AM</SHIPDATE>
<LINEITEMS>
<LINEITEM_TYP LineItemNo="1">
<ITEM StockNo="1004">
<PRICE>6750</PRICE>
<TAXRATE>2</TAXRATE>
</ITEM>
<QUANTITY>1</QUANTITY>
<DISCOUNT>0</DISCOUNT>
</LINEITEM_TYP>
<LINEITEM_TYP LineItemNo="2">
<ITEM StockNo="1011">
<PRICE>4500.23</PRICE>
<TAXRATE>2</TAXRATE>
</ITEM>
<QUANTITY>2</QUANTITY>
<DISCOUNT>1</DISCOUNT>
</LINEITEM_TYP>
</LINEITEMS>
<SHIPTOADDR>
<STREET>55 Madison Ave</STREET>
<CITY>Madison</CITY>
<STATE>WI</STATE>
<ZIP>53715</ZIP>
</SHIPTOADDR>
</PurchaseOrder>
2 rows selected.
例17-29 DBMS_XMLGEN: REFカーソルからの新規コンテキスト・ハンドルの生成
この例では、問合せのカーソル変数の開き方、およびカーソル変数を使用してDBMS_XMLGENの新しいコンテキスト・ハンドルを作成する方法を説明します。
CREATE TABLE emp_tab(emp_id NUMBER PRIMARY KEY,
name VARCHAR2(20),
dept_id NUMBER);
Table created.
INSERT INTO emp_tab VALUES(122, 'Scott', 301);
1 row created.
INSERT INTO emp_tab VALUES(123, 'Mary', 472);
1 row created.
INSERT INTO emp_tab VALUES(124, 'John', 93);
1 row created.
INSERT INTO emp_tab VALUES(125, 'Howard', 488);
1 row created.
INSERT INTO emp_tab VALUES(126, 'Sue', 16);
1 row created.
COMMIT;
DECLARE
ctx NUMBER;
maxrow NUMBER;
xmldoc CLOB;
refcur SYS_REFCURSOR;
BEGIN
DBMS_LOB.createtemporary(xmldoc, TRUE);
maxrow := 3;
OPEN refcur FOR 'SELECT * FROM emp_tab WHERE ROWNUM <= :1' USING maxrow;
ctx := DBMS_XMLGEN.newContext(refcur);
-- xmldoc will have 3 rows
DBMS_XMLGEN.getXML(ctx, xmldoc, DBMS_XMLGEN.NONE);
DBMS_OUTPUT.put_line(xmldoc);
DBMS_LOB.freetemporary(xmldoc);
CLOSE refcur;
DBMS_XMLGEN.closeContext(ctx);
END;
/
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMP_ID>122</EMP_ID>
<NAME>Scott</NAME>
<DEPT_ID>301</DEPT_ID>
</ROW>
<ROW>
<EMP_ID>123</EMP_ID>
<NAME>Mary</NAME>
<DEPT_ID>472</DEPT_ID>
</ROW>
<ROW>
<EMP_ID>124</EMP_ID>
<NAME>John</NAME>
<DEPT_ID>93</DEPT_ID>
</ROW>
</ROWSET>
PL/SQL procedure successfully completed.
|
関連項目: カーソル変数（REF CURSOR）の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
例17-30 DBMS_XMLGEN: NULL処理の指定
CREATE TABLE emp_tab(emp_id NUMBER PRIMARY KEY,
name VARCHAR2(20),
dept_id NUMBER);
Table created.
INSERT INTO emp_tab VALUES(30, 'Scott', NULL);
1 row created.
INSERT INTO emp_tab VALUES(31, 'Mary', NULL);
1 row created.
INSERT INTO emp_tab VALUES(40, 'John', NULL);
1 row created.
COMMIT;
CREATE TABLE temp_clob_tab(result CLOB);
Table created.
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
result CLOB;
BEGIN
qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM emp_tab where name = :NAME');
-- Set the row header to be EMPLOYEE
DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE');
-- Drop nulls
DBMS_XMLGEN.setBindValue(qryCtx, 'NAME', 'Scott');
DBMS_XMLGEN.setNullHandling(qryCtx, DBMS_XMLGEN.DROP_NULLS);
result := DBMS_XMLGEN.getXML(qryCtx);
INSERT INTO temp_clob_tab VALUES(result);
-- Null attribute
DBMS_XMLGEN.setBindValue(qryCtx, 'NAME', 'Mary');
DBMS_XMLGEN.setNullHandling(qryCtx, DBMS_XMLGEN.NULL_ATTR);
result := DBMS_XMLGEN.getXML(qryCtx);
INSERT INTO temp_clob_tab VALUES(result);
-- Empty tag
DBMS_XMLGEN.setBindValue(qryCtx, 'NAME', 'John');
DBMS_XMLGEN.setNullHandling(qryCtx, DBMS_XMLGEN.EMPTY_TAG);
result := DBMS_XMLGEN.getXML(qryCtx);
INSERT INTO temp_clob_tab VALUES(result);
--Close context
DBMS_XMLGEN.closeContext(qryCtx);
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM temp_clob_tab;
RESULT
-------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<EMPLOYEE>
<EMP_ID>30</EMP_ID>
<NAME>Scott</NAME>
</EMPLOYEE>
</ROWSET>
<?xml version="1.0"?>
<ROWSET xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">
<EMPLOYEE>
<EMP_ID>31</EMP_ID>
<NAME>Mary</NAME>
<DEPT_ID xsi:nil = "true"/>
</EMPLOYEE>
</ROWSET>
<?xml version="1.0"?>
<ROWSET>
<EMPLOYEE>
<EMP_ID>40</EMP_ID>
<NAME>John</NAME>
<DEPT_ID/>
</EMPLOYEE>
</ROWSET>
3 rows selected.
例17-31 DBMS_XMLGEN: 階層問合せを使用した再帰XMLの生成
関数DBMS_XMLGEN.newContextFromHierarchy通常CONNECT BY句を使用して形成される階層問合せ文字列を引数として取ります。再帰要素が含まれる階層XML文書の生成に使用されるコンテキストを戻します。
階層問合せでは、レベル番号（CONNECT BY問合せを使用して生成される疑似列）とXMLTypeの2つの列が戻されます。レベルは、結果XML文書の階層内のXMLType値の位置を判断するために使用されます。
newContextFromHierarchyから作成されたコンテキストに対して、スキップ行数または最大行数を設定するとエラーになります。
たとえば、DBMS_ XMLGEN.newContextFromHierarchyを使用して、マネージャと従業員の階層を生成できます。
CREATE TABLE sqlx_display(id NUMBER, xmldoc XMLType);
Table created.
DECLARE
qryctx DBMS_XMLGEN.ctxhandle;
result XMLType;
BEGIN
qryctx :=
DBMS_XMLGEN.newContextFromHierarchy(
'SELECT level,
XMLElement("employees",
XMLElement("enumber", employee_id),
XMLElement("name", last_name),
XMLElement("Salary", salary),
XMLElement("Hiredate", hire_date))
FROM hr.employees
START WITH last_name=''De Haan'' CONNECT BY PRIOR employee_id=manager_id
ORDER SIBLINGS BY hire_date');
result := DBMS_XMLGEN.getxmltype(qryctx);
DBMS_OUTPUT.put_line('<result num rows>');
DBMS_OUTPUT.put_line(to_char(DBMS_XMLGEN.getNumRowsProcessed(qryctx)));
DBMS_OUTPUT.put_line('</result num rows>');
INSERT INTO sqlx_display VALUES (2, result);
COMMIT;
DBMS_XMLGEN.closecontext(qryctx);
END;
/
<result num rows>
6
</result num rows>
PL/SQL procedure successfully completed.
SELECT xmldoc FROM sqlx_display WHERE id = 2;
XMLDOC
-----------------------------------------------------
<?xml version="1.0"?>
<employees>
<enumber>102</enumber>
<name>De Haan</name>
<Salary>17000</Salary>
<Hiredate>1993-01-13</Hiredate>
<employees>
<enumber>103</enumber>
<name>Hunold</name>
<Salary>9000</Salary>
<Hiredate>1990-01-03</Hiredate>
<employees>
<enumber>104</enumber>
<name>Ernst</name>
<Salary>6000</Salary>
<Hiredate>1991-05-21</Hiredate>
</employees>
<employees>
<enumber>105</enumber>
<name>Austin</name>
<Salary>4800</Salary>
<Hiredate>1997-06-25</Hiredate>
</employees>
<employees>
<enumber>106</enumber>
<name>Pataballa</name>
<Salary>4800</Salary>
<Hiredate>1998-02-05</Hiredate>
</employees>
<employees>
<enumber>107</enumber>
<name>Lorentz</name>
<Salary>4200</Salary>
<Hiredate>1999-02-07</Hiredate>
</employees>
</employees>
</employees>
1 row selected.
デフォルトでは、ROWSETタグはNULLです。つまり、XML結果を囲むために使用されるデフォルトのROWSETタグはありません。ただし、次のように、setRowSetTagプロシージャを使用するとROWSETタグを明示的に設定できます。
CREATE TABLE gg(x XMLType);
Table created.
DECLARE
qryctx DBMS_XMLGEN.ctxhandle;
result CLOB;
BEGIN
qryctx := DBMS_XMLGEN.newContextFromHierarchy(
'SELECT level,
XMLElement("NAME", last_name) AS myname FROM hr.employees
CONNECT BY PRIOR employee_id=manager_id
START WITH employee_id = 102');
DBMS_XMLGEN.setRowSetTag(qryctx, 'mynum_hierarchy');
result:=DBMS_XMLGEN.getxml(qryctx);
DBMS_OUTPUT.put_line('<result num rows>');
DBMS_OUTPUT.put_line(to_char(DBMS_XMLGEN.getNumRowsProcessed(qryctx)));
DBMS_OUTPUT.put_line('</result num rows>');
INSERT INTO gg VALUES(XMLType(result));
COMMIT;
DBMS_XMLGEN.closecontext(qryctx);
END;
/
<result num rows>
6
</result num rows>
PL/SQL procedure successfully completed.
SELECT * FROM gg;
X
----------------------------------------------------------
<?xml version="1.0"?>
<mynum_hierarchy>
<NAME>De Haan
<NAME>Hunold
<NAME>Ernst</NAME>
<NAME>Austin</NAME>
<NAME>Pataballa</NAME>
<NAME>Lorentz</NAME>
</NAME>
</NAME>
</mynum_hierarchy>
1 row selected.
例17-32 DBMS_XMLGEN: setBindValueメソッドによる問合せ変数のバインド
コンテキストの作成に使用する問合せ文字列にホスト変数が含まれている場合、問合せの実行前にsetBindValueメソッドを使用して、変数値を指定できます。
-- Bind one variable
DECLARE
ctx NUMBER;
xmldoc CLOB;
BEGIN
ctx := DBMS_XMLGEN.newContext(
'SELECT * FROM employees WHERE employee_id = :NO');
DBMS_XMLGEN.setBindValue(ctx, 'NO', '145');
xmldoc := DBMS_XMLGEN.getXML(ctx);
DBMS_OUTPUT.put_line(xmldoc);
DBMS_XMLGEN.closeContext(ctx);
EXCEPTION
WHEN OTHERS THEN DBMS_XMLGEN.closeContext(ctx);
RAISE;
END;
/
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPLOYEE_ID>145</EMPLOYEE_ID>
<FIRST_NAME>John</FIRST_NAME>
<LAST_NAME>Russell</LAST_NAME>
<EMAIL>JRUSSEL</EMAIL>
<PHONE_NUMBER>011.44.1344.429268</PHONE_NUMBER>
<HIRE_DATE>01-OCT-96</HIRE_DATE>
<JOB_ID>SA_MAN</JOB_ID>
<SALARY>14000</SALARY>
<COMMISSION_PCT>.4</COMMISSION_PCT>
<MANAGER_ID>100</MANAGER_ID>
<DEPARTMENT_ID>80</DEPARTMENT_ID>
</ROW>
</ROWSET>
PL/SQL procedure successfully completed.
--Bind one variable twice with different values
DECLARE
ctx NUMBER;
xmldoc CLOB;
BEGIN
ctx := DBMS_XMLGEN.newContext('SELECT * FROM employees
WHERE hire_date = :MDATE');
DBMS_XMLGEN.setBindValue(ctx, 'MDATE', '01-OCT-96');
xmldoc := DBMS_XMLGEN.getXML(ctx);
DBMS_OUTPUT.put_line(xmldoc);
DBMS_XMLGEN.setBindValue(ctx, 'MDATE', '10-MAR-97');
xmldoc := DBMS_XMLGEN.getXML(ctx);
DBMS_OUTPUT.put_line(xmldoc);
DBMS_XMLGEN.closeContext(ctx);
EXCEPTION
WHEN OTHERS THEN DBMS_XMLGEN.closeContext(ctx);
RAISE;
END;
/
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPLOYEE_ID>145</EMPLOYEE_ID>
<FIRST_NAME>John</FIRST_NAME>
<LAST_NAME>Russell</LAST_NAME>
<EMAIL>JRUSSEL</EMAIL>
<PHONE_NUMBER>011.44.1344.429268</PHONE_NUMBER>
<HIRE_DATE>01-OCT-96</HIRE_DATE>
<JOB_ID>SA_MAN</JOB_ID>
<SALARY>14000</SALARY>
<COMMISSION_PCT>.4</COMMISSION_PCT>
<MANAGER_ID>100</MANAGER_ID>
<DEPARTMENT_ID>80</DEPARTMENT_ID>
</ROW>
</ROWSET>
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPLOYEE_ID>147</EMPLOYEE_ID>
<FIRST_NAME>Alberto</FIRST_NAME>
<LAST_NAME>Errazuriz</LAST_NAME>
<EMAIL>AERRAZUR</EMAIL>
<PHONE_NUMBER>011.44.1344.429278</PHONE_NUMBER>
<HIRE_DATE>10-MAR-97</HIRE_DATE>
<JOB_ID>SA_MAN</JOB_ID>
<SALARY>12000</SALARY>
<COMMISSION_PCT>.3</COMMISSION_PCT>
<MANAGER_ID>100</MANAGER_ID>
<DEPARTMENT_ID>80</DEPARTMENT_ID>
</ROW>
<ROW>
<EMPLOYEE_ID>159</EMPLOYEE_ID>
<FIRST_NAME>Lindsey</FIRST_NAME>
<LAST_NAME>Smith</LAST_NAME>
<EMAIL>LSMITH</EMAIL>
<PHONE_NUMBER>011.44.1345.729268</PHONE_NUMBER>
<HIRE_DATE>10-MAR-97</HIRE_DATE>
<JOB_ID>SA_REP</JOB_ID>
<SALARY>8000</SALARY>
<COMMISSION_PCT>.3</COMMISSION_PCT>
<MANAGER_ID>146</MANAGER_ID>
<DEPARTMENT_ID>80</DEPARTMENT_ID>
</ROW>
</ROWSET>
PL/SQL procedure successfully completed.
-- Bind two variables
DECLARE
ctx NUMBER;
xmldoc CLOB;
BEGIN
ctx := DBMS_XMLGEN.newContext('SELECT * FROM employees
WHERE employee_id = :NO
AND hire_date = :MDATE');
DBMS_XMLGEN.setBindValue(ctx, 'NO', '145');
DBMS_XMLGEN.setBindValue(ctx, 'MDATE', '01-OCT-96');
xmldoc := DBMS_XMLGEN.getXML(ctx);
DBMS_OUTPUT.put_line(xmldoc);
DBMS_XMLGEN.closeContext(ctx);
EXCEPTION
WHEN OTHERS THEN DBMS_XMLGEN.closeContext(ctx);
RAISE;
END;
/
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPLOYEE_ID>145</EMPLOYEE_ID>
<FIRST_NAME>John</FIRST_NAME>
<LAST_NAME>Russell</LAST_NAME>
<EMAIL>JRUSSEL</EMAIL>
<PHONE_NUMBER>011.44.1344.429268</PHONE_NUMBER>
<HIRE_DATE>01-OCT-96</HIRE_DATE>
<JOB_ID>SA_MAN</JOB_ID>
<SALARY>14000</SALARY>
<COMMISSION_PCT>.4</COMMISSION_PCT>
<MANAGER_ID>100</MANAGER_ID>
<DEPARTMENT_ID>80</DEPARTMENT_ID>
</ROW>
</ROWSET>
PL/SQL procedure successfully completed.
このOracle Database固有のSQL関数は、SQL/XML標準関数XMLElementに類似していますが、単一の引数を取り、結果をXMLTypeインスタンスに変換するという点で異なります。他のXML生成関数とは異なり、sys_XMLGenは常に整形式のXML文書を戻します。また、問合せレベルで機能するDBMS_XMLGENとは異なり、sys_XMLGenは行レベルで機能し、各行に対してXML文書を戻します。
例17-33 SYS_XMLGENを使用したXMLの作成
この問合せでは、SQL関数sys_XMLGenはXMLインスタンスを問い合せ、リレーショナル・データの各行にXML文書を戻します。
SELECT sys_XMLGen(employee_id) AS "result" FROM employees WHERE first_name LIKE 'John%';
結果のXML文書は次のようになります。
result --------------- <?xml version="1.0"?> <EMPLOYEE_ID>110</EMPLOYEE_ID> <?xml version="1.0"?> <EMPLOYEE_ID>139</EMPLOYEE_ID> <?xml version="1.0"?> <EMPLOYEE_ID>145</EMPLOYEE_ID> 3 rows selected.
SYS_XMLGENの構文
SQL関数sys_XMLGenは、XML文書に変換するスカラー値の引数、オブジェクト型またはXMLTypeインスタンスを取ります。また、結果のXML文書のフォーマット・オプションを指定するオプションのXMLFormatオブジェクト（以前のXMLGenFormatType）も取ります（オプション）。この構文を図17-15に示します。
exprの評価結果はデータベースの特定の行と列です。これは、スカラー値、ユーザー定義のデータ型インスタンス、またはXMLTypeインスタンスのいずれかです。
exprの評価結果がスカラー値の場合、この関数は、スカラー値を含むXML要素を戻します。
exprの評価結果がユーザー定義のデータ型インスタンスの場合、ユーザー定義の型属性をXML要素にマップします。
exprの評価結果がXMLTypeインスタンスの場合、この関数は、デフォルトのタグ名がROWであるXML要素で文書を囲みます。
デフォルトでは、XML文書の要素はexprと一致します。たとえば、exprが列名に変換される場合、XMLの囲み要素は同じ列名になります。XML文書を別の方法でフォーマットする場合はfmtを指定します。これは、XMLFormatオブジェクトのインスタンスです。
問合せ内でWHEREをsys_XMLGenとともに使用して<ROW/>タグを出力しないようにすると、NULL値が表示されません。
SELECT sys_XMLGen(x) FROM table_name WHERE x IS NOT NULL;
例17-34 SYS_XMLGEN: データベース列からのXML要素の生成
次の例では、サンプルのスキーマ表hr.employeesから、従業員（employee_id値が110）のfirst_nameを取得し、FIRST_NAME 要素を持つXML文書を含むXMLTypeインスタンスを生成します。
SELECT sys_XMLGen(first_name).getStringVal() FROM employees WHERE employee_id = 110; SYS_XMLGEN(FIRST_NAME).GETSTRINGVAL() ---------------------------------------- <?xml version="1.0"?> <FIRST_NAME>John</FIRST_NAME> 1 row selected.
SYS_XMLGENを使用する利点
SQL関数sys_XMLGenには次の利点があります。
SQL問合せ内でXMLインスタンスを作成し、問い合せることができます。
オブジェクト・リレーショナル・インフラストラクチャを使用して、簡単なリレーショナル表から複雑なネストしたXMLインスタンスを作成できます。たとえば、オブジェクト型でsys_XMLGenを使用した場合のXMLTypeビューを使用すると、Oracle XML DBでは、可能なかぎりこれらの問合せがリライトされます。第7章「XPathリライト」も参照してください。
sys_XMLGenは、ユーザー定義のデータ型インスタンス、スカラー値、またはXMLTypeインスタンスからXML文書を作成します。戻り値はXMLTypeインスタンスです。
sys_XMLGenは、結果をカスタマイズするのに使用するオプションのXMLFormatオブジェクトも引数として受け入れます。書式設定オブジェクトがNULLの場合は、デフォルトのマッピング動作が使用されます。
XMLFormatを使用して、SQL関数sys_XMLGenおよびsys_XMLAggに対してフォーマット引数を指定できます。
関数sys_XMLGenはXML文書を含むXMLTypeインスタンスを戻します。Oracle Databaseには、sys_XMLGenの出力をフォーマットするXMLFormatオブジェクトが用意されています。
表17-2に、オブジェクトXMLFormatの属性をリストします。
表17-2 XMLFormatオブジェクトの属性
| 属性 | データ型 | 用途 |
|---|---|---|
|
|
|
|
|
|
|
出力ドキュメントに対するスキーマ生成の型です。有効な値は、 |
|
|
|
|
|
|
|
スキーマ（ |
|
|
|
|
|
|
|
ユーザー指定の処理命令です。関数出力の先頭の要素の前に追加されるユーザーの処理命令です。 |
createFormatメソッドを使用すると、XMLFormatオブジェクトを実装できます。オブジェクトXMLFormatのメソッドcreateFormatは、囲み要素名、XMLスキーマ型、およびXMLスキーマ名を引数に取れます。他のXMLFormat属性にはデフォルト値が用意されています。
|
関連項目:
|
例17-35 SYS_XMLGEN: スカラー値のXML要素コンテンツへの変換
SQL関数sys_XMLGenは、スカラー値を、そのスカラー値を含む要素に変換します。たとえば次の問合せは、employee_id値を、その値を含む要素として含むXML文書を戻します。
SELECT sys_XMLGen(employee_id) FROM hr.employees WHERE ROWNUM < 2; SYS_XMLGEN(EMPLOYEE_ID) --------------------------- <?xml version="1.0"?> <EMPLOYEE_ID>100</EMPLOYEE_ID> 1 row selected.
囲み要素名（この例ではEMPLOYEE_ID）はsys_XMLGenに渡された列名から導出されます。問合せ結果は、完全なXML文書に対応するXMLTypeインスタンスを含む単独の行です。
例17-36 SYS_XMLGEN: デフォルトの要素名ROW
例17-35で、列名EMPLOYEE_IDはXML要素名のデフォルトとして使用されています。列名を直接導出できない場合、デフォルト名ROWがかわりに使用されます。
SELECT sys_XMLGen(employee_id*2) FROM hr.employees WHERE ROWNUM < 2; SYS_XMLGEN(EMPLOYEE_ID*2) ------------------------- <?xml version="1.0"?> <ROW>200</ROW> 1 row selected.
この例では、sys_XMLGenの引数が単純な列名ではないため、出力要素タグの名前をそのまま列名にできません。このためデフォルトの要素名ROWが使用されます。
デフォルトのROWタグは、XMLFormatオブジェクトを演算子の2番目のsys_XMLGen引数に指定することによって変更できます。例17-37を参照してください。
例17-37 デフォルトの要素名のオーバーライド: SYS_XMLGENとXMLFormatの併用
この例では、フォーマット引数がsys_XMLGenに対して指定され、要素の名前を明示的に指定しています。
SELECT sys_XMLGen(employee_id*2,
XMLFormat.createformat('DOUBLE_ID')).getclobval()
FROM hr.employees WHERE ROWNUM < 2;
SYS_XMLGEN(EMPLOYEE_ID*2,XMLFORMAT.CREATEFORMAT('EMPLOYEE_ID')).GETCLOBVAL()
----------------------------------------------------------------------------
<?xml version="1.0"?>
<DOUBLE_ID>200</DOUBLE_ID>
1 row selected.
例17-38 SYS_XMLGEN: ユーザー定義のデータ型インスタンスのXMLへの変換
ユーザー定義のデータ型インスタンスをsys_XMLGenに引数として指定すると、インスタンスはXML文書に正規マッピングされます。このマッピングでは、ユーザー定義のデータ型の属性はXML要素にマップされます。
アットマーク「@」で始まる名前のデータ型属性は、その前にあるXML要素の属性にマップされます。ユーザー定義のデータ型インスタンスを使用して、結果のXML文書内でネストを取得できます。
従業員と部門の例の階層XMLは、次のようにして生成できます（「DBMS_XMLGENを使用したXMLの生成」を参照）。
CREATE OR REPLACE TYPE hr.emp_t AS OBJECT(empno NUMBER(6),
ename VARCHAR2(25),
job VARCHAR2(10),
mgr NUMBER(6),
hiredate DATE,
sal NUMBER(8,2),
comm NUMBER(2,2));
/
Type created.
CREATE OR REPLACE TYPE hr.emplist_t AS TABLE OF emp_t;
/
Type created.
CREATE OR REPLACE TYPE hr.dept_t AS OBJECT(deptno NUMBER(4),
dname VARCHAR2(30),
loc VARCHAR2(4),
emplist emplist_t);
/
Type created.
SELECT sys_XMLGen(
dept_t(department_id,
department_name,
d.location_id,
CAST(MULTISET(SELECT emp_t(e.employee_id, e.last_name, e.job_id,
e.manager_id, e.hire_date, e.salary,
e.commission_pct)
FROM hr.employees e
WHERE e.department_id = d.department_id)
AS emplist_t))).getCLOBVal()
AS deptxml
FROM hr.departments d WHERE department_id = 10 OR department_id = 20;
SQL関数MULTISETは、その部門の従業員のサブセット結果を、リストおよびCASTとして扱い、それを適切なコレクション型に割り当てます。部門の型（dept_t）要素はその周囲を囲まれ、オブジェクト・インスタンスのXMLデータを作成します。
結果は次のようになります。関数が入力オペランドの名前を直接推論できないため、デフォルト名ROWが存在します。
DEPTXML ------------------------------------- <?xml version="1.0"?> <ROW> <DEPTNO>10</DEPTNO> <DNAME>Administration</DNAME> <LOC>1700</LOC> <EMPLIST> <EMP_T> <EMPNO>200</EMPNO> <ENAME>Whalen</ENAME> <JOB>AD_ASST</JOB> <MGR>101</MGR> <HIREDATE>17-SEP-87</HIREDATE> <SAL>4400</SAL> </EMP_T> </EMPLIST> </ROW> <?xml version="1.0"?> <ROW> <DEPTNO>20</DEPTNO> <DNAME>Marketing</DNAME> <LOC>1800</LOC> <EMPLIST> <EMP_T> <EMPNO>201</EMPNO> <ENAME>Hartstein</ENAME> <JOB>MK_MAN</JOB> <MGR>100</MGR> <HIREDATE>17-FEB-96</HIREDATE> <SAL>13000</SAL> </EMP_T> <EMP_T> <EMPNO>202</EMPNO> <ENAME>Fay</ENAME> <JOB>MK_REP</JOB> <MGR>201</MGR> <HIREDATE>17-AUG-97</HIREDATE> <SAL>6000</SAL> </EMP_T> </EMPLIST> </ROW> 2 rows selected.
|
注意: 前の例から、SQL関数sys_XMLGenとPL/SQLパッケージDBMS_XMLGENの違いは明確です。関数sys_XMLGen はSQL問合せの内部で動作し、行内の式および列を操作します。パッケージDBMS_XMLGENは、結果セット全体で動作します。 |
例17-39 SYS_XMLGEN: XMLTypeインスタンスの変換
XML文書をsys_XMLGenに渡す場合、この関数は、タグ名がデフォルトのROWである要素、またはXMLFormat書式設定オブジェクトを介して渡される名前で、その文書（またはフラグメント）を囲みます。この機能を使用して、XMLフラグメントを整形式の文書に変換できます。次のXMLデータについて考えてみます。
CREATE TABLE po_xml_tab(podoc XMLType);
Table created.
INSERT INTO po_xml_tab VALUES(XMLType('<DOCUMENT>
<EMPLOYEE>
<ENAME>John</ENAME>
<EMPNO>200</EMPNO>
</EMPLOYEE>
<EMPLOYEE>
<ENAME>Jack</ENAME>
<EMPNO>400</EMPNO>
</EMPLOYEE>
<EMPLOYEE>
<ENAME>Joseph</ENAME>
<EMPNO>300</EMPNO>
</EMPLOYEE>
</DOCUMENT>'));
1 row created.
COMMIT;
この問合せはENAME要素を抽出します。
SELECT e.podoc.extract('/DOCUMENT/EMPLOYEE/ENAME') FROM po_xml_tab e;
問合せの結果はXML文書フラグメントです。
<ENAME>John</ENAME> <ENAME>Jack</ENAME> <ENAME>Joseph</ENAME>
このフラグメントを有効なXML文書にするには、次のように、sys_XMLGenをコールして、フラグメントをルート要素で囲みます。
SELECT sys_XMLGen(e.podoc.extract('/DOCUMENT/EMPLOYEE/ENAME')).getCLOBVal() FROM po_xml_tab e;
これによって、次のように、フラグメントの前後に要素ROWが置かれます。
<?xml version="1.0"?> <ROW> <ENAME>John</ENAME> <ENAME>Jack</ENAME> <ENAME>Joseph</ENAME> </ROW>
|
注意: sys_XMLGenへの入力が列の場合、その列名がデフォルトの要素名として使用されます。要素名は、XMLFormat書式設定オブジェクトを2番目の引数としてsys_XMLGenに渡すことで変更できます。「XMLFormatオブジェクト型の使用」を参照してください。 |
例17-40 オブジェクト・ビューでのSYS_XMLGENの使用
ここで定義されていないエンティティは、例17-28を参照してください。
-- Create purchase order object type
CREATE OR REPLACE TYPE po_typ AUTHID CURRENT_USER
AS OBJECT(pono NUMBER,
customer customer_typ,
orderdate DATE,
shipdate TIMESTAMP,
lineitems_ntab lineitems_ntabtyp,
shiptoaddr address_typ)
/
--Purchase order view
CREATE OR REPLACE VIEW po OF po_typ
WITH OBJECT IDENTIFIER (PONO)
AS SELECT p.pono, customer_typ(p.custno, c.custname, c.address, c.phonelist),
p.orderdate, p.shipdate,
CAST(MULTISET(
SELECT
lineitem_typ(l.lineitemno,
stockitem_typ(l.stockno, s.price, s.taxrate),
l.quantity, l.discount)
FROM lineitems_tab l, stock_tab s
WHERE l.pono = p.pono AND s.stockno=l.stockno)
AS lineitems_ntabtyp),
address_typ(p.tostreet, p.tocity, p.tostate, p.tozip)
FROM po_tab p, customer c
WHERE p.custno=c.custno;
-- Use sys_XMLGen to generate PO in XML format
SELECT sys_XMLGen(OBJECT_VALUE,
XMLFormat.createFormat('PurchaseOrder')).getCLOBVal() PO
FROM po p
WHERE p.pono=1001;
この問合せは発注書をXML形式で戻します。
PO
----------------------------------------------
<?xml version="1.0"?>
<PurchaseOrder>
<PONO>1001</PONO>
<CUSTOMER>
<CUSTNO>1</CUSTNO>
<CUSTNAME>Jean Nance</CUSTNAME>
<ADDRESS>
<STREET>2 Avocet Drive</STREET>
<CITY>Redwood Shores</CITY>
<STATE>CA</STATE>
<ZIP>95054</ZIP>
</ADDRESS>
<PHONELIST>
<VARCHAR2>415-555-1212</VARCHAR2>
</PHONELIST>
</CUSTOMER>
<ORDERDATE>10-APR-97</ORDERDATE>
<SHIPDATE>10-MAY-97 12.00.00.000000 AM</SHIPDATE>
<LINEITEMS_NTAB>
<LINEITEM_TYP LineItemNo="1">
<ITEM StockNo="1534">
<PRICE>2234</PRICE>
<TAXRATE>2</TAXRATE>
</ITEM>
<QUANTITY>12</QUANTITY>
<DISCOUNT>0</DISCOUNT>
</LINEITEM_TYP>
<LINEITEM_TYP LineItemNo="2">
<ITEM StockNo="1535">
<PRICE>3456.23</PRICE>
<TAXRATE>2</TAXRATE>
</ITEM>
<QUANTITY>10</QUANTITY>
<DISCOUNT>10</DISCOUNT>
</LINEITEM_TYP>
</LINEITEMS_NTAB>
<SHIPTOADDR/>
</PurchaseOrder>
1 row selected.
SQL関数sys_XMLAggは、式により表されるすべてのXML文書またはフラグメントを集計し、単一のXML文書を生成します。この関数は、デフォルト名ROWSETの新しい囲み要素を追加します。XML文書を別の方法でフォーマットするには、fmtパラメータを使用します。
|
関連項目: 『Oracle Database SQL言語リファレンス』 |
Oracle9iデータベースで、XMLベースのデータベース・コンテンツの格納および問合せに使用するXMLTypeが導入されました。これらのデータベースのXML機能を使用して、<xsql:include-xml>アクション要素を使用することによってXSQLページに挿入するXMLを生成できます。
<xsql:include-xml>要素内のSELECT文によって、1列を含む1行が戻されます。列はCLOBインスタンスとVARCHAR2値のいずれかです。整形式のXML文書が含まれています。XML文書は解析され、XSQLページに挿入されます。
|
関連項目: 要素<xsql:include-xml>およびXSQLページの詳細は、『Oracle XML Developer's Kitプログラマーズ・ガイド』を参照してください。 |
例17-41 XSQLサーブレット<xsql:include-xml>とネストされたXMLAgg関数の使用
次の例では、関数XMLAggへのネストされたコールを使用して、部門および従業員を含む、動的に構築されたXML文書の結果を、DepartmentList要素で囲んだ単一のXML結果文書に集計します。メソッドgetCLOBValへのコールは、XMLTypeにかわってXSQLサーブレットに対してCLOB値を提供します。XSQLサーブレットが結果を表示するためには、XSQLコマンドライン・ユーティリティ、WebサーバーにインストールされたXSQLサーブレット、Java Server Pages（JSP）またはJava XSQLRequestオブジェクトといった、特別の環境が必要です。
<xsql:include-xml connection="orcl92" xmlns:xsql="urn:oracle-xsql">
SELECT
XMLElement("DepartmentList",
XMLAgg(XMLElement(
"Department",
XMLAttributes(department_id as "Id"),
XMLForest(department_name as "Name"),
(SELECT XMLElement("Employees",
XMLAgg(XMLElement(
"Employee",
XMLAttributes(
employee_id as "Id"),
XMLForest(
last_name as "Name",
salary as "Salary",
job_id as "Job"))))
FROM employees e
WHERE e.department_id=d.department_id)))).getCLOBVal()
FROM departments d
ORDER BY department_name
</xsql:include-xml>
この問合せにより生成される結果は次のとおりです。
XMLELEMENT("DEPARTMENTLIST",XMLAGG(XMLELEMENT("DEPARTMENT",XMLATTRIBUTES(DEPARTM
--------------------------------------------------------------------------------
<DepartmentList><Department Id="10"><Name>Administration</Name><Employees><Emplo
yee Id="200"><Name>Whalen</Name><Salary>4400</Salary><Job>AD_ASST</Job></Employe
e></Employees></Department><Department Id="20"><Name>Marketing</Name><Employees>
<Employee Id="201"><Name>Hartstein</Name><Salary>13000</Salary><Job>MK_MAN</Job>
</Employee><Employee Id="202"><Name>Fay</Name><Salary>6000</Salary><Job>MK_REP</
Job></Employee></Employees></Department>
...
</DepartmentList>
1 row selected.
例17-42 XSQLサーブレット<xsql:include-xml>とXMLElementおよびXMLAggの使用
データベースがXMLフラグメントを1つの結果文書に集計する方が効率的です。要素<xsql:include-xml>は、指定した問合せの最初の行のみを取得するのでこの方法に便利です。
Oracle Databaseの組込みXPath問合せ機能を使用すると、映画『大いなる幻影』の発注書すべての集計リストを抽出できます。この例は、サンプル・スキーマOEのpurchaseorder表を使用します。
CONNECT oe
Enter password: password
Connected.
SELECT
XMLElement(
"GrandIllusionOrders",
XMLAgg(extract(OBJECT_VALUE,
'/PurchaseOrder/LineItems/*[Part[@Id="37429121924"]]')))
FROM purchaseorder;
これによって、次の結果が戻されます。
XMLELEMENT("GRANDILLUSIONORDERS",XMLAGG(EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LIN
--------------------------------------------------------------------------------
<GrandIllusionOrders>
<LineItem ItemNumber="14">
<Description>Grand Illusion</Description>
<Part Id="37429121924" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="14">
<Description>Grand Illusion</Description>
<Part Id="37429121924" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="6">
<Description>Grand Illusion</Description>
<Part Id="37429121924" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="19">
<Description>Grand Illusion</Description>
<Part Id="37429121924" UnitPrice="39.95" Quantity="4"/>
</LineItem>
<LineItem ItemNumber="21">
<Description>Grand Illusion</Description>
<Part Id="37429121924" UnitPrice="39.95" Quantity="3"/>
</LineItem>
<LineItem ItemNumber="15">
<Description>Grand Illusion</Description>
<Part Id="37429121924" UnitPrice="39.95" Quantity="3"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>Grand Illusion</Description>
<Part Id="37429121924" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="8">
<Description>Grand Illusion</Description>
<Part Id="37429121924" UnitPrice="39.95" Quantity="1"/>
</LineItem>
<LineItem ItemNumber="17">
<Description>Grand Illusion</Description>
<Part Id="37429121924" UnitPrice="39.95" Quantity="4"/>
</LineItem>
</GrandIllusionOrders>
1 row selected.
このXMLType問合せ結果をXSQLページに含めるには、問合せを<xsql:include-xml>要素の内部に貼り付け、メソッドgetCLOBValをコールすることにより、結果がXMLTypeインスタンスでなくCLOB値としてクライアントに戻されるようにします。
<xsql:include-xml connection="orcl92" xmlns:xsql="urn:oracle-xsql"> SELECT XMLElement( "GrandIllusionOrders", XMLAgg( extract( OBJECT_VALUE, '/PurchaseOrder/LineItems/*[Part[@Id="37429121924"]]'))).getCLOBval() FROM purchaseorder; </xsql:include-xml>
ここではSQL関数XMLElementおよびXMLAggが組み合せて使用され、問合せによって識別されたすべてのXMLフラグメントを単一の整形式XML文書に集計しています。失敗した場合、XSQLページ・プロセッサによって、次のようなCLOB値が解析されます。
<LineItem>...</LineItem> <LineItem>...</LineItem> ...
XML 1.0勧告で必要な単一のルート要素が存在しないため、このXML文書は整形式ではありません。関数XMLElementおよびXMLAggは連携し、単一のルート要素GrandIllusionOrdersに整形式の結果を生成します。この整形式のXML文書は解析され、XSQLページに挿入されます。
|
関連項目: 『Oracle XML Developer's Kitプログラマーズ・ガイド』のXSQLページ・パブリッシング・フレームワークに関する章を参照してください。 |
XSLTおよびXSQLの使用
XSQLページを使用すると、XSLTをデータベース、中間層またはクライアントのいずれで実行するかを制御できます。データベースで実行する場合は、問合せでSQL関数XMLtransform（または同等のもの）を使用します。中間層で実行する場合は、テンプレート・ページの先頭に<?xml-stylesheet?>を追加します。クライアントで実行する場合は、client="yes"属性をPI <?xml-stylesheet?>に追加します。XSQLページを使用すると、たとえば使用されているブラウザの種類などに応じて、スタイルシート処理を条件付きでクライアントにオフロードするページも作成できます。
XSQLは、パフォーマンスおよびスループットを向上させるために（データベース接続に加えて）中間層にXSLTスタイルシートをキャッシュおよびプールします。アプリケーションによっては、Web Cacheや他のテクニックを使用した変換の回避、および同じ（静的な）データを何度も変換しないようにする最適化により、パフォーマンスをさらに向上させることができます。XSQLページには静的XMLと動的に生成したXMLを混在させることができます。これにより、ページの動的部分の作成のみにデータベースを使用することができます。
Oracle Databaseでは、Oracle XML SQL Utility（XSU）を使用してXMLを生成できます。中間層またはクライアントでXMLを生成する場合にXSU lを使用できます。また、XSUはXMLType列を含む表でのXMLの生成をサポートしています。
|
関連項目: XSUの詳細は、『Oracle XML Developer's Kitプログラマーズ・ガイド』を参照してください。 |
この項では、Oracle XML DBを使用してXMLを生成する場合の追加のガイドラインを示します。
集計前にXMLAgg ORDER BY句を使用するには、最初のXMLAGG引数の後にORDER BY句を指定します。
例17-43 XMLAGG ORDER BY句の使用
次の表を検討します。
CREATE TABLE dev_tab (dev NUMBER,
dev_total NUMBER,
devname VARCHAR2(20));
Table created.
INSERT INTO dev_tab VALUES (16, 5, 'Alexis');
1 row created.
INSERT INTO dev_tab VALUES (2, 14, 'Han');
1 row created.
INSERT INTO dev_tab VALUES (1, 2, 'Jess');
1 row created.
INSERT INTO dev_tab VALUES (9, 88, 'Kurt');
1 row created.
COMMIT;
この例では、結果がdev列の順に従って集計されます。（ここではわかりやすいように、この結果をフォーマット出力しています。）
SELECT XMLAgg(XMLElement("Dev",
XMLAttributes(dev AS "id", dev_total AS "total"),
devname)
ORDER BY dev)
FROM tab1 dev_total;
XMLAGG(XMLELEMENT("DEV",XMLATTRIBUTES(DEVAS"ID",DEV_TOTALAS"TOTAL"),DEVNAME)ORDE
--------------------------------------------------------------------------------
<Dev id="1" total="2">Jess</Dev>
<Dev id="2" total="14">Han</Dev>
<Dev id="9" total="88">Kurt</Dev>
<Dev id="16" total="5">Alexis</Dev>
1 row selected.
標準SQL/XML関数XMLTableを使用し、例17-44のように、複数の行として抽出された文書の関連する部分の行セットを戻します。
例17-44 XMLTABLEを使用して行セットを戻す方法
この例は、サンプル・スキーマOEのpurchaseorder表を使用します。
CONNECT oe
Enter password: password
Connected.
SELECT item.descr, item.partid
FROM purchaseorder,
XMLTable('/PurchaseOrder/LineItems/LineItem' PASSING OBJECT_VALUE
COLUMNS descr VARCHAR2(256) PATH 'Description',
partid VARCHAR2(14) PATH 'Part/@Id') item
WHERE item.partid = '715515012027'
OR item.partid = '715515011921'
ORDER BY partid;
この例では、説明とPart IDだけを含む行セットが、Part IDの順序で戻されます。
DESCR -------------- PARTID -------------- My Man Godfrey 715515011921 My Man Godfrey 715515011921 My Man Godfrey 715515011921 My Man Godfrey 715515011921 My Man Godfrey 715515011921 My Man Godfrey 715515011921 My Man Godfrey 715515011921 Mona Lisa 715515012027 Mona Lisa 715515012027 Mona Lisa 715515012027 Mona Lisa 715515012027 Mona Lisa 715515012027 Mona Lisa 715515012027 Mona Lisa 715515012027 Mona Lisa 715515012027 Mona Lisa 715515012027 16 rows selected.
脚注の凡例
脚注1: SQL/XML標準では、引数data-typeが存在することが必須ですが、Oracle XML DBでのこの標準の実装では、便宜性を考慮してオプションとなっています。