16 Generating XML Data from the Database

Example 16-7 XMLFOREST: Generating Elements with Attribute and Child Elements

This example generates an Emp element for each employee, with a name attribute and elements with the employee hire date and department as the content.

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;

(The WHERE clause is used here to keep the example brief.) This query produces the following XML result:

RESULT
-------------------------------------
<Emp name="Michael Hartstein">
  <HIRE_DATE>1996-02-17</HIRE_DATE>
  <department>20</department>
</Emp>
<Emp name="Pat Fay">
  <HIRE_DATE>1997-08-17</HIRE_DATE>
  <department>20</department>
</Emp>

2 rows selected.

Example 16-8 XMLFOREST: Generating an Element from a User-Defined Datatype Instance

You can also use SQL function XMLForest to generate hierarchical XML from user-defined datatype instances.

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;

Example 16-9 XMLSEQUENCE Returns Only Top-Level Element Nodes

Function XMLSequence returns only top-level element nodes. That is, it will not shred attributes or text nodes.

SELECT value(T).getstringval() Attribute_Value
  FROM table(XMLSequence(extract(XMLType('<A><B>V1</B><B>V2</B><B>V3</B></A>'),
                                 '/A/B'))) T;

ATTRIBUTE_VALUE
----------------------
<B>V1</B>
<B>V2</B>
<B>V3</B>

3 rows selected.

Example 16-10 XMLSEQUENCE: Generating One XML Document from Another

Consider the following XMLType table containing an XML document with employee information:

CREATE TABLE emp_xml_tab OF XMLType;
 
Table created.

INSERT INTO emp_xml_tab VALUES( XMLType('<EMPLOYEES>
                                           <EMP>
                                             <EMPNO>112</EMPNO> 
                                             <EMPNAME>Joe</EMPNAME>
                                             <SALARY>50000</SALARY>
                                           </EMP>
                                           <EMP>
                                             <EMPNO>217</EMPNO>
                                             <EMPNAME>Jane</EMPNAME> 
                                             <SALARY>60000</SALARY>
                                           </EMP>
                                           <EMP> 
                                             <EMPNO>412</EMPNO> 
                                             <EMPNAME>Jack</EMPNAME>
                                             <SALARY>40000</SALARY>
                                           </EMP>
                                         </EMPLOYEES>'));

1 row created.

COMMIT;

To create a new XML document containing only employees who earn $50,000 or more, you can use the following query:

SELECT sys_XMLAgg(value(em), XMLFormat('EMPLOYEES'))
  FROM emp_xml_tab doc, table(XMLSequence(extract(value(doc), 
                                          '/EMPLOYEES/EMP'))) em
  WHERE extractValue(value(em), '/EMP/SALARY') >= 50000;

These are the steps involved in this query:

1. Function extract returns a fragment of EMP elements.

2. Function XMLSequence gathers a collection of these top-level elements into XMLType instances and returns that.

3. Function table makes a table value from the collection. The table value is then used in the query FROM clause.

The query returns the following XML document:

SYS_XMLAGG(VALUE(EM),XMLFORMAT('EMPLOYEES'))
--------------------------------------------
<?xml version="1.0"?>
<EMPLOYEES>
  <EMP>
    <EMPNO>112</EMPNO>
    <EMPNAME>Joe</EMPNAME>
    <SALARY>50000</SALARY>
  </EMP>
  <EMP>
    <EMPNO>217</EMPNO>
    <EMPNAME>Jane</EMPNAME>
    <SALARY>60000</SALARY>
  </EMP>
</EMPLOYEES>
 
1 row selected.

Example 16-11 XMLSEQUENCE: Generate a Document for Each Row of a Cursor

In this example, SQL function XMLSequence is used to create an XML document for each row of a cursor expression, and it returns an XMLSequenceType value (a varray of XMLType instances).

SELECT value(em).getClobVal() AS "XMLTYPE"
  FROM table(XMLSequence(Cursor(SELECT * 
                                  FROM hr.employees
                                  WHERE employee_id = 104))) em;

This query returns the following XML:

XMLTYPE
--------------------------------------------------
 <ROW>
  <EMPLOYEE_ID>104</EMPLOYEE_ID>
  <FIRST_NAME>Bruce</FIRST_NAME>
  <LAST_NAME>Ernst</LAST_NAME>
  <EMAIL>BERNST</EMAIL>
  <PHONE_NUMBER>590.423.4568</PHONE_NUMBER>
  <HIRE_DATE>21-MAY-91</HIRE_DATE>
  <JOB_ID>IT_PROG</JOB_ID>
  <SALARY>6000</SALARY>
  <MANAGER_ID>103</MANAGER_ID>
  <DEPARTMENT_ID>60</DEPARTMENT_ID>
 </ROW>
 
1 row selected.

The tag used for each row can be changed using the XMLFormat object.

Example 16-12 XMLSEQUENCE: Unnesting Collections in XML Documents into SQL Rows

Because SQL function XMLSequence is a table function, it can be used to unnest the elements inside an XML document. For example, consider the following XMLType table dept_xml_tab containing XML documents:

CREATE TABLE dept_xml_tab OF XMLType;
 
Table created.
 
INSERT INTO dept_xml_tab
  VALUES(
    XMLType('<Department deptno="100">
               <DeptName>Sports</DeptName>
               <EmployeeList>
                 <Employee empno="200"><Ename>John</Ename><Salary>33333</Salary>
                 </Employee>
                 <Employee empno="300"><Ename>Jack</Ename><Salary>333444</Salary>
                 </Employee>
               </EmployeeList>
             </Department>'));
 
1 row created.
 
INSERT INTO dept_xml_tab
  VALUES (
    XMLType('<Department deptno="200">
               <DeptName>Sports</DeptName>
               <EmployeeList>
                 <Employee empno="400"><Ename>Marlin</Ename><Salary>20000</Salary>
                 </Employee>
               </EmployeeList>
             </Department>'));
 
1 row created.
 
COMMIT;

You can use SQL function XMLSequence to unnest the Employee list items as top-level SQL rows:

SELECT extractValue(OBJECT_VALUE, '/Department/@deptno') AS deptno,
       extractValue(value(em), '/Employee/@empno') AS empno,
       extractValue(value(em), '/Employee/Ename') AS ename
  FROM dept_xml_tab, 
       table(XMLSequence(extract(OBJECT_VALUE,
                                 '/Department/EmployeeList/Employee'))) em;

This returns the following:

DEPTNO       EMPNO          ENAME
---------------------------------
100           200           John
100           300           Jack
200           400           Marlin

3 rows selected

For each row in table dept_xml_tab, function table is applied. Here, function extract creates a new XMLType instance that contains a fragment of all employee elements. This is fed to SQL function XMLSequence, which creates a collection of all employees.

Function TABLE then explodes the collection elements into multiple rows which are correlated with the parent table dept_xml_tab. Thus you get a list of all the parent dept_xml_tab rows with the associated employees.

Function extractValue extracts out the scalar values for the department number, employee number, and name.

Example 16-13 XMLCONCAT: Concatenating XMLType Instances from a Sequence

This example uses function XMLConcat to return a concatenation of XMLType instances from an XMLSequenceType value (a varray of XMLType instances).

SELECT XMLConcat(XMLSequenceType(
                   XMLType('<PartNo>1236</PartNo>'), 
                   XMLType('<PartName>Widget</PartName>'),
                   XMLType('<PartPrice>29.99</PartPrice>'))).getClobVal()
  AS "RESULT"
  FROM DUAL;

This query returns a single XML fragment (the actual output is not pretty-printed):

RESULT
---------------
<PartNo>1236</PartNo>
<PartName>Widget</PartName>
<PartPrice>29.99</PartPrice>

1 row selected.

Example 16-14 XMLCONCAT: Concatenating XML Elements

The following example creates an XML element for the first and the last names and then concatenates the result:

SELECT XMLConcat(XMLElement("first", e.first_name), 
                 XMLElement("last", e.last_name))
  AS "RESULT"
  FROM employees e;

This query produces the following XML fragment:

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.

Example 16-15 XMLAGG: Generating Department Elements with a List of Employee Elements

This example produces a Department element containing Employee elements with employee job ID and last name as the contents of the elements. It also orders the employee XML elements in the department by their last name. (The actual result is not pretty-printed.)

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.

The result is a single row, because XMLAgg aggregates the rows. You can use the GROUP BY clause to group the returned set of rows into multiple groups. (The actual result of the following query is not pretty-printed.)

SELECT XMLElement("Department", XMLAttributes(department_id AS "deptno"), 
                  XMLAgg(XMLElement("Employee", e.job_id||' '||e.last_name)))
   AS "Dept_list"
   FROM hr.employees e
   GROUP BY e.department_id;

Dept_list
------------------
<Department deptno="30">
  <Employee>PU_MAN Raphaely</Employee>
  <Employee>PU_CLERK Khoo</Employee>
  <Employee>PU_CLERK Baida</Employee>
  <Employee>PU_CLERK Himuro</Employee>
  <Employee>PU_CLERK Colmenares</Employee>
  <Employee>PU_CLERK Tobias</Employee>
</Department>

<Department deptno="40">
  <Employee>HR_REP Mavris</Employee>
</Department>

2 rows selected.

You can order the employees within each department by using the ORDER BY clause inside the XMLAgg expression.

Example 16-16 XMLAGG: Generating Nested Elements

Function XMLAgg can be used to reflect the hierarchical nature of some relationships that exist in tables. This example generates a department element for department 30. Within this element is a child element for each employee of the department. Within each employee element is a dependent element for each dependent of that employee.

First, this query shows the employees of department 30.

SELECT last_name, employee_id FROM employees WHERE department_id = 30;
 
LAST_NAME                 EMPLOYEE_ID
------------------------- -----------
Raphaely                          114
Khoo                              115
Baida                             116
Tobias                            117
Himuro                            118
Colmenares                        119
 
6 rows selected.
 

A dependents table is created, to hold the dependents of each employee.

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.
 

This query generates the XML data for department that contains the information on dependents (the actual output is not pretty-printed):

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.

Example 16-17 Using XMLPI

SELECT XMLPI(NAME "OrderAnalysisComp", 'imported, reconfigured, disassembled')
  AS pi FROM DUAL; 

This results in the following output :

PI
----------------------------------------------------------
<?OrderAnalysisComp imported, reconfigured, disassembled?>

1 row selected.

Example 16-18 Using XMLCOMMENT

SELECT XMLComment('This is a comment') AS cmnt FROM DUAL; 

This query results in the following output:

CMNT
--------------------------
<!--This is a comment-->

Example 16-19 Using XMLRoot

SELECT XMLRoot(XMLType('<poid>143598</poid>'), VERSION '1.0', STANDALONE YES)
  AS xmlroot FROM DUAL; 

This results in the following output :

XMLROOT
--------------------------------------
<?xml version="1.0" standalone="yes"?>
<poid>143598</poid>

1 row selected.

Example 16-20 Using XMLSERIALIZE

SELECT XMLSerialize(DOCUMENT XMLType('<poid>143598</poid>') AS CLOB)
  AS xmlserialize_doc FROM DUAL; 

This results in the following output:

XMLSERIALIZE_DOC
-------------------
<poid>143598</poid>

Example 16-21 Using XMLPARSE

SELECT XMLParse(CONTENT 
                '124 <purchaseOrder poNo="12435">
                       <customerName> Acme Enterprises</customerName>
                       <itemNo>32987457</itemNo>
                     </purchaseOrder>'
                WELLFORMED)
  AS po FROM DUAL d;

This results in the following output :

PO
-----------------------------------------------
124 <purchaseOrder poNo="12435">
<customerName>Acme Enterprises</customerName>
<itemNo>32987457</itemNo>
</purchaseOrder>

Example 16-22 XMLCOLATTVAL: Generating Elements with Attribute and Child Elements

This example generates an Emp element for each employee, with a name attribute and elements with the employee hire date and department as the content.

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;

This query produces the following XML result (the actual result is not pretty-printed):

RESULT
-----------------------------------------------------------
<Emp fullname="Den Raphaely">
  <column name = "HIRE_DATE">1994-12-07</column>
  <column name = "department">30</column>
</Emp>
<Emp fullname="Alexander Khoo">
  <column name = "HIRE_DATE">1995-05-18</column>
  <column name = "department">30</column>
</Emp>
<Emp fullname="Shelli Baida">
  <column name = "HIRE_DATE">1997-12-24</column>
  <column name = "department">30</column>
</Emp>
<Emp fullname="Sigal Tobias">
  <column name = "HIRE_DATE">1997-07-24</column>
  <column name = "department">30</column>
</Emp>
<Emp fullname="Guy Himuro">
  <column name = "HIRE_DATE">1998-11-15</column>
  <column name = "department">30</column>
</Emp>
<Emp fullname="Karen Colmenares">
  <column name = "HIRE_DATE">1999-08-10</column>
  <column name = "department">30</column>
</Emp>
 
6 rows selected.

Example 16-23 Using XMLCDATA

SELECT XMLElement("PurchaseOrder",
                  XMLElement("Address",
                             XMLCDATA('100 Pennsylvania Ave.'),
                             XMLElement("City", 'Washington, D.C.')))
  AS RESULT FROM DUAL;
                            

This results in the following output (the actual output is not pretty-printed):

RESULT
--------------------------
<PurchaseOrder>
  <Address>
    <![CDATA[100 Pennsylvania Ave.]]>
    <City>Washington, D.C.</City>
  </Address>
</PurchaseOrder>

Example 16-24 DBMS_XMLGEN: Generating Simple XML

This example creates an XML document by selecting employee data from an object-relational table and putting the resulting CLOB value into a table.

CREATE TABLE temp_clob_tab(result CLOB);

DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  result CLOB;
BEGIN
  qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM hr.employees');
  -- Set the row header to be EMPLOYEE
  DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE');
  -- Get the result
  result := DBMS_XMLGEN.getXML(qryCtx);
  INSERT INTO temp_clob_tab VALUES(result);
  --Close context
  DBMS_XMLGEN.closeContext(qryCtx);
END;
/

This query example generates the following XML (only part of the result is shown):

SELECT * FROM temp_clob_tab WHERE ROWNUM = 1;

RESULT
-------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <EMPLOYEE>
  <EMPLOYEE_ID>100</EMPLOYEE_ID>
  <FIRST_NAME>Steven</FIRST_NAME>
  <LAST_NAME>King</LAST_NAME>
  <EMAIL>SKING</EMAIL>
  <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
  <HIRE_DATE>17-JUN-87</HIRE_DATE>
  <JOB_ID>AD_PRES</JOB_ID>
  <SALARY>24000</SALARY>
  <DEPARTMENT_ID>90</DEPARTMENT_ID>
 </EMPLOYEE>

...
 
1 row selected.

Example 16-25 DBMS_XMLGEN: Generating Simple XML with Pagination (fetch)

Instead of generating all the XML data for all rows, you can use the fetch interface of DBMS_XMLGEN to retrieve a fixed number of rows each time. This speeds up response time and can help in scaling applications that need a Document Object Model (DOM) Application Program Interface (API) on the resulting XML, particularly if the number of rows is large.

The following example uses package DBMS_XMLGEN to retrieve results from table hr.employees:

-- Create a table to hold the results
CREATE TABLE temp_clob_tab(result clob);
DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  result CLOB;
BEGIN
  -- Get the query context;
  qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM hr.employees');
  -- Set the maximum number of rows to be 2
  DBMS_XMLGEN.setMaxRows(qryCtx, 2);
  LOOP
    -- Get the result
    result := DBMS_XMLGEN.getXML(qryCtx);
    -- If no rows were processed, then quit
    EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
 
    -- Do some processing with the lob data
    --   Here, we insert the results into a table.
    --   You can print the lob out, output it to a stream,
    --   put it in a queue, or do any other processing.
    INSERT INTO temp_clob_tab VALUES(result);
  END LOOP;
  --close context
  DBMS_XMLGEN.closeContext(qryCtx);
END;
/

SELECT * FROM temp_clob_tab WHERE rownum <3;

RESULT
----------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPLOYEE_ID>100</EMPLOYEE_ID>
  <FIRST_NAME>Steven</FIRST_NAME>
  <LAST_NAME>King</LAST_NAME>
  <EMAIL>SKING</EMAIL>
  <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
  <HIRE_DATE>17-JUN-87</HIRE_DATE>
  <JOB_ID>AD_PRES</JOB_ID>
  <SALARY>24000</SALARY>
  <DEPARTMENT_ID>90</DEPARTMENT_ID>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>101</EMPLOYEE_ID>
  <FIRST_NAME>Neena</FIRST_NAME>
  <LAST_NAME>Kochhar</LAST_NAME>
  <EMAIL>NKOCHHAR</EMAIL>
  <PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
  <HIRE_DATE>21-SEP-89</HIRE_DATE>
  <JOB_ID>AD_VP</JOB_ID>
  <SALARY>17000</SALARY>
  <MANAGER_ID>100</MANAGER_ID>
  <DEPARTMENT_ID>90</DEPARTMENT_ID>
 </ROW>
</ROWSET>
 
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPLOYEE_ID>102</EMPLOYEE_ID>
  <FIRST_NAME>Lex</FIRST_NAME>
  <LAST_NAME>De Haan</LAST_NAME>
  <EMAIL>LDEHAAN</EMAIL>
  <PHONE_NUMBER>515.123.4569</PHONE_NUMBER>
  <HIRE_DATE>13-JAN-93</HIRE_DATE>
  <JOB_ID>AD_VP</JOB_ID>
  <SALARY>17000</SALARY>
  <MANAGER_ID>100</MANAGER_ID>
  <DEPARTMENT_ID>90</DEPARTMENT_ID>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>103</EMPLOYEE_ID>
  <FIRST_NAME>Alexander</FIRST_NAME>
  <LAST_NAME>Hunold</LAST_NAME>
  <EMAIL>AHUNOLD</EMAIL>
  <PHONE_NUMBER>590.423.4567</PHONE_NUMBER>
  <HIRE_DATE>03-JAN-90</HIRE_DATE>
  <JOB_ID>IT_PROG</JOB_ID>
  <SALARY>9000</SALARY>
  <MANAGER_ID>102</MANAGER_ID>
  <DEPARTMENT_ID>60</DEPARTMENT_ID>
 </ROW>
</ROWSET>
 
2 rows selected.

Example 16-26 DBMS_XMLGEN: Generating Nested XML With Object Types

This example uses object types to represent nested structures.

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;

Here is the resulting 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.

With relational data, the result is an XML document without nested elements. To obtain nested XML structures, you can use object-relational data, where the mapping is as follows:

Example 16-27 DBMS_XMLGEN: Generating Nested XML With User-Defined Datatype Instances

When you provide a user-defined datatype instance to DBMS_XMLGEN functions, the user-defined datatype instance is mapped to an XML document using canonical mapping: the attributes of the user-defined datatype are mapped to XML elements. Attributes with names starting with an at-sign (@) are mapped to attributes of the preceding element.

User-defined datatype instances can be used for nesting in the resulting XML document. For example, consider tables, emp and dept:

CREATE TABLE dept(deptno NUMBER PRIMARY KEY, dname VARCHAR2(20));
CREATE TABLE emp(empno   NUMBER PRIMARY KEY, ename VARCHAR2(20), 
                 deptno  NUMBER REFERENCES dept);

To generate a hierarchical view of the data, that is, departments with employees in them, you can define suitable object types to create the structure inside the database as follows:

-- empno is preceded by an at-sign (@) to indicate that it must 
-- be mapped as an attribute of the enclosing Employee element. 
CREATE TYPE emp_t AS OBJECT("@empno" NUMBER,  -- empno defined as attribute
                             ename   VARCHAR2(20));
/
INSERT INTO DEPT VALUES(10, 'Sports');
INSERT INTO DEPT VALUES(20, 'Accounting');
INSERT INTO EMP VALUES(200, 'John',  10);
INSERT INTO EMP VALUES(300, 'Jack',  10);
INSERT INTO EMP VALUES(400, 'Mary',  20);
INSERT INTO EMP VALUES(500, 'Jerry', 20);
COMMIT;
CREATE TYPE emplist_t AS TABLE OF emp_t;
/
CREATE TYPE dept_t AS OBJECT("@deptno" NUMBER, 
                             dname     VARCHAR2(20),
                             emplist   emplist_t);
/
-- Department type dept_t contains a list of employees.
-- We can now query the employee and department tables and get 
-- the result as an XML document, as follows:
CREATE TABLE temp_clob_tab(result CLOB);
DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  RESULT CLOB;
BEGIN
  -- get query context
  qryCtx := DBMS_XMLGEN.newContext(
    'SELECT dept_t(deptno,
                   dname,
                   CAST(MULTISET(SELECT empno, ename
                                   FROM emp e
                                   WHERE e.deptno = d.deptno)
                        AS emplist_t))
       AS deptxml
       FROM dept d');
  -- set maximum number of rows to 5
  DBMS_XMLGEN.setMaxRows(qryCtx, 5);
  -- set no row tag for this result, since there is a single ADT column
  DBMS_XMLGEN.setRowTag(qryCtx, NULL);
  LOOP 
    -- get result
    result := DBMS_XMLGEN.getXML(qryCtx);
    -- if there were no rows processed, then quit
    EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
    -- do something with the result
    INSERT INTO temp_clob_tab VALUES (result);
  END LOOP;
END;
/

Function MULTISET treats the employees working in the department as a list, and function CAST assigns this list to the appropriate collection type. A department instance is created, and DBMS_XMLGEN routines create the XML for the object instance.

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.

The default name ROW is not present because we set that to NULL. The deptno and empno have become attributes of the enclosing element.

Example 16-28 DBMS_XMLGEN: Generating an XML Purchase Order

This example uses DBMS_XMLGEN.getXMLType() to generate a purchase order in XML format using object views.

-- Create relational schema and define object views
-- DBMS_XMLGEN maps user-defined datatype 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,