8 リレーショナル・データからのXMLデータの生成
Oracle XML DBには、データベースのリレーショナル・データからXMLデータを生成(構成)するための機能が用意されています。リレーショナル・コンテンツからXMLデータを生成するためのSQL/XML標準関数およびOracle固有の関数とパッケージがあります。
- XMLデータの生成の概要
XMLデータは、Oracle XML DBで、標準SQL/XML関数、Oracle固有のSQL関数、パッケージDBMS_XMLGEN
のPL/SQLサブプログラムまたはDBURIType
を使用して生成できます。 - SQL関数を使用したXMLデータの生成
Oracle XML DBには、XMLデータの構成に使用できるSQL関数が用意されています。これらの多くは、SQL/XML標準に属しています。 - DBMS_XMLGENを使用したXMLデータの生成
PL/SQLパッケージDBMS_XMLGEN
は、SQL問合せの結果からXML文書を作成します。XML文書はCLOB
またはXMLType
値として取得されます。 - SYS_XMLAGG Oracle SQL関数
Oracle SQL関数sys_XMLAgg
は、式により表されるすべてのXML文書またはフラグメントを集計し、単一のXML文書を生成します。この関数は、デフォルト名ROWSET
の新しい要素の式の結果を囲みます。 - XMLAGG ORDER BY句を使用して、問合せ結果を集計前に順序付けする方法
集計前にXMLAgg ORDER BY
句を使用するには、最初のXMLAGG
引数の後にORDER BY
句を指定します。 - XMLTABLEを使用して行セットを戻す方法
標準SQL/XML関数XMLTable
を使用すると、複数の行として抽出された文書の関連する部分の行セットを戻すことができます。
関連項目:
SQL/XML関数XMLQuery
およびXMLTable
を使用したXMLデータの構成の詳細は、XQueryとOracle XML DBを参照してください。
親トピック: XMLデータに対するリレーショナル・データ
8.1 XMLデータの生成の概要
XMLデータは、Oracle XML DBで、標準SQL/XML関数、Oracle固有のSQL関数、パッケージDBMS_XMLGEN
のPL/SQLサブプログラムまたはDBURIType
を使用して生成できます。
-
標準SQL/XML関数を使用します。SQL関数を使用したXMLデータの生成を参照してください。
-
Oracle SQL関数を使用します。次の項を参照してください。
-
SYS_XMLAGG Oracle SQL関数この関数は、行のグループを操作し、複数のXML文書を1つに集約します。
-
PL/SQLパッケージ
DBMS_XMLGEN
の使用。DBMS_XMLGENを使用したXMLデータの生成を参照してください。 -
DBURIType
インスタンスを使用してデータベース・データからXML文書を構成します。URIを使用したデータ・アクセスを参照してください。
8.2 SQL関数を使用したXMLデータの生成
Oracle XML DBには、XMLデータの構成に使用できるSQL関数が用意されています。これらの多くは、SQL/XML標準に属しています。
標準XML生成関数は、SQL/XMLパブリッシング関数または生成関数とも呼ばれます。
SQL/XML関数XMLQuery
の用途は、XMLデータの生成(パブリッシング)に限定されません。関数XMLQuery
は非常に汎用性が高く、このマニュアルではSQL/XML問合せおよび更新関数と呼んでいます。
XMLを生成する次のSQL関数は、(SQL/XML標準の一部ではなく) Oracle固有です。
-
SYS_XMLAGG Oracle SQL関数この関数は、リレーショナル行のグループを操作し、複数のXML文書を1つに集約します。
すべてのXML生成SQL関数では、スカラーおよびユーザーのデータ型インスタンスが正規のXML形式に変換されます。この正規マッピングでは、ユーザー定義のデータ型の属性はXML要素にマップされます。
- XMLELEMENTおよびXMLATTRIBUTES SQL/XML関数
SQL/XML標準関数XMLElement
は、リレーショナル・データからXML要素を構成します。SQL/XML標準関数XMLAttributes
をXMLElement
とともに使用すると、生成される要素の属性を指定できます。 - XMLFOREST SQL/XML関数
SQL/XML標準関数XMLForest
を使用して、XML要素のフォレストを構成します。 - XMLCONCAT SQL/XML関数
SQL/XML標準関数XMLConcat
を使用して、複数のXMLType
インスタンスを連結することにより、XMLフラグメントを構成します。 - XMLAGG SQL/XML関数
SQL/XML標準関数XMLAgg
を使用して、XML要素のコレクションからXML要素のフォレストを構成します。これは集計関数です。 - XMLPI SQL/XML関数
SQL/XML標準関数XMLPI
を使用して、XML処理命令(PI)を構成します。 - XMLCOMMENT SQL/XML関数
SQL/XML標準関数XMLComment
を使用して、XMLコメントを構成します。 - XMLSERIALIZE SQL/XML関数
SQL/XML標準関数XMLSerialize
は、文字列またはXMLデータのLOB表現を取得するために使用します。 - XMLPARSE SQL/XML関数
SQL/XML標準関数XMLParse
は、XMLデータを含む文字列を解析してXMLType
インスタンスの対応する値を構成するために使用します。 - XMLCOLATTVAL Oracle SQL関数
Oracle SQL関数XMLColAttVal
は、渡された引数の値を含むXMLのcolumn
要素のフォレストを生成します。この関数は、SQL/XML ANSI-ISO標準関数に対応するためのOracleの拡張です。 - XMLCDATA Oracle SQL関数
Oracle SQL関数XMLCDATA
を使用して、XMLCDATA
セクションを生成します。
関連項目:
-
SQL/XML関数
XMLQuery
を使用したXMLデータの構成の詳細は、XQueryとOracle XML DBを参照してください。 -
OracleによるSQL/XML標準のサポートの詳細は、Oracle Database SQL言語リファレンスを参照してください。
親トピック: リレーショナル・データからのXMLデータの生成
8.2.1 XMLELEMENTおよびXMLATTRIBUTES SQL/XML関数
SQL/XML標準関数XMLElement
は、リレーショナル・データからXML要素を構成します。SQL/XML標準関数XMLAttributes
をXMLElement
とともに使用すると、生成される要素の属性を指定できます。
SQL/XML標準関数XMLElement
は、引数としてXML要素名、その要素の属性のコレクション(オプション)、およびその要素のコンテンツを構成する0 (ゼロ)個以上の追加の引数を取ります。戻り値はXMLType
インスタンスです。
キーワードENTITYESCAPING
およびNOENTITYESCAPING
の詳細は、生成されたXMLデータにおける文字のエスケープを参照してください。これらのキーワードは、標準SQL/XML関数XMLElement
およびXMLAttributes
に対応するためのOracleの拡張です。
関数XMLElement
の最初の引数は、作成されるrootXML要素名に名前を付ける識別子を定義します。ルート要素識別子の引数は、リテラル識別子(図8-1のidentifier
)を使用することで、またはEVALNAME
に識別子を評価する式(value_expr
)を続けることで定義できます。ただし、識別子は、定義されてもNULL
にできません。NULLの場合、エラーが発生します。EVALNAME
の使用の可能性は、標準SQL/XML関数XMLElement
に対応するためのOracleの拡張です。
関数XMLElement
のオプションのXML-attributes-clause
引数は、生成されるルート要素の属性を指定します。この関数の構文を図8-2に示します。
オプションのXML-attributes-clause
引数の他に、関数XMLElement
は、ルート要素のコンテンツ(子要素とテキスト・コンテンツ)を構成する0 (ゼロ)個以上のvalue_expr
引数を取ります。XML-attributes-clause
引数も存在する場合、XML-attributes-clause
引数の後にこれらのコンテンツ引数が続く必要があります。個々のコンテンツ引数式が評価され、結果がXMLフォーマットに変換されます。値引数の評価結果がNULL
だった場合、その引数にはコンテンツが作成されません。
注意:
別名(c_alias
)の前のAS
は、SQL/XML標準で必要ですが、Oracleではオプションです。
オプションのXML-attributes-clause
引数は、SQL/XML標準関数XMLAttributes
を使用してルート要素の属性を指定します。関数XMLAttributes
は、関数XMLElementのコールでのみ
使用できます。単独では使用できません。
キーワードENTITYESCAPING
およびNOENTITYESCAPING
の詳細は、生成されたXMLデータにおける文字のエスケープを参照してください。これらのキーワードは、標準SQL/XML関数XMLElement
およびXMLAttributes
に対応するためのOracleの拡張です。
キーワードSCHEMACHECK
およびNOSCHEMACHECK
は、生成された属性のいずれかがOracle XML DBで登録されているXML Schemaに対応するスキーマの場所を指定するか確認し、指定している場合は、それに対応するXML Schemaに基づくXMLデータを生成するか確認するため、それら属性で実行時チェックが行われるかどうか判断します。NOSCHEMACHECK
で提供されているデフォルト動作では、チェックは行いません。12cリリース1 (12.1)より前のリリースでは、デフォルト動作ではチェックを行います。SCHEMACHECK
キーワードを使用して、下位互換性を得ることができます。
NOSCHEMACHECK
の有無にかかわらず、コンパイル時に同様のチェックが必ず行われます。つまり、特にXML Schemaの場所属性値の指定に文字列リテラルを使用している場合、(コンパイル時)チェックが行われ、適切な場合、XML Schemaに基づくデータがそれに伴って生成されます。
キーワードSCHEMACHECK
およびNOSCHEMACHECK
は、標準SQL/XML関数XMLAttributes
に対応するためのOracleの拡張です。
注意:
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
に文字列リテラルを評価する式が続く名前になります。
注意:
標準SQL/XML構文に対するOracleの拡張は次のとおりです。
-
EVALNAME
を使用できること。 -
別名(
c_alias
)の前のAS
はオプションであること。
属性値式がNULL
であると評価された場合、その式に対応する属性は作成されません。属性値式のデータ型は、オブジェクト型またはコレクションにできません。
- 生成されたXMLデータにおける文字のエスケープ
SQL/XML標準で規定されているとおり、明示された識別子に含まれる文字はいかなる場合もエスケープされません。必ず、有効なXML名を使用してください。これはすべてのSQL/XML関数に当てはまります。 - XMLの日付とタイムスタンプのフォーマット
XMLデータの日付とタイムスタンプは標準フォーマットで表記するようにXMLスキーマ標準で規定されています。Oracle XML DBにおけるXML生成関数では、この標準に従ってXMLの日付とタイムスタンプが生成されます。 - XMLElementの例
ここでの例は、SQL/XML関数XMLElement
の使用方法を示しています。
親トピック: SQL関数を使用したXMLデータの生成
8.2.1.1 生成されたXMLデータにおける文字のエスケープ
SQL/XML標準で規定されているとおり、明示された識別子に含まれる文字はいかなる場合もエスケープされません。必ず、有効なXML名を使用してください。これはすべてのSQL/XML関数に当てはまります。
特に、XMLElement
のルート要素識別子(図8-1のidentifier
)やXMLAttributes
のAS
句で名前を指定された属性識別子の別名に当てはまります(図8-2を参照)。
ただし、生成される他のXMLデータはデフォルトではエスケープされるため、有効なNameChar
文字のみが生成されます。SQL識別子から有効なXML要素や属性名を生成する際、XML名として許容されない文字は、元の文字の16進数Unicode表現の前後にアンダースコア(_
)を付けたものに置換されます。たとえば、コロン(:
)は_003A_
に置換されてエスケープされます。003Aは16進数Unicode表現です。
エスケープは、XMLElement
やXMLAttributes
など、すべてのSQL/XML関数に対する、評価されたvalue_expr
引数に含まれる文字に適用されます。また、AS
句の前でないXMLAttributes
属性値式で暗黙的に定義されている属性識別子の文字にも適用されます。SQL列名のエスケープされた形が属性の名前として使用されます。
文字のエスケープが不要な場合もあります。たとえば、生成中のXMLデータが整形式であることがわかっている場合、エスケープをしないことで処理時間を節約できます。SQL/XML関数XMLElement
およびXMLAttributes
にNOENTITYESCAPING
キーワードを指定して処理時間を節約できます。ENTITYESCAPING
キーワードでエスケープを制限します。これはデフォルトの動作ではありません。キーワードNOENTITYESCAPING
およびENTITYESCAPING
は、標準SQL/XML関数XMLElement
およびXMLAttributes
に対応するためのOracleの拡張です。
8.2.1.2 XMLの日付とタイムスタンプのフォーマット
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
を使用します(例8-1を参照)。
関連項目:
XML Schema Part 2: Datatypes, D. ISO 8601 Date and Time Formats (XMLの日付およびタイムスタンプのフォーマットに関するXML Schemaの仕様)
8.2.1.3 XMLElementの例
ここでの例は、SQL/XML関数XMLElement
の使用方法を示しています。
例8-1では、XMLElement
を使用して、XML Schemaの標準フォーマットと異なるフォーマットを持つXMLの日付を生成します。
例8-2では、XMLElement
を使用して、従業員の名前をコンテンツとして持つEmp
要素を各従業員に1つずつ生成します。
例8-3では、XMLElement
を使用して、従業員名と雇用日を提供する子要素を持つEmp
要素を各従業員に1つずつ生成します。
例8-4では、XMLElement
を使用して、属性id
およびname
を持つEmp
要素を各従業員に1つずつ生成します。
生成されたXMLデータにおける文字のエスケープで説明するように、ルート要素名、およびAS
句により定義される属性の名前に含まれる文字はエスケープされません。識別指名に含まれる文字は、その名前が、評価された式から作成される場合にのみエスケープされます(列の参照と同様)。
例8-5は、XMLElement
を使用して構成されたXNLデータによって、ルート要素名と属性名がエスケープされないことを示します。大なり記号(>
)とカンマ(,
)はXML要素や属性名では許容されないため、無効なXMLが作成されます。
文字のエスケープに関する詳細説明は、SQL/XML標準に含まれています。
例8-6では、名前空間を使用してXML Schemaに基づく文書を作成する方法を示します。XML Schema http://www.oracle.com/Employee.xsd
が存在し、ターゲットの名前空間が存在しない場合、例8-6の問合せによって、そのスキーマに準拠したXMLType
インスタンスが作成されます。
例8-7では、XMLElement
を使用して、従業員と部門情報を含むXML文書を生成します。ここでは、サンプルのデータベース・スキーマ表hr.departments
を使用します。
例8-1 XMLELEMENT: 日付のフォーマット
-- With standard XML date format: SELECT XMLElement("Date", hire_date) FROM hr.employees WHERE employee_id = 203; XMLELEMENT("DATE",HIRE_DATE) ---------------------------- <Date>2002-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-02</Date> 1 row selected.
例8-2 XMLELEMENT: 各従業員の要素の生成
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/XMLL関数XMLElement
は、ネストすることにより、ネスト構造を持つXMLデータを生成することもできます。
例8-3 XMLELEMENT: ネストされたXMLの生成
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>2004-02-17</hiredate></Emp> <Emp><name>Pat Fay</name><hiredate>2005-08-17</hiredate></Emp> <Emp><name>Susan Mavris</name><hiredate>2002-06-07</hiredate></Emp> <Emp><name>Hermann Baer</name><hiredate>2002-06-07</hiredate></Emp> <Emp><name>Shelley Higgins</name><hiredate>2002-06-07</hiredate></Emp> <Emp><name>William Gietz</name><hiredate>2002-06-07</hiredate></Emp> 6 rows selected.
例8-4 XMLELEMENT: IDおよびname属性を持つ従業員要素の生成
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.
例8-5 XMLELEMENT: 生成された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.
例8-6 XMLELEMENTと名前空間を使用したXML Schemaに基づく文書の作成
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.
例8-7 XMLELEMENT: ユーザー定義のデータ型インスタンスからの要素の生成
CREATE OR REPLACE TYPE emp_t AS OBJECT ("@EMPNO" NUMBER(4), ENAME VARCHAR2(10)); CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t; CREATE OR REPLACE TYPE dept_t AS OBJECT ("@DEPTNO" NUMBER(2), DNAME VARCHAR2(14), EMP_LIST emplist_t); 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.
8.2.2 XMLFOREST SQL/XML関数
SQL/XML標準関数XMLForest
を使用して、XML要素のフォレストを構成します。
その引数は評価する式で、オプションで別名も付けられます。図8-3 は、XMLForest
の構文を説明するものです。
値の式(図8-3のvalue_expr
)がそれぞれXML形式に変換され、オプションで識別子c_alias
が属性識別子として使用されます(c_alias
は文字列リテラルまたはEVALNAME
に文字列リテラルを評価する式が続く名前になります)。EVALNAME
の使用の可能性は、標準SQL/XML関数XMLForest
に対応するためのOracleの拡張です。
オブジェクト型またはコレクションの場合、AS
句は必須です。それ以外の方の場合、AS
句はオプションです。指定された式でAS
句が省略されている場合は、評価された値の式に含まれる文字はエスケープされ、要素の囲みタグ名を形成します。エスケープの方式は生成されたXMLデータにおける文字のエスケープでの定義によります。値の式の評価結果がNULL
だった場合、その式には要素が作成されません。
例8-8では、XMLElement
とXMLForest
を使用して、name
属性を含み、雇用日および部門をコンテンツとして含む子要素を持つEmp
要素を各従業員に1つずつ生成します。
例8-8 XMLFOREST: 属性と子要素を持つ要素の生成
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>2004-02-17</HIRE_DATE> <department>20</department> </Emp> <Emp name="Pat Fay"> <HIRE_DATE>2005-08-17</HIRE_DATE> <department>20</department> </Emp> 2 rows selected.
例8-9 XMLFOREST: ユーザー定義のデータ型インスタンスからの要素の生成
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;
親トピック: SQL関数を使用したXMLデータの生成
8.2.3 XMLCONCAT SQL/XML関数
SQL/XML標準関数XMLConcat
を使用して、複数のXMLType
インスタンスを連結することにより、XMLフラグメントを構成します。
図8-4はXMLConcat
の構文を説明するものです。関数XMLConcat
には次の2つの形式があります。
-
1つ目の形式では、
XMLType
インスタンスのVARRAYであるXMLSequenceType
値を引数として取り、VARRAYのすべての要素を連結して単一のXMLType
インスタンスを戻します。この形式は、XMLType
インスタンスのリストを単一のインスタンスに縮小する場合に有効です。 -
2つ目の形式では、任意の数の
XMLType
インスタンスを取り、それらの値を連結します。値の1つがNULL
である場合、その値は結果に出力されません。すべての値がNULL
である場合、結果はNULL
になります。この形式は、任意の数のXMLType
インスタンスを同じ行に連結するために使用できます。関数XMLAgg
を使用すると、複数行にわたってXMLType
インスタンスを連結できます。
例8-10では、SQL/XML関数XMLConcat
を使用して、XMLSequenceType
値(XMLType
インスタンスのVALLAY)のXMLType
インスタンスを連結して戻します。
例8-10 XMLCONCAT: 順序で使用されるXMLTypeインスタンスの連結
SELECT XMLSerialize( CONTENT XMLConcat(XMLSequenceType( XMLType('<PartNo>1236</PartNo>'), XMLType('<PartName>Widget</PartName>'), XMLType('<PartPrice>29.99</PartPrice>'))) AS CLOB) AS "RESULT" FROM DUAL;
この問合せによりXMLフラグメントが戻されます。(ここではわかりやすいように、この結果をフォーマット出力しています。)
RESULT --------------- <PartNo>1236</PartNo> <PartName>Widget</PartName> <PartPrice>29.99</PartPrice> 1 row selected.
例8-11では、XMLConcat
を使用して、従業員の姓と名のXML要素を作成して連結します。
例8-11 XMLCONCAT: 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データの生成
8.2.4 XMLAGG SQL/XML関数
SQL/XML標準関数XMLAgg
を使用して、XML要素のコレクションからXML要素のフォレストを構成します。これは集計関数です。
図8-5は、XMLAgg
の構文を説明するものです。
order_by_clause
は、次のようになります。
ORDER BY [list of: expr [ASC|DESC] [NULLS {FIRST|LAST}]]
数値リテラルは、列の位置として解釈されません。たとえば、ORDER BY 1
は、最初の列での順序付けを意味しません。数値リテラルは他のリテラルと同様に解釈されます。
SQL/XML関数XMLConcat
と同様に、値がNULL
である引数は結果から削除されます。SQL/XML関数XMLAgg
はOracle SQL関数sys_XMLAgg
に類似していますが、XMLAgg
はノードのフォレストを戻し、XMLFormat
パラメータを取らない点で異なります。
SQL/XML関数XMLAgg
を使用すると、複数行
にわたってXMLTypeインスタンスを連結できます。また、オプションのORDER BY
句を使用して、集計するXML値を順序付けできます。関数XMLAgg
は各グループについて1つの集計XMLを生成します。問合せでGROUP BYが指定されていない場合、問合せのすべての行に対する結果が集計され、1つのXMLが戻されます。
例8-12では、SQL/XML関数XMLAgg
およびXMLElement
を使用して、従業員のジョブIDおよび姓を要素のコンテンツとして持つEmployee
要素を含むDepartment
要素を作成します。また、その部門に属するEmployee
要素を従業員の姓で順序付けします。また、その部門に属するEmployee要素を従業員の姓で順序付けします (わかりやすいように、この結果をフォーマット出力しています)。
例8-12 XMLAGG: 子の従業員要素を含む部門要素の生成
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によって行が集計されるため、単一の
従業員行が戻されます。
例8-13は、GROUP
BY
句を使用して、戻された一連の行を複数のグループにグループ化し、Department
要素を構成する方法を示しています。(ここではわかりやすいように、この結果をフォーマット出力しています。)
例8-13 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 Colmenares</Employee> <Employee>PU_CLERK Himuro</Employee> <Employee>PU_CLERK Tobias</Employee> <Employee>PU_CLERK Baida</Employee> <Employee>PU_CLERK Khoo</Employee></Department> <Department deptno="40"> <Employee>HR_REP Mavris</Employee> </Department> 2 rows selected.
XMLAgg
式でORDER BY
句を使用すると、各部門内で従業員を順序付けできます。
注意:
ORDER BY
句にかぎり、他のOracle Databaseでの使用方法とは異なり、数値リテラルは列の位置として解釈されません。
関数XMLAgg
を使用すると、表に存在するいくつかの階層関係を反映できます。例8-14では、部門30の部門要素を生成します。この要素内に、部門の各従業員に対応する子要素emp
があります。各従業員要素内に、その従業員の各依存に対応するdependent
要素があります。
例8-14 XMLAGG: ネストされた要素の生成
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データの生成
8.2.5 XMLPI SQL/XML関数
SQL/XML標準関数XMLPI
を使用して、XML処理命令(PI)を構成します。
図8-6に構文を示します。
引数value_expr
が評価され、文字列の結果が、空白で区切られてオプションの識別子(identifier
)に追加されます。この連結は、次に「<?
」と「?>
」で囲まれて処理命令になります。つまり、string-result
がvalue_expr
の評価結果だとすると、生成された処理命令は<?
identifier
string-result?>
となります。string-result
が空の文字列''
の場合、関数は<?
identifier
?>
を戻します。
キーワードNAME
にリテラル文字列identifier
を続ける方法の他に、キーワードEVALNAME
に、評価結果が識別子として使用される文字列になる式を続ける方法もあります。EVALNAME
の使用の可能性は、標準SQL/XML関数XMLPI
に対応するためのOracleの拡張です。
構成されたXMLが有効なXML処理命令でない場合はエラーが発生します。特に、次の点に注意してください。
-
identifier
には「xml
」という語は使用できません(大文字、小文字、混合のいずれも)。 -
string-result
には「?>
」という文字の並びを含めることはできません。
関数XMLPI
はXMLType
のインスタンスを戻します。string-result
がNULL
の場合はNULL
が戻されます。
例8-15では、XMLPI
を使用して簡単な処理の指示を生成します。
例8-15 SQL/XML関数XMLPIの使用
SELECT XMLPI(NAME "OrderAnalysisComp", 'imported, reconfigured, disassembled')
AS pi FROM DUAL;
これによって、次の出力が生成されます。
PI
----------------------------------------------------------
<?OrderAnalysisComp imported, reconfigured, disassembled?>
1 row selected.
親トピック: SQL関数を使用したXMLデータの生成
8.2.6 XMLCOMMENT SQL/XML関数
SQL/XML標準関数XMLComment
を使用して、XMLコメントを構成します。
図8-7に構文を示します。
引数value_expr
の評価結果は文字列で、生成されるXMLコメントの本文として使用されます。つまり、結果は<!--
string-result
-->
で、ここでstring-result
がvalue_expr
の文字列評価結果です。string-result
が空の文字列の場合、次のようにコメントは空になります。<!---->
。
構成されたXMLが有効なXMLコメントでない場合はエラーが発生します。特に、string-result
に、ハイフン(-
)が連続して2つ含まれてはいけないことに注意してください(--
)。
関数XMLComment
はXMLType
のインスタンスを戻します。string-result
がNULL
の場合、関数はNULL
を戻します。
例8-16では、XMLComment
を使用して簡単なXMLコメントを生成します。
例8-16 SQL/XML関数XMLCOMMENTの使用
SELECT XMLComment('This is a comment') AS cmnt FROM DUAL;
この問合せによって、次の出力が生成されます。
CMNT
--------------------------
<!--This is a comment-->
親トピック: SQL関数を使用したXMLデータの生成
8.2.7 XMLSERIALIZE SQL/XML関数
SQL/XML標準関数XMLSerialize
は、文字列またはXMLデータのLOB表現を取得するために使用します。
図8-8にXMLSerialize
の構文を示します。
引数value_expr
が評価され、結果のXMLType
インスタンスがシリアライズされて、作成された文字列またはLOBのコンテンツを生成します。存在する場合は脚注1、指定されたdatatype
は、次のいずれかであることが必要です(デフォルトのデータ型はCLOB
)。
-
VARCHAR2(
N
)
。ここでN
はバイト単位のサイズ脚注2 -
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
とともに指定した場合、エラーが発生します。UTF-16文字の場合、xml_encoding_spec
は次のいずれかである必要があります。
-
encoding=UTF-16BE
- ビッグ・エンディアンUTF-16エンコーディング -
encoding=UTF-16LE
- リトル・エンディアンUTF-16エンコーディング
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 Schemaでデフォルト値が定義されているオプションの要素や属性が入力データに存在しないと、それらの要素や属性はそのデフォルト値とともに出力に含められます。HIDE DEFAULTS
を指定した場合、このような要素や属性は出力に含められません。HIDE DEFAULTS
がデフォルトの動作です。
例8-17では、XMLSerialize
を使用して、シリアライズされたXMLデータを含むCLOB
インスタンスを生成します。
例8-17 SQL/XML関数XMLSERIALIZEの使用
SELECT XMLSerialize(DOCUMENT XMLType('<poid>143598</poid>') AS CLOB)
AS xmlserialize_doc FROM DUAL;
これによって、次の出力が生成されます。
XMLSERIALIZE_DOC ------------------- <poid>143598</poid>
親トピック: SQL関数を使用したXMLデータの生成
8.2.8 XMLPARSE SQL/XML関数
SQL/XML標準関数XMLParse
は、XMLデータを含む文字列を解析してXMLType
インスタンスの対応する値を構成するために使用します。
図8-9に構文を示します。
引数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
を戻します。
例8-18では、XMLParse
を使用してXMLコードの文字列を解析し、XMLType
インスタンスを生成します。
例8-18 SQL/XML関数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>
関連項目:
Extensible Markup Language (XML) 1.0 (整形式のXML文書およびフラグメントの定義)
親トピック: SQL関数を使用したXMLデータの生成
8.2.9 XMLCOLATTVAL Oracle SQL関数
Oracle SQL関数XMLColAttVal
は、渡された引数の値を含むXMLのcolumn
要素のフォレストを生成します。この関数は、SQL/XML ANSI-ISO標準関数に対応するためのOracleの拡張です。
図8-10はXMLColAttVal
の構文を説明するものです。
引数はcolumn
要素のname
属性の値として使用されます。c_alias
値は属性識別子として使用されます。
キーワードAS
にリテラル文字列c_alias
を続ける方法の他に、AS EVALNAME
に、評価結果が属性識別子として使用される文字列になる式を続ける方法もあります。
引数値value_expr
は属性valuesとしてのみ使用されているため、いずれにしてもエスケープする必要はありません。XMLForest
とは対照的です。つまり、XMLColAttVal
を使用すると、SQL列と値をエスケープせずに転送できます。
例8-19では、XMLColAttVal
を使用して、name
属性を含み、雇用日および部門をコンテンツとして含むcolumn
要素を持つEmp
要素を各従業員に1つずつ生成します。
例8-19 XMLCOLATTVAL: 属性と子要素を持つ要素の生成
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">2002-12-07</column> <column name = "department">30</column> </Emp> <Emp fullname="Alexander Khoo"> <column name = "HIRE_DATE">2003-05-18</column> <column name = "department">30</column> </Emp> <Emp fullname="Shelli Baida"> <column name = "HIRE_DATE">2005-12-24</column> <column name = "department">30</column> </Emp> <Emp fullname="Sigal Tobias"> <column name = "HIRE_DATE">2005-07-24</column> <column name = "department">30</column> </Emp> <Emp fullname="Guy Himuro"> <column name = "HIRE_DATE">2006-11-15</column> <column name = "department">30</column> </Emp> <Emp fullname="Karen Colmenares"> <column name = "HIRE_DATE">2007-08-10</column> <column name = "department">30</column> </Emp> 6 rows selected.
関連項目:
親トピック: SQL関数を使用したXMLデータの生成
8.2.10 XMLCDATA Oracle SQL関数
Oracle SQL関数XMLCDATA
を使用して、XML CDATA
セクションを生成します。
図8-11に構文を示します。
引数value_expr
の評価結果は文字列で、生成されるXML CDATA
セクション<![CDATA[
string-result
]]>
の本文として使用されます。ここで、string-result
は、value_expr
を評価した結果です。string-result
が空の文字列の場合、CDATA
セクションは空(<![CDATA[]]>
)です。
構成されたXMLが有効なXMLのCDATA
セクションと異なる場合はエラーが発生します。特に、string-result
に、左開き大カッコ(]
)が連続して2つ含まれてはいけないことに注意してください(]]
)。
関数XMLCDATA
はXMLType
のインスタンスを戻します。string-result
がNULL
の場合、関数はNULL
を戻します。
例8-20では、XMLCDATA
を使用して、XML CDATA
セクションを生成します。
例8-20 Oracle SQL関数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>
親トピック: SQL関数を使用したXMLデータの生成
8.3 DBMS_XMLGENを使用したXMLデータの生成
PL/SQLパッケージDBMS_XMLGEN
は、SQL問合せの結果からXML文書を作成します。XML文書はCLOB
またはXMLType
値として取得されます。
fetchインタフェースが用意されるので、それを使用して、取得する行の最大数とスキップする行数を指定できます。たとえば、最初のフェッチで最大10行を取り出し、最初の4行をスキップできます。これは特にWebアプリケーションでのページ区切り要件を満たすために有効です。
DBMS_XMLGEN
には、ROW
、ROWSET
などのタグ名を変更するオプションもあります。DBMS_XMLGENパッケージのパラメータは、取り出す行の数を制限し、タグ名を囲みます。
- PL/SQLパッケージDBMS_XMLGENの使用
パッケージDBMS_XMLGEN
を使用すると、リレーショナル・データからXMLデータを生成できます。 - パッケージDBMS_XMLGENのファンクションおよびプロシージャ
PL/SQLパッケージDBMS_XMLGEN
には、リレーショナル・データからXMLデータを生成するためのファンクションおよびプロシージャが用意されています。 - DBMS_XMLGENの例
ここでの例は、PL/SQLパッケージDBMS_XMLGEN
の使用方法を示しています。
関連項目:
-
Oracle XML Developer's Kitプログラマーズ・ガイド(
OracleXMLQuery
とDBMS_XMLGEN
を比較)
親トピック: リレーショナル・データからのXMLデータの生成
8.3.1 PL/SQLパッケージDBMS_XMLGENの使用
パッケージDBMS_XMLGEN
を使用すると、リレーショナル・データからXMLデータを生成できます。
図8-12は、パッケージDBMS_XMLGEN
の使用方法を示しています。ステップは次のとおりです。
SQL問合せと組み合せることにより、PL/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です。
親トピック: DBMS_XMLGENを使用したXMLデータの生成
8.3.2 パッケージDBMS_XMLGENのファンクションおよびプロシージャ
PL/SQLパッケージDBMS_XMLGEN
には、リレーショナル・データからXMLデータを生成するためのファンクションおよびプロシージャが用意されています。
表8-1に、パッケージDBMS_XMLGEN
のファンクションおよびプロシージャの概要を示します。
表8-1 DBMS_XMLGENのファンクションおよびプロシージャ
ファンクションまたはプロシージャ | 説明 |
---|---|
|
すべてのファンクションで使用されるコンテキスト・ハンドルです。 Document Type Definition(DTD)またはSchemaの指定は次のとおりです。
これらの指定を |
|
任意の問合せ文字列に対して、後続のファンクションで使用される新しいコンテキスト・ハンドルを生成します。 |
|
新しいコンテキストを戻します。 パラメータ: 戻り値: コンテキスト・ハンドル。最初にこのファンクションをコールして、結果からXMLを戻すための |
|
PL/SQLカーソル変数からコンテキスト・ハンドルを作成します。コンテキスト・ハンドルは、他のファンクションにも使用できます。 |
|
パラメータ: 戻り値: コンテキスト・ハンドル。最初にこのファンクションをコールして、結果から再帰要素付きの階層XMLを戻すための |
|
すべての行を区切る要素の名前を設定します。デフォルト名は |
|
パラメータ:
このファンクションをコールして、 ただし、 |
|
文書のルート要素の名前を設定します。デフォルト名は |
|
パラメータ:
このファンクションをコールして、文書のルート要素の名前を設定すると、デフォルト名の ただし、 |
|
指定した最大行数まで行をフェッチすることによって、XML文書を取得します。また、そのXML文書を渡された |
|
パラメータ:
このバージョンのファンクション |
|
XML文書を生成し、それを |
|
パラメータ:
戻り値: 文書を含む一時 |
|
パラメータ:
戻り値: 文書を含む |
|
SQL問合せ文字列
|
|
SQL問合せ文字列
|
|
ファンクション |
|
パラメータ: 戻り値: このファンクションをコールして、結果セットの最後に達したかどうかを確認できます。これには、XMLデータの生成前にスキップされる行数は含まれません。ループで |
|
|
|
パラメータ:
|
|
|
|
パラメータ:
|
|
XMLデータの特殊文字を、XMLの等価のエスケープ文字に変換する必要があるかどうかを指定します。たとえば、 |
|
パラメータ:
エスケープ文字で置換する必要がある特殊文字( |
|
コレクション要素名を設定します。コレクション要素のデフォルト名は、そのタイプ名です。このファンクションを使用して、列名を、列名に |
|
パラメータ: たとえば、 |
|
問合せを再度開始し、最初の行からXMLを生成します。 |
|
パラメータ: |
|
任意のコンテキストをクローズし、SQLカーソル、バインドや定義バッファなど、そのコンテキストに対応付けられているすべてのリソースを解放します。 |
|
パラメータ: |
変換関数
|
XMLデータ文字列引数をエンコードまたはデコードします。
|
|
渡されたXMLの
|
NULL処理
|
|
|
|
|
コンテキスト・ハンドルに関連付けられた問合せ文字列内で使用されているバインド変数に、バインド値を設定します。バインド変数が含まれる問合せ文字列は、 |
|
コンテキスト・ハンドルに関連付けられた問合せ文字列内で使用されているすべてのバインド変数のバインド値をすべて消去します。後で、 |
親トピック: DBMS_XMLGENを使用したXMLデータの生成
8.3.3 DBMS_XMLGENの例
ここでの例は、PL/SQLパッケージDBMS_XMLGEN
の使用方法を示しています。
例8-21では、DBMS_XMLGEN
を使用して、オブジェクト・リレーショナル表から従業員データを選択してXML文書を作成し、結果のCLOB
値を表に挿入します。
すべての行に対してXMLデータ全体を生成するかわりに、パッケージDBMS_XMLGEN
のフェッチ・インタフェースを使用して、1回に固定の行数を取り出すことができます。これによって特に行数が多い場合に、応答時間が短縮され、また結果のXMLにドキュメント・オブジェクト・モデル(DOM)Application Program Interface(API)を使用する必要があるアプリケーションのスケーリングに有効です。
例8-22では、DBMS_XMLGEN
を使用してHR.employees
表から結果を取り出します。
例8-23では、DBMS_XMLGEN
とオブジェクト型を使用してネスト構造を表現しています。
リレーショナル・データの場合、結果はネストされた要素がないXML文書になります。ネストしたXML構造にするには、オブジェクト・リレーショナル・データを使用します。この場合のマッピングは次のとおりです。
-
オブジェクト型はXML要素にマップします。XML Schemaの格納と問合せ: 基本を参照してください。
-
型の属性は、親要素のサブ要素にマップされます。
注意:
複雑な構造にするには、オブジェクト型を使用し、オブジェクト・ビューまたはオブジェクト表を作成します。正規マッピングを使用して、オブジェクト・インスタンスをXMLにマップしてください。
アットマーク(
@
)を列名または属性名で使用すると、マッピングでXMLの囲み要素の属性に変換されます。
ユーザー定義のデータ型インスタンスをDBMS_XMLGEN
ファンクションに入力すると、ユーザー定義データ型インスタンスは正規マッピングを使用してXML文書にマップされます。ユーザー定義のデータ型の属性はXML要素にマップされます。アットマーク「@
」で始まる名前の属性は、その前にある要素の属性にマップされます。
ユーザー定義のデータ型インスタンスを使用して、結果のXML文書内でネストできます。
たとえば、例8-24で定義されたemp
およびdept
という2つの表を考えてみます。部門とその従業員のデータの階層ビューを生成するために、例8-24では、適切なオブジェクト型を定義してデータベース内に構造を作成します。
デフォルト名ROW
は、NULL
に設定しているため存在しません。deptno
およびempno
は囲み要素の属性になっています。
例8-25では、DBMS_XMLGEN.getXMLType
を使用して、オブジェクト・ビューによって発注書をXML形式で生成します。
例8-26では、問合せのカーソル変数の開き方、およびカーソル変数を使用してDBMS_XMLGEN
の新しいコンテキスト・ハンドルを作成する方法を説明します。
関連項目:
カーソル変数(REF CURSOR
)の詳細は、Oracle Database PL/SQL言語リファレンスを参照してください。
例8-27に、DBMS_XMLGEN
を使用する場合のNULL
処理の指定方法を示します。
関数DBMS_XMLGEN.newContextFromHierarchy
は通常CONNECT BY
句を使用して形成される階層問合せ文字列を引数として取ります。再帰要素が含まれる階層XML文書の生成に使用されるコンテキストを戻します。
階層問合せでは、レベル番号(CONNECT BY
問合せを使用して生成される疑似列)とXMLType
インスタンスの2つの列が戻されます。レベルは、結果XML文書の階層内のXMLType
値の位置を判断するために使用されます。
newContextFromHierarchy
から作成されたコンテキストに対して、スキップ行数または最大行数を設定するとエラーになります。
例8-28では、DBMS_ XMLGEN.newContextFromHierarchy
を使用して、管理者(従業員階層)を生成します。
コンテキストの作成に使用する問合せ文字列にホスト変数が含まれている場合、問合せの実行前にPL/SQLメソッドsetBindValue
を使用して、変数値を指定できます。例8-29に、これを示します。
例8-21 DBMS_XMLGEN: 単純なXMLの生成
CREATE TABLE temp_clob_tab (result CLOB); DECLARE qryCtx DBMS_XMLGEN.ctxHandle; result CLOB; BEGIN qryCtx := DBMS_XMLGEN.newContext( 'SELECT * FROM hr.employees WHERE employee_id = 101'); -- 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; RESULT ------------------------------------------------------- <?xml version="1.0"?> <ROWSET> <EMPLOYEE> <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-05</HIRE_DATE> <JOB_ID>AD_VP</JOB_ID> <SALARY>17000</SALARY> <MANAGER_ID>100</MANAGER_ID> <DEPARTMENT_ID>90</DEPARTMENT_ID> </EMPLOYEE> </ROWSET> 1 row selected.
例8-22 DBMS_XMLGEN: ページ区切りを使用した単純なXMLの生成(フェッチ)
-- 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
-- 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-03</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-05</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-01</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-06</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.
例8-23 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.
例8-24 DBMS_XMLGEN: ユーザー定義のデータ型インスタンスを使用したXMLの生成
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. -- You 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; /
Oracle SQL関数cast
のMULTISET
キーワードでは、その部門に勤務する従業員をリストとして扱い、cast
がリストを適切なコレクション型に割り当てます。部門インスタンスは、コンストラクタdept_t
を使用して作成され、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.
例8-25 DBMS_XMLGEN: 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(XMLCast(XMLQuery('//PONO/text()' PASSING pxml RETURNING CONTENT) AS NUMBER), pxml); END LOOP; END; /
この問合せによって、2件のXML発注書が生成されます。
SELECT XMLSerialize(DOCUMENT x.podoc AS CLOB) 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.
例8-26 DBMS_XMLGEN: REFカーソルからの新規コンテキスト・ハンドルの生成
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.
例8-27 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.
例8-28 DBMS_XMLGEN: 階層問合せを使用した再帰XMLの生成
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>2001-01-13</Hiredate> <employees> <enumber>103</enumber> <name>Hunold</name> <Salary>9000</Salary> <Hiredate>2006-01-03</Hiredate> <employees> <enumber>105</enumber> <name>Austin</name> <Salary>4800</Salary> <Hiredate>2005-06-25</Hiredate> </employees> <employees> <enumber>106</enumber> <name>Pataballa</name> <Salary>4800</Salary> <Hiredate>2006-02-05</Hiredate> </employees> <employees> <enumber>107</enumber> <name>Lorentz</name> <Salary>4200</Salary> <Hiredate>2007-02-07</Hiredate> </employees> <employees> <enumber>104</enumber> <name>Ernst</name> <Salary>6000</Salary> <Hiredate>2007-05-21</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.
例8-29 DBMS_XMLGEN: 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-04</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-04'); xmldoc := DBMS_XMLGEN.getXML(ctx); DBMS_OUTPUT.put_line(xmldoc); DBMS_XMLGEN.setBindValue(ctx, 'MDATE', '10-MAR-05'); 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-04</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-05</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-04'); 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-04</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.
親トピック: DBMS_XMLGENを使用したXMLデータの生成
8.4 SYS_XMLAGG Oracle SQL関数
Oracle SQL関数sys_XMLAgg
は、式により表されるすべてのXML文書またはフラグメントを集計し、単一のXML文書を生成します。この関数は、デフォルト名ROWSET
の新しい要素の式の結果を囲みます。
Oracle関数sys_XMLAgg
はSQL/XML標準関数sys_XMLAgg
に類似していますが、XMLAgg
は単一のノードを戻し、XMLFormat
パラメータを取ります。そのパラメータを使用して結果のXML文書を様々な方法でフォーマットできます。
関連項目:
-
sys_XMLAgg
の詳細は、Oracle Database SQL言語リファレンスを参照してください。 -
XMLFormat
パラメータの詳細は、Oracle Database SQL言語リファレンスを参照してください。
親トピック: リレーショナル・データからのXMLデータの生成
8.5 XMLAGG ORDER BY句を使用して、問合せ結果を集計前に順序付けする方法
集計前にXMLAgg ORDER BY
句を使用するには、最初のXMLAGG
引数の後にORDER BY
句を指定します。
これを例8-30に示します。
例8-30 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 dev_tab 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.
親トピック: リレーショナル・データからのXMLデータの生成
8.6 XMLTABLEを使用して行セットを戻す方法
標準SQL/XML関数XMLTable
を使用すると、複数の行として抽出された文書の関連する部分の行セットを戻すことができます。
例8-31を参照してください。
例8-31 XMLTABLEを使用して行セットを戻す方法
CONNECT oe
Enter password: password
Connected.
SELECT item.descr, item.partid
FROM purchaseorder,
XMLTable('$p/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.
親トピック: リレーショナル・データからのXMLデータの生成
脚注の説明
脚注1:SQL/XML標準では、引数data-type
が存在することを必須としていますが、Oracle XML DBでのこの標準の実装では、便宜性を考慮してオプションとなっています。
脚注2:
制限は32767または4000バイトであり、初期化パラメータMAX_STRING_SIZE
の値によって異なります。『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。