| Oracle® XML DB開発者ガイド 11gリリース2 (11.2) B70200-03 |
|
 前 |
 次 |
この章では、データベースのリレーショナル・データからXMLデータを生成(構成)するためのOracle XML DB機能を説明します。また、リレーショナル・コンテンツからXMLデータを生成するためのSQL/XML標準関数およびOracle Databaseが提供する関数とパッケージについて説明します。
この章の内容は次のとおりです。
次に示す方法でOracle DatabaseによりXMLデータを生成できます。
標準SQL/XML関数を使用します。「SQL関数を使用したXMLの生成」を参照してください。
Oracle SQL関数を使用します。次の項を参照してください。
SYS_XMLGEN Oracle SQL関数この関数は、行を操作し、XML文書を生成します。
SYS_XMLAGG Oracle SQL関数この関数は、行のグループを操作し、複数のXML文書を1つに集約します。
PL/SQLパッケージDBMS_XMLGENの使用。「DBMS_XMLGENを使用したXMLの生成」を参照してください。
DBURITypeインスタンスを使用してデータベース・データからXML文書を構成します。第20章「URIを介したデータのアクセス」を参照してください。
この項では、XMLデータの構成に使用するSQL関数について説明します。この関数の多くは、SQL/XML標準に属しています。
標準XML生成関数はSQL/XMLパブリッシング関数とも呼ばれます。
SQL/XML関数XMLQueryを使用してXMLデータを構成することもできます。XMLQueryの用途は、XMLデータのパブリッシュにかぎりません。非常に汎用性が高く、このマニュアルではSQL/XML問合せおよびアクセス関数と呼んでいます。
この標準化プロセスは進行中です。XMLQueryやXMLTableを含むSQL/XML関数の最新情報については、http://www.sqlx.orgを参照してください。
この項で説明する、XMLを生成する他のSQL関数は、(SQL/XML標準の一部ではなく)Oracle専用です。
SYS_XMLGEN Oracle SQL関数この関数は、リレーショナル行を操作し、XML文書を生成します。
SYS_XMLAGG Oracle SQL関数この関数は、リレーショナル行のグループを操作し、複数のXML文書を1つに集約します。
すべてのXML生成SQL関数では、スカラーおよびユーザーのデータ型インスタンスが正規のXML形式に変換されます。この正規マッピングでは、ユーザー定義のデータ型の属性はXML要素にマップされます。
|
関連項目:
|
SQL/XML標準関数XMLElementを使用して、リレーショナル・データからXML要素を構成します。この関数は、要素名、その要素の属性のコレクション(オプション)、およびその要素のコンテンツを構成する0(ゼロ)個以上の引数を取ります。戻り値はXMLTypeインスタンスです。
キーワードENTITYESCAPINGおよびNOENTITYESCAPINGの説明は、「生成されたXMLデータにおける文字のエスケープ」を参照してください。これらのキーワードは、標準SQL/XML関数XMLElementおよびXMLAttributesに対応するためのOracleの拡張です。
関数XMLElementの最初の引数は、作成されるrootXML要素名に名前を付ける識別子を定義します。ルート要素識別子の引数は、リテラル識別子(図18-1のidentifier)を使用することで、またはEVALNAMEに識別子を評価する式(value_expr)を続けることで定義できます。ただし、識別子は、定義されてもNULLにできません。NULLの場合、エラーが発生します。EVALNAMEの使用の可能性は、標準SQL/XML関数XMLElementに対応するためのOracleの拡張です。
関数XMLElementのオプションのXML-attributes-clause引数は、生成されるルート要素の属性を指定します。この関数の構文を図18-2に示します。
オプションのXML-attributes-clause引数の他に、関数XMLElementは、ルート要素のコンテンツ(子要素とテキスト・コンテンツ)を構成する0 (ゼロ)個以上のvalue_expr引数を取ります。XML-attributes-clause引数も存在する場合、XML-attributes-clause引数の後にこれらのコンテンツ引数が続く必要があります。個々のコンテンツ引数式が評価され、結果がXMLフォーマットに変換されます。値引数の評価結果がNULLだった場合、その引数にはコンテンツが作成されません。
オプションの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で提供されているデフォルト動作では、チェックは行いません。11gリリース2(11.2)以前のリリースでは、デフォルト動作ではチェックを行います。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に文字列リテラルを評価する式が続く名前になります。(EVALNAMEの使用の可能性は、標準SQL/XML関数XMLElementに対応するためのOracleの拡張です)。
属性値式がNULLであると評価された場合、その式に対応する属性は作成されません。属性値式のデータ型は、オブジェクト型またはコレクションにできません。
SQL/XML標準で規定されているとおり、明示された識別子に含まれる文字はいかなる場合もエスケープされません。必ず、有効なXML名を使用してください。これはすべてのSQL/XML関数に当てはまります。特に、XMLElementのルート要素識別子(図18-1のidentifier)やXMLAttributesのAS句で名前を指定された属性識別子エイリアスに当てはまります(図18-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の拡張です。
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を使用します(例18-1を参照)。
|
関連項目: http://www.w3.org/TR/2004/REC-xmlschema-2-20041028/datatypes.html#isoformats にある、XMLの日付およびタイムスタンプのフォーマットに関するXMLスキーマの仕様 |
この項では、SQL/XML関数XMLElementを使用する例を示します。
例18-1では、XMLElementを使用して、XML Schemaの標準フォーマットと異なるフォーマットを持つXMLの日付を生成します。
例18-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.
例18-2では、XMLElementを使用して、従業員の名前をコンテンツとして持つEmp要素を各従業員に1つずつ生成します。
例18-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データを生成することもできます。
例18-3では、XMLElementを使用して、従業員名と雇用日を提供する子要素を持つEmp要素を各従業員に1つずつ生成します。
例18-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.
例18-4では、XMLElementを使用して、属性idおよびnameを持つEmp要素を各従業員に1つずつ生成します。
例18-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.
「生成されたXMLデータにおける文字のエスケープ」で前述のとおり、ルート要素名、およびAS句により定義される属性の名前に含まれる文字はエスケープされません。識別指名に含まれる文字は、その名前が、評価された式から作成される場合にのみエスケープされます(列の参照と同様)。
例18-5は、XMLElementを使用して構成されたXNLデータによって、ルート要素名と属性名がエスケープされないことを示します。大なり記号(>)とカンマ(,)はXML要素や属性名では許容されないため、無効なXMLが作成されます。
例18-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.
文字のエスケープに関する詳細説明は、SQL/XML標準に含まれています。
例18-6では、名前空間を使用してXML Schemaに基づく文書を作成する方法を示します。XML Schema http://www.oracle.com/Employee.xsdが存在し、ターゲットの名前空間が存在しない場合、例18-6の問合せによって、そのスキーマに準拠したXMLTypeインスタンスが作成されます。
例18-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.
例18-7では、XMLElementを使用して、従業員と部門情報を含むXML文書を生成します。ここでは、サンプルのデータベース・スキーマ表hr.departmentsを使用します。
例18-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.
SQL/XML標準関数XMLForestを使用して、XML要素のフォレストを構成します。その引数は評価する式で、オプションで別名も付けられます。図18-3 は、XMLForestの構文を説明するものです。
値の式(図18-3のvalue_expr)がXMLフォーマットに変換され、識別子c_aliasが属性識別子として使用されます(c_aliasは文字列リテラルまたはEVALNAMEに文字列リテラルを評価する式が続く名前になります)。EVALNAMEの使用の可能性は、標準SQL/XML関数XMLForestに対応するためのOracleの拡張です。
オブジェクト型またはコレクションの場合、AS句は必須です。それ以外の方の場合、AS句はオプションです。指定された式でAS句が省略されている場合は、評価された値の式に含まれる文字はエスケープされ、要素の囲みタグ名を形成します。エスケープの方式は「生成されたXMLデータにおける文字のエスケープ」での定義によります。値の式の評価結果がNULLだった場合、その式には要素が作成されません。
例18-8では、XMLElementとXMLForestを使用して、name属性を含み、雇用日および部門をコンテンツとして含む子要素を持つEmp要素を各従業員に1つずつ生成します。
例18-7 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.
例18-9では、XMLForestを使用して、ユーザー定義のデータ型インスタンスから階層XMLデータを生成します。
例18-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;
これにより、属性DEPTNOおよび子要素DNAMEを含む要素Departmentを持つXMLドキュメントが作成されます。
DEPTXML
---------------------------------
<Department DEPTNO="10">
<DNAME>Administration</DNAME>
<EMP_LIST>
<EMP_T EMPNO="200">
<ENAME>Whalen</ENAME>
</EMP_T>
</EMP_LIST>
</Department>
1 row selected.
SQL/XML標準関数XMLConcatを使用して、複数のXMLTypeインスタンスを連結することにより、XMLフラグメントを構成します。図18-4はXMLConcatの構文を説明するものです。関数XMLConcatには次の2つの形式があります。
1つ目の形式では、XMLTypeインスタンスのVARRAYであるXMLSequenceType値を引数として取り、VARRAYのすべての要素を連結して単一のXMLTypeインスタンスを戻します。この形式は、XMLTypeインスタンスのリストを単一のインスタンスに縮小する場合に有効です。
2つ目の形式では、任意の数のXMLTypeインスタンスを取り、それらの値を連結します。値の1つがNULLである場合、その値は結果に出力されません。すべての値がNULLである場合、結果はNULLになります。この形式は、任意の数のXMLTypeインスタンスを同じ行に連結するために使用できます。関数XMLAggを使用すると、複数行にわたってXMLTypeインスタンスを連結できます。
例18-10では、SQL/XML関数XMLConcatを使用して、XMLSequenceType値(XMLTypeインスタンスのVALLAY)のXMLTypeインスタンスを連結して戻します。
例18-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.
例18-11では、XMLConcatを使用して、従業員の姓と名のXML要素を作成して連結します。
例18-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標準関数XMLAggを使用して、XML要素のコレクションからXML要素のフォレストを構成します。これは集計関数です。
図18-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が戻されます。
例18-12では、SQL/XML関数XMLAggおよびXMLElementを使用して、従業員のジョブIDおよび姓を要素のコンテンツとして持つEmployee要素を含むDepartment要素を作成します。また、その部門に属するEmployee要素を従業員の姓で順序付けします。また、その部門に属する Employee 要素を従業員の姓で順序付けします (わかりやすいように、この結果をフォーマット出力しています)。
例18-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によって行が集計されるため、単一の従業員行が戻されます。
例18-13は、GROUP BY句を使用して、戻された一連の行を複数のグループにグループ化し、Department要素を構成する方法を示しています。(ここではわかりやすいように、この結果をフォーマット出力しています。)
例18-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を使用すると、表に存在するいくつかの階層関係を反映できます。例18-14では、部門30の部門要素を生成します。この要素内に、部門の各従業員に対応する子要素empがあります。各従業員要素内に、その従業員の各依存に対応するdependent要素があります。
例18-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標準関数XMLPIを使用して、XML処理命令(PI)を構成します。図18-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が戻されます。
例18-15では、XMLPIを使用して簡単な処理の指示を生成します。
SQL/XML標準関数XMLCommentを使用して、XMLコメントを構成します。図18-7に構文を示します。
引数value_exprの評価結果は文字列で、生成されるXMLコメントの本文として使用されます。つまり、結果は<!--string-result-->で、ここでstring-resultがvalue_exprの文字列評価結果です。string-resultが空の文字列の場合、次のようにコメントは空になります。<!---->
構成されたXMLが有効なXMLコメントでない場合はエラーが発生します。特に、string-resultに、ハイフン(-)が連続して2つ含まれてはいけないことに注意してください(--)。
関数XMLCommentはXMLTypeのインスタンスを戻します。string-resultがNULLの場合、関数はNULLを戻します。
例18-16では、XMLCommentを使用して簡単なXMLコメントを生成します。
SQL/XML標準関数XMLSerializeは、文字列またはXMLデータのLOB表現を取得するために使用します。
図18-8にXMLSerializeの構文を示します。
引数value_expr/が評価され、結果のXMLTypeインスタンスがシリアライズされて、作成された文字列またはLOBのコンテンツを生成します。存在する場合は脚注1、指定されたdatatypeは、次のいずれかであることが必要です(デフォルトのデータ型はCLOB)。
VARCHAR2(N)。Nはバイト・サイズ(上限limit 4000)を示します。
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がデフォルトの動作です。
例18-17では、XMLSerializeを使用して、シリアライズされたXMLデータを含むCLOBインスタンスを生成します。
SQL/XML標準関数XMLParseは、XMLデータを含む文字列を解析してXMLTypeインスタンスの対応する値を構成するために使用します。図18-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を戻します。
例18-18では、XMLParseを使用してXMLコードの文字列を解析し、XMLTypeインスタンスを生成します。
例18-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>
|
関連項目: http://www.w3.org/TR/REC-xml/「Extensible Markup Language (XML) 1.0」にある、整形式のXML文書およびフラグメントに関する説明 |
SQL関数XMLRootはSQL/XML標準関数である時期がありましたが、SQL/XML 2005時点で標準関数として非推奨になりました。Oracle SQL関数としてOracle XML DBでは使用できます。
XMLRootは、XML値のルート情報項目へのVERSIONプロパティの追加や、オプションのSTANDALONEプロパティの追加に使用します。通常、これはデータモデル準拠の確認に使用します。図18-10に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を戻します。
例18-19では、XMLRootを使用して、version属性およびstandalone属性を持つXML宣言を追加します。
Oracle SQL関数XMLColAttValは、渡された引数の値を含むXMLのcolumn要素のフォレストを生成します。この関数は、SQL/XML ANSI-ISO標準関数に対応するためのOracleの拡張です。図18-11はXMLColAttValの構文を説明するものです。
引数はcolumn要素のname属性の値として使用されます。c_alias値は属性識別子として使用されます。
キーワードASにリテラル文字列c_aliasを続ける方法の他に、AS EVALNAMEに、評価結果が属性識別子として使用される文字列になる式を続ける方法もあります。
引数値value_exprは属性valuesとしてのみ使用されているため、いずれにしてもエスケープする必要はありません。XMLForestとは対照的です。つまり、XMLColAttValを使用すると、SQL列と値をエスケープせずに転送できます。
例18-20では、XMLColAttValを使用して、name属性を含み、雇用日および部門をコンテンツとして含むcolumn要素を持つEmp要素を各従業員に1つずつ生成します。
例18-20 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.
Oracle SQL関数XMLCDATAを使用して、XML CDATAセクションを生成します。図18-12に構文を示します。
引数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を戻します。
例18-21では、XMLCDATAを使用して、XML CDATAセクションを生成します。
例18-21 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>
PL/SQLパッケージDBMS_XMLGENは、SQL問合せの結果からXML文書を作成します。XML文書はCLOBまたはXMLType値として取得されます。
fetchインタフェースが用意されるので、それを使用して、取得する行の最大数とスキップする行数を指定できます。たとえば、最初のフェッチで最大10行を取り出し、最初の4行をスキップできます。これは特にWebアプリケーションでのページ区切り要件を満たすために有効です。
DBMS_XMLGENには、ROW、ROWSETなどのタグ名を変更するオプションもあります。DBMS_XMLGENパッケージのパラメータは、取り出す行の数を制限し、タグ名を囲みます。
|
関連項目:
|
図18-13は、パッケージ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問合せと組み合せることにより、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です。
表18-1に、DBMS_XMLGENのファンクションおよびプロシージャの概要を示します。
表18-1 DBMS_XMLGENのファンクションおよびプロシージャ
例18-22では、DBMS_XMLGENを使用して、オブジェクト・リレーショナル表から従業員データを選択してXML文書を作成し、結果のCLOB値を表に挿入します。
例18-22 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.
すべての行に対してXMLデータ全体を生成するかわりに、パッケージDBMS_XMLGENのフェッチ・インタフェースを使用して、1回に固定の行数を取り出すことができます。これによって特に行数が多い場合に、応答時間が短縮され、また結果のXMLにドキュメント・オブジェクト・モデル(DOM)Application Program Interface(API)を使用する必要があるアプリケーションのスケーリングに有効です。
例18-23では、DBMS_XMLGENを使用してHR.employees表から結果を取り出します。
例18-23 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.
例18-24では、DBMS_XMLGENとオブジェクト型を使用してネスト構造を表現しています。
例18-24 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要素をマップします。第7章「XML Schemaの格納と問合せ: 基本」を参照してください。
型の属性は、親要素のサブ要素にマップされます。
|
注意: 複雑な構造にするには、オブジェクト型を使用し、オブジェクト・ビューまたはオブジェクト表を作成します。正規マッピングを使用して、オブジェクト・インスタンスをXMLにマップしてください。アットマーク( |
ユーザー定義のデータ型インスタンスをDBMS_XMLGENファンクションに入力すると、ユーザー定義データ型インスタンスは正規マッピングを使用してXML文書にマップされます。ユーザー定義のデータ型の属性はXML要素にマップされます。アットマーク「@」で始まる名前の属性は、その前にある要素の属性にマップされます。
ユーザー定義のデータ型インスタンスを使用して、結果のXML文書内でネストできます。
たとえば、例18-25で定義されたempおよびdeptという2つの表を考えてみます。部門とその従業員のデータの階層ビューを生成するために、例18-25では、適切なオブジェクト型を定義してデータベース内に構造を作成します。
例18-25 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.
デフォルト名ROWは、NULLに設定しているため存在しません。deptnoおよびempnoは囲み要素の属性になっています。
例18-26では、DBMS_XMLGEN.getXMLTypeを使用して、オブジェクト・ビューによって発注書をXML形式で生成します。
例18-26 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.
例18-27では、問合せのカーソル変数の開き方、およびカーソル変数を使用してDBMS_XMLGENの新しいコンテキスト・ハンドルを作成する方法を説明します。
例18-27 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.
|
関連項目: カーソル変数(REF CURSOR)の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
例18-28に、DBMS_XMLGENを使用する場合のNULL処理の指定方法を示します。
例18-28 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.
関数DBMS_XMLGEN.newContextFromHierarchyは通常CONNECT BY句を使用して形成される階層問合せ文字列を引数として取ります。再帰要素が含まれる階層XML文書の生成に使用されるコンテキストを戻します。
階層問合せでは、レベル番号(CONNECT BY問合せを使用して生成される疑似列)とXMLTypeの2つの列が戻されます。レベルは、結果XML文書の階層内のXMLType値の位置を判断するために使用されます。
newContextFromHierarchyから作成されたコンテキストに対して、スキップ行数または最大行数を設定するとエラーになります。
例18-29では、DBMS_ XMLGEN.newContextFromHierarchyを使用して、管理者(従業員階層)を生成します。
例18-29 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.
コンテキストの作成に使用する問合せ文字列にホスト変数が含まれている場合、問合せの実行前にPL/SQLメソッドsetBindValueを使用して、変数値を指定できます。例18-30に、これを示します。
例18-30 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.
Oracle SQL関数sys_XMLGenは、SQL/XML標準関数XMLElementに類似していますが、単一の引数を取り、結果をXMLTypeインスタンスに変換します。他のXML生成関数とは異なり、sys_XMLGenは常に整形式のXML文書を戻します。また、問合せレベルで機能するDBMS_XMLGENとは異なり、ssys_XMLGenは行レベルで機能し、各行に対してXML文書を戻します。
例18-31では、sys_XMLGenを使用してXMLインスタンスを問い合せ、リレーショナル・データの各行にXML文書を戻します。
例18-31 SYS_XMLGENを使用した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の構文
Oracle SQL関数sys_XMLGenは、XML文書に変換するスカラー値の引数、オブジェクト型またはXMLTypeインスタンスを取ります。また、結果のXML文書のフォーマット・オプションを指定するオプションのXMLFormatオブジェクト(以前のXMLGenFormatType)も取ります(オプション)。この構文を図18-14に示します。
式exprの評価結果はデータベースの特定の行と列です。これは、スカラー値、ユーザー定義のデータ型インスタンス、またはXMLTypeインスタンスのいずれかです。
exprの評価結果がスカラー値の場合、この関数は、スカラー値を含むXML要素を戻します。
exprの評価結果がユーザー定義のデータ型インスタンスの場合、ユーザー定義の型属性をXML要素にマップします。
exprの評価結果がXMLTypeインスタンスの場合、この関数は、デフォルトのタグ名がROWであるXML要素で文書を囲みます。
デフォルトでは、XML文書の要素はexprと一致します。たとえば、exprが列名に変換される場合、XMLの囲み要素の名前は列名と同じになります。XML文書を別の方法でフォーマットする場合は、XMLFormatオブジェクトのインスタンスであるfmtを指定します。
問合せ内でWHEREをsys_XMLGenとともに使用して<ROW/>タグを出力しないようにすると、NULL値が表示されません。
SELECT sys_XMLGen(x) FROM table_name WHERE x IS NOT NULL;
例18-32では、表HR.employeesから、従業員(employee_id値が110)の名前を取得し、FIRST_NAME要素を持つXML文書を含むXMLTypeインスタンスを生成します。
例18-32 SYS_XMLGEN: データベース列からのXML要素の生成
SELECT XMLSerialize(DOCUMENT SYS_XMLGEN(first_name)) FROM employees WHERE employee_id = 110; XMLSERIALIZE(DOCUMENTSYS_XMLGEN(FIRST_NAME)) -------------------------------------------- <?xml version="1.0"?> <FIRST_NAME>John</FIRST_NAME> 1 row selected.
Oracle SQL関数sys_XMLGenには次の利点があります。
SQL問合せ内でXMLインスタンスを作成し、問い合せることができます。
オブジェクト・リレーショナル・インフラストラクチャを使用して、簡単なリレーショナル表から複雑なネストしたXMLインスタンスを作成できます。たとえば、オブジェクト型でsys_XMLGenを使用した場合のXMLTypeビューを使用すると、Oracle XML DBでは、可能なかぎりこれらの問合せがリライトされます。第8章「構造化記憶域のXPathリライト」も参照してください。
sys_XMLGenは、ユーザー定義のデータ型インスタンス、スカラー値、またはXMLTypeインスタンスからXML文書を作成します。戻り値はXMLTypeインスタンスです。
sys_XMLGenは、結果をカスタマイズするのに使用するオプションのXMLFormatオブジェクトも引数として受け入れます。書式設定オブジェクトがNULLの場合は、デフォルトのマッピング動作が使用されます。
XMLFormatを使用して、Oracle SQL関数sys_XMLGenおよびsys_XMLAggに対してフォーマット引数を指定できます。
関数sys_XMLGenはXML文書を含むXMLTypeインスタンスを戻します。Oracle Databaseには、sys_XMLGenの出力をフォーマットするXMLFormatオブジェクトが用意されています。
表18-2に、オブジェクトXMLFormatの属性をリストします。
表18-2 XMLFormatオブジェクトの属性
| 属性 | データ型 | 用途 |
|---|---|---|
|
|
|
|
|
|
|
出力ドキュメントに対するスキーマ生成の型です。有効な値は、 |
|
|
|
|
|
|
|
スキーマ( |
|
|
|
|
|
|
|
ユーザー指定の処理命令です。関数出力の先頭の要素の前に追加されるユーザーの処理命令です。 |
PL/SQLメソッドcreateFormatを使用すると、XMLFormatオブジェクトを実装できます。オブジェクトXMLFormat()のメソッドcreateFormatは、囲み要素名、XML Schema型、およびXML Schema名を引数に取ります。他のXMLFormat属性にはデフォルト値が用意されています。
|
関連項目:
|
SQL関数sys_XMLGenは、スカラー値を、そのスカラー値を含む要素に変換します。例18-33の問合せに、これを示します。employee_id値を要素EMPLOYEE_IDのコンテンツとして含むXML文書を戻します。
例18-33 SYS_XMLGEN: スカラー値の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インスタンスを含む単独の行です。
例18-33で、列名EMPLOYEE_IDはXML要素名のデフォルトとして使用されています。列名を直接導出できない場合は、例18-34に示すように、デフォルト名ROWがかわりに使用されます。
例18-34 SYS_XMLGEN: デフォルトの要素名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.
例18-34では、sys_XMLGenの引数が単純な列名ではないため、出力要素タグの名前をそのまま列名にできません。このためデフォルトの要素名ROWが使用されます。
デフォルトのROWタグは、XMLFormatオブジェクトを2番目のsys_XMLGen引数に指定することによって変更できます。例18-35にこれを示します。問合せがフォーマット引数をsys_XMLGenに渡し、要素の名前を明示的に指定します。
例18-35 SYS_XMLGENとXMLFormatを使用したデフォルトの要素名のオーバーライド
SELECT XMLSerialize(DOCUMENT
SYS_XMLGEN(employee_id*2,
XMLFormat.createformat('DOUBLE_ID'))
AS CLOB)
FROM hr.employees WHERE ROWNUM < 2;
XMLSERIALIZE(DOCUMENTSYS_XMLGEN(EMPLOYEE_ID*2,XMLFORMAT.CREATEFORMAT('DOUBLE_ID'
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<DOUBLE_ID>200</DOUBLE_ID>
1 row selected.
ユーザー定義のデータ型インスタンスをsys_XMLGenに引数として指定すると、インスタンスはXML文書に正規マッピングされます。このマッピングでは、ユーザー定義のデータ型の属性はXML要素にマップされます。
アットマーク「@」で始まる名前のデータ型属性は、その前にあるXML要素の属性にマップされます。ユーザー定義のデータ型インスタンスを使用して、結果のXML文書内でネストを取得できます。
例18-36に、「DBMS_XMLGENを使用したXMLの生成」の項の従業員と部門の例の階層XMLを生成する方法を示します。
例18-36 SYS_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 XMLSerialize(
DOCUMENT
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)))
AS CLOB)
AS deptxml
FROM hr.departments d WHERE department_id = 10 OR department_id = 20;
Oracle SQL関数castのMULTISETキーワードでは、その部門に勤務する従業員をリストとして扱い、castによって適切なコレクション型に割り当てられます。sys_XMLGenは、オブジェクト・インスタンスの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-03</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-04</HIREDATE> <SAL>13000</SAL> </EMP_T> <EMP_T> <EMPNO>202</EMPNO> <ENAME>Fay</ENAME> <JOB>MK_REP</JOB> <MGR>201</MGR> <HIREDATE>17-AUG-05</HIREDATE> <SAL>6000</SAL> </EMP_T> </EMPLIST> </ROW> 2 rows selected.
|
注意: 前の例から、SQL関数sys_XMLGenとPL/SQLパッケージDBMS_XMLGENの違いは明確です。関数sys_XMLGen はSQL問合せの内部で動作し、行内の式および列を操作します。パッケージDBMS_XMLGENは、結果セット全体で動作します。 |
XML文書をsys_XMLGenに渡す場合、この関数は、タグ名がデフォルトのROWである要素、またはXMLFormat書式設定オブジェクトを介して渡される名前で、その文書(またはフラグメント)を囲みます。この機能を使用して、XMLフラグメントを整形式の文書に変換できます。例18-37に、これを示します。
例18-37 SYS_XMLGEN: XMLTypeインスタンスの変換
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 XMLQuery('/DOCUMENT/EMPLOYEE/ENAME' PASSING e.podoc RETURNING CONTENT)
FROM po_xml_tab e;
問合せの結果はXML文書フラグメントです(ここではわかりやすくするためフォーマット出力しています)。
<ENAME>John</ENAME> <ENAME>Jack</ENAME> <ENAME>Joseph</ENAME>
このフラグメントを有効なXML文書にするには、次のように、sys_XMLGenをコールして、フラグメントをルート要素で囲みます。
SELECT XMLSerialize(DOCUMENT
sys_XMLGen(XMLQuery('/DOCUMENT/EMPLOYEE/ENAME'
PASSING e.podoc RETURNING CONTENT))
AS CLOB)
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オブジェクト型の使用」を参照してください。 |
例18-38では、オブジェクト・ビューでsys_XMLGenを使用する方法を示します(ここで定義されていないエンティティは、例18-26のコードを参照してください)。
例18-38 オブジェクト・ビューでのSYS_XMLGENの使用
-- 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 XMLSerialize(DOCUMENT
SYS_XMLGEN(OBJECT_VALUE,
XMLFormat.createFormat('PurchaseOrder'))
AS CLOB) 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.
Oracle SQL関数sys_XMLAggは、式により表されるすべてのXML文書またはフラグメントを集計し、単一のXML文書を生成します。この関数は、デフォルト名ROWSETの新しい要素の式の結果を囲みます。
Oracle関数sys_XMLAggはSQL/XML標準関数sys_XMLAggに類似していますが、XMLAggは単一のノードを戻し、XMLFormatパラメータを取ります。そのパラメータを使用して結果のXML文書を様々な方法でフォーマットできます。
|
関連項目: 『Oracle Database SQL言語リファレンス』 |
この項では、Oracle XML DBを使用してXMLを生成する場合の追加のガイドラインを示します。
集計前にXMLAgg ORDER BY句を使用するには、最初のXMLAGG引数の後にORDER BY句を指定します。これを例18-39に示します。
例18-39 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を使用し、例18-40のように、複数の行として抽出された文書の関連する部分の行セットを戻します。
例18-40 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.
脚注の凡例
脚注 1: SQL/XML標準では、引数data-typeが存在することが必須ですが、Oracle XML DBでのこの標準の実装では、便宜性を考慮してオプションとなっています。