この章では、データベースのリレーショナル・データから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でのこの標準の実装では、便宜性を考慮してオプションとなっています。