Skip Headers

Oracle® XML DB Developer's Guide
10g Release 1 (10.1)

Part Number B10790-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

15 Generating XML Data from the Database

This chapter describes Oracle XML DB options for generating XML from the database. It explains the SQL/XML standard functions and Oracle Database-provided functions and packages for generating XML data from relational content.

This chapter contains these topics:

Oracle XML DB Options for Generating XML Data From Oracle Database

Oracle Database supports native XML generation. Oracle provides you with several options for generating or regenerating XML data when stored in:

The following discussion illustrates the Oracle XML DB options you can use to generate XML from Oracle Database.

Generating XML Using SQL/XML Functions

The following SQL/XML functions are supported in Oracle XML DB:

Generating XML Using Oracle Database Extensions to SQL/XML

The following are Oracle Database extension functions to SQL/XML:

Generating XML Using DBMS_XMLGEN

Oracle XML DB supports DBMS_XMLGEN, a PL/SQL supplied package. DBMS_XMLGEN generates XML from SQL queries.

Generating XML Using SQL Functions

Oracle XML DB also supports the following Oracle Database-provided SQL functions that generate XML from SQL queries:

Generating XML with XSQL Pages Publishing Framework

Generating XML Using XSQL Pages Publishing Framework can also be used to generate XML from Oracle Database.

XSQL Pages Publishing Framework, also known as XSQL Servlet, is part of the XDK for Java.

Generating XML Using XML SQL Utility (XSU)

XML SQL Utility (XSU) enables you to perform the following tasks on data in XMLType tables and columns:

  • Transform data retrieved from object-relational database tables or views into XML.

  • Extract data from an XML document, and using a canonical mapping, insert the data into appropriate columns or attributes of a table or a view.

  • Extract data from an XML document and apply this data to updating or deleting values of the appropriate columns or attributes.

Generating XML from the Database Using SQL/XML Functions

XMLElement(), XMLForest(), XMLConcat(), and XMLAgg() belong to the SQL/XML standard, an emerging SQL standard for XML. Because these are emerging standards the syntax and semantics of these functions are subject to change in the future in order to conform to the standard. The SQL/XML standard is being developed under the auspices of INCITS Technical Committee H2, the USA committee responsible for SQL and SQL/MM. ("INCITS" stands for "International Committee for Information Technology Standards") INCITS is an Accredited Standards Development Organization operating under the policies and procedures of ANSI, the American National Standards Institute. SQL/XML is being developed as a new part (Part 14) of the SQL standard and is aligned with SQL:2003.

All of the generation functions convert scalars and user-defined types (UDTs) to their canonical XML format. In canonical mapping the user-defined type attributes are mapped to XML elements.

XMLElement() Function

XMLElement() function is based on the emerging SQL XML standard. It takes an element name, an optional collection of attributes for the element, and zero or more arguments that make up the element content and returns an instance of type XMLType. See Figure 15-1. The XML_attributes_clause is described in the following section.

Figure 15-1 XMLElement() Syntax

Description of XMLElement.gif follows
Description of the illustration XMLElement.gif

It is similar to SYS_XMLGEN(), but unlike SYS_XMLGEN(), XMLElement() does not create an XML document with the prolog (the XML version information). It allows multiple arguments and can include attributes in the XML returned.

XMLElement() is primarily used to construct XML instances from relational data. It takes an identifier that is partially escaped to give the name of the root XML element to be created. The identifier does not have to be a column name, or column reference, and cannot be an expression. If the identifier specified is NULL, then no element is returned.

As part of generating a valid XML element name from a SQL identifier, characters that are disallowed in an XML element name are escaped. With partial escaping the SQL identifiers other than the ":" sign that are not representable in XML, are preceded by an escape character using the # sign followed by the unicode representation of that character in hexadecimal format. This can be used to specify namespace prefixes for the elements being generated.

The fully escaped mapping escapes all non-XML characters in the SQL identifier name, including the ":" character.

XML_Attributes_Clause

XMLElement() also takes an optional XMLAttributes() clause, which specifies the attributes of that element. This can be followed by a list of values that make up the children of the newly created element. See Figure 15-2.

Figure 15-2 XMLAttributes Clause Syntax

Description of XML_attributes_clause.gif follows
Description of the illustration XML_attributes_clause.gif

In the XMLAttributes() clause, the value expressions are evaluated to get the values for the attributes. For a given value expression, if the AS clause is omitted, the fully escaped form of the column name is used as the name of the attribute. If the AS clause is specified, then the partially escaped form of the alias is used as the name of the attribute. If the expression evaluates to NULL, then no attribute is created for that expression. The type of the expression cannot be an object type or collection.

The list of values that follow the XMLAttributes() clause are converted to XML format, and are made as children of the top-level element. If the expression evaluates to NULL, then no element is created for that expression.

Example 15-1 XMLElement(): Generating an Element for Each Employee

The following example produces an Emp XML element for each employee, with the employee's name as its content:

SELECT e.employee_id, XMLELEMENT ("Emp", e.fname ||' '|| e.lname) AS "result"
   FROM employees e
   WHERE employee_id > 200;

-- This query produces the following typical result:
-- EMPLOYEE_ID    result
-- ---------------------
--        1001    <Emp>John Smith</Emp>
--        1206    <Emp>Mary Martin</Emp>

2 rows selected

XMLElement() can also be nested to produce XML data with a nested structure.

Example 15-2 XMLElement(): Generating Nested XML

To produce an Emp element for each employee, with elements that provide the employee's name and start date, do the following:

SELECT XMLELEMENT("Emp", XMLELEMENT("name", e.fname ||' '|| e.lname),
                           XMLELEMENT ( "hiredate", e.hire)) AS "result" 
FROM employees e 
WHERE employee_id > 200 ;

This query produces the following typical XML result:

result
-----------------
<Emp> 
  <name>John Smith</name>
  <hiredate>24-MAY-00</hiredate>
</Emp>
<Emp> 
  <name>Mary Martin</name>
  <hiredate>01-FEB-96</hiredate>
</Emp>

2 rows selected

If NLS_DATE_FORMAT is set to YYYY-MM-DD, then the date is in XML schema date format. The same query then produces this result:

result
-----------------
<Emp> 
  <name>John Smith</name>
  <hiredate>2000-05-24</hiredate>
</Emp>
<Emp> 
  <name>Mary Martin</name>
  <hiredate>1996-02-01</hiredate>
</Emp>

2 rows selected

Note:

Attributes, if they are specified, appear in the second argument of XMLElement() as:

"XMLATTRIBUTES (attribute, …)".


Example 15-3 XMLElement(): Generating an Element for Each Employee with ID and Name Attribute

This example produces an Emp element for each employee, with an id and name attribute:

SELECT XMLELEMENT ("Emp", XMLATTRIBUTES (
                             e.employee_id as "ID",
                             e.fname ||' ' || e.lname AS "name"))
    AS "result"
    FROM employees e
    WHERE employee_id > 200;

This query produces the following typical XML result fragment:

result
--------------
<Emp ID="1001" name="John Smith"/>
<Emp ID="1206" name="Mary Martin"/>

If the name of the element or attribute is being created from the ALIAS specified in the AS clause, then partially escaped mapping is used. If the name of the element or attribute is being created from a column reference, then fully escaped mapping is used. The following example illustrates these mappings:

SELECT XMLELEMENT ("Emp:Exempt", XMLATTRIBUTES (e.fname,
                                                 e.lname AS "name:last",
                                                 e."name:middle"))  
   AS "result"
   FROM employees e
   WHERE employee_id = 1001;

This query could produce the following XML result:

result
--------------
<Emp:Exempt FNAME="John" name:last="Smith" name_x003A_middle="Quincy"/>
</Emp:Exempt>

1 row selected.

Note:

XMLElement() does not validate the document produced with these namespace prefixes and it is the responsibility of the user to ensure that the appropriate namespace declarations are included as well. A full description of partial and full escaping has been specified as part of the emerging SQL XML standard.

Example 15-4 XMLElement(): Using Namespaces to Create a Schema-Based XML Document

The following example illustrates the use of namespaces to create an XML schema-based document. Assuming that an XML schema "http://www.oracle.com/Employee.xsd" exists and has no target namespace, then the following query creates an XMLType instance conforming to that schema:

SET LONG 2000
SELECT XMLELEMENT ("Employee", XMLATTRIBUTES (
                                  'http://www.w3.org/2001/XMLSchema' AS
                                          "xmlns:xsi",
                                  'http://www.oracle.com/Employee.xsd' AS
                                          "xsi:nonamespaceSchemaLocation"),
                    XMLForest(empno, ename, sal))
   AS "result"
   FROM scott.emp
   WHERE deptno = 10;

This creates an XML document that conforms to the Employee.xsd XMLSchema, result:

result
--------------
<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema"
          xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd">
   <EMPNO>7782</EMPNO>
   <ENAME>CLARK</ENAME>
   <SAL>2450</SAL>
</Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema"
          xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd">
   <EMPNO>7839</EMPNO>
   <ENAME>KING</ENAME>
   <SAL>5000</SAL>
</Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema"
          xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd">
   <EMPNO>7934</EMPNO>
   <ENAME>MILLER</ENAME>
   <SAL>1300</SAL>
</Employee>

3 rows selected.

Example 15-5 XMLElement(): Generating an Element from a User-Defined Type

Using the same example as given in the following XMLSequence() section for generating one XML document from another, you can generate a hierarchical XML for the employee, department example as follows:

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(deptno, dname,
                         CAST(MULTISET(select empno, ename from scott.emp e
                                       where e.deptno = d.deptno)
                              AS emplist_t)))
AS deptxml
FROM scott.dept d
WHERE d.deptno = 10;

This produces an XML document which contains the Department element and the canonical mapping of the dept_t type.

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.

XMLForest() Function

XMLForest() function produces a forest of XML elements from the given list of arguments. The arguments may be value expressions with optional aliases. Figure 15-3 describes the XMLForest() syntax.

Figure 15-3 XMLForest() Syntax

Description of XMLForest.gif follows
Description of the illustration XMLForest.gif

The list of value expressions are converted to XML format. For a given expression, if the AS clause is omitted, then the fully escaped form of the column name is used as the name of the enclosing tag of the element.

For an object type or collection, the AS clause is mandatory. For other types, the AS clause can be optionally specified. If the AS clause is specified, then the partially escaped form of the alias is used as the name of the enclosing tag. If the expression evaluates to NULL, then no element is created for that expression.

Example 15-6 XMLForest(): Generating Elements for Each Employee with Name Attribute, Start Date, and Dept as Content

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

SELECT XMLELEMENT("Emp", XMLATTRIBUTES (e.fname ||' '|| e.lname AS "name"),
                   XMLForest (e.hire, e.department AS "department"))
AS "result"
FROM employees e;

This query might produce the following XML result:

result
--------------
<Emp name="John Smith">
  <HIRE>24-MAY-00</HIRE>
  <department>Accounting</department>
</Emp>
<Emp name="Mary Martin">
  <HIRE>FEB-01-96</HIRE>
  <department>Shipping</department>
</Emp>

2 rows selected.

If NLS_DATE_FORMAT is set to YYYY-MM-DD, then the date is in XML schema date format. The same query then produces this result:

result
--------------
<Emp name="John Smith">
  <HIRE>2000-05-24</HIRE>
  <department>Accounting</department>
</Emp>
<Emp name="Mary Martin">
  <HIRE>1996-02-01</HIRE>
  <department>Shipping</department>
</Emp>

2 rows selected.

Example 15-7 XMLForest(): Generating an Element from an UDT

You can also use XMLForest() to generate XML from user-defined types (UDTs). Using the same example as given in the following DBMS_XMLGEN section on generating complex XML, you can generate a hierarchical XML for the employee, department example as follows:

SELECT XMLForest(
  dept_t(deptno,dname,
         CAST (MULTISET (select empno, ename
                         from scott.emp e
                         where e.deptno = d.deptno)
               AS emplist_t))
         AS "Department")
AS deptxml
FROM scott.dept d
WHERE deptno=10;

This produces an XML document that contains the Department element and the canonical mapping of the dept_t type.


Note:

Unlike in the XMLElement() case, the DEPT_T element is missing.

DEPTXML
-------------
<Department>
  <DEPT_T DEPTNO="10">
<DNAME>ACCOUNTING</DNAME>
    <EMP_LIST>
      <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>
    </EMP_LIST>
  </DEPT_T>
</Department>

1 row selected.

XMLSequence() Function

XMLSequence() function returns a sequence of XMLType. The function returns an XMLSequenceType which is a VARRAY of XMLType instances. Because this function returns a collection, it can be used in the FROM clause of SQL queries. See Figure 15-4.

Figure 15-4 XMLSequence() Syntax

Description of XMLSequence.gif follows
Description of the illustration XMLSequence.gif

XMLSequence() only returns top-level element nodes. That is, it will not shred attributes or text nodes. For example:

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.

The XMLSequence() function has two forms:

XMLSequence() is essential for effective SQL queries involving XMLTypes.

Example 15-8 XMLSequence(): Generating One XML Document from Another

Suppose you had the following XML document containing employee information:

<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>

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

SELECT SYS_XMLAGG(value(e), xmlformat('EMPLOYEES'))
  FROM TABLE(XMLSequence(Extract(doc, '/EMPLOYEES/EMP'))) e
  WHERE EXTRACTVALUE(value(e), '/EMP/SALARY') >= 50000;

This returns the following XML document:

<EMPLOYEES>
  <EMP>
     <EMPNO>112</EMPNO>
     <EMPNAME>Joe</EMPNAME>
     <SALARY>50000</SALARY>
  </EMP>
  <EMP>
     <EMPNO>217</EMPNO>
     <EMPNAME>Jane</EMPNAME>
     <SALARY>60000</SALARY>
  </EMP>
 </EMPLOYEES>

2 rows selected.

Notice how extract() was used to extract out all the employees:

  1. Extract() returns a fragment of EMP elements.

  2. XMLSequence() creates a collection of these top level elements into XMLType instances and returns that.

  3. The TABLE function was then used to makes the collection into a table value which can be used in the FROM clause of queries.

Example 15-9 XMLSequence(): Generating An XML Document for Each Row of a Cursor Expression, Using SYS_REFCURSOR Argument

Here XMLSequence() creates an XML document for each row of the cursor expression and returns the value as an XMLSequenceType. The XMLFormat object can be used to influence the structure of the resulting XML documents. For example, a call such as:

SELECT value(e).getClobVal() AS "xmltype"
FROM TABLE(XMLSequence(Cursor(SELECT * FROM scott.emp))) e;

might return the following XML:

xmltype
---------------------------------
  <ROW>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>17-DEC-80</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>

  <ROW>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>20-FEB-81</HIREDATE>
    <SAL>1600</SAL>
    <DEPTNO>30</DEPTNO>
  </ROW>
...
14 rows selected.

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

Example 15-10 XMLSequence(): Unnesting Collections in XML Documents into SQL Rows

XMLSequence() being a TABLE function, can be used to unnest the elements inside an XML document. For example, suppose you have XML documents such as the following stored in an XMLType table dept_xml_tab:

<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>

<Department deptno="200">
  <DeptName>Garment</DeptName>
  <EmployeeList>
    <Employee empno="400">
      <Ename>Marlin</Ename>
      <Salary>20000</Salary>
    </Employee>
  </EmployeeList>
</Department>

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

CREATE TABLE dept_xml_tab OF XMLType;

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>'));
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>'));

COMMIT;
SELECT extractvalue(value(d),'/Department/@deptno') as deptno,
       extractvalue(value(e),'/Employee/@empno') as empno,
       extractvalue(value(e),'/Employee/Ename') as ename
  FROM dept_xml_tab d, TABLE(XMLSequence(extract(value(d),
                             '/Department/EmployeeList/Employee'))) e;

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, the TABLE function is evaluated. Here, the extract() function creates a new XMLType instance that contains a fragment of all employee elements. This is fed to the XMLSequence() which creates a collection of all employees.

The TABLE function 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.

The extractValue() functions extract out the scalar values for the department number, employee number, and name.

XMLConcat() Function

XMLConcat() function concatenates all the arguments passed in to create a XML fragment. Figure 15-5 shows the XMLConcat() syntax. XMLConcat() has two forms:

Figure 15-5 XMLConcat() Syntax

Description of XMLConcat.gif follows
Description of the illustration XMLConcat.gif

Example 15-11 XMLConcat(): Returning a Concatenation of XML Elements Used in the Argument Sequence

This example shows XMLConcat() returning the concatenation of XMLTypes from the XMLSequenceType:

SELECT XMLConcat(XMLSequenceType(xmltype('<PartNo>1236</PartNo>'), 
                                 xmltype('<PartName>Widget</PartName>'),
                                 xmltype('<PartPrice>29.99</PartPrice>')
                )).getClobVal()
  AS "result"
  FROM dual;

returns a single fragment of the form:

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

1 row selected.

Example 15-12 XMLConcat(): Returning XML Elements By Concatenating the Elements in the Arguments

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

SELECT XMLConcat(XMLElement("first", e.fname), 
                 XMLElement ("last", e.lname))
  AS "result"
  FROM employees e;

This query might produce the following XML document:

result
---------------
<first>Mary</first>
<last>Martin</last>
<first>John</first>
<last>Smith</last>

2 rows selected.

XMLAgg() Function

XMLAgg() is an aggregate function that produces a forest of XML elements from a collection of XML elements. Figure 15-6 describes the XMLAgg() syntax, where the order_by_clause is:

ORDER BY [list of: expr [ASC|DESC] [NULLS {FIRST|LAST}]]

and number literals are not interpreted as column positions. For example, ORDER BY 1 does not mean order by the first column. Instead the number literals are interpreted just as any other literal.

As with XMLConcat(), any arguments that are null are dropped from the result. XMLAgg() function is similar to the SYS_XMLAGG() function except that it returns a forest of nodes, and does not take the XMLFormat() parameter. This function can be used to concatenate XMLType instances across multiple rows. It also allows an optional ORDER BY clause to order the XML values being aggregated.

XMLAgg() is an aggregation function and hence produces one aggregated XML result for each group. If there is no group by specified in the query, then it returns a single aggregated XML result for all the rows of the query.

Figure 15-6 XMLAgg() Syntax

Description of XMLAgg.gif follows
Description of the illustration XMLAgg.gif

Example 15-13 XMLAgg(): Generating Department Elements with a List of Employee Elements

The following 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.

SELECT XMLELEMENT("Department", XMLAGG(XMLELEMENT("Employee",
                                                  e.job||' '||e.ename)
                                       ORDER BY e.ename))
  AS "Dept_list"     
  FROM scott.emp e
  WHERE e.deptno = 10;

Dept_list
------------------
<Department>
  <Employee>MANAGER CLARK</Employee>
  <Employee>PRESIDENT KING</Employee>
  <Employee>CLERK MILLER</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:

SELECT XMLELEMENT("Department", XMLAttributes(deptno AS "deptno"), 
                  XMLAgg(XMLElement("Employee", e.job||' '||e.ename)))
   AS "Dept_list"
   FROM scott.emp e
   GROUP BY e.deptno;

Dept_list
------------------
<Department deptno="10">
  <Employee>MANAGER CLARK</Employee>
  <Employee>PRESIDENT KING</Employee>
  <Employee>CLERK MILLER</Employee>
</Department>

<Department deptno="20">
  <Employee>CLERK SMITH</Employee>
  <Employee>ANALYST FORD</Employee>
  <Employee>CLERK ADAMS</Employee>
  <Employee>ANALYST SCOTT</Employee>
  <Employee>MANAGER JONES</Employee>
</Department>

<Department deptno="30">
  <Employee>SALESMAN ALLEN</Employee>
  <Employee>MANAGER BLAKE</Employee>
  <Employee>SALESMAN MARTIN</Employee>
  <Employee>SALESMAN TURNER</Employee>
  <Employee>CLERK JAMES</Employee>
  <Employee>SALESMAN WARD</Employee>
</Department>

3 rows selected.

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


Note:

Within the order_by_clause, Oracle Database does not interpret number literals as column positions, as it does in other uses of this clause, but simply as number literals.

Example 15-14 XMLAgg(): Generating Department Elements, Employee Elements in Each Department, and Employee Dependents

XMLAgg() can be used to reflect the hierarchical nature of some relationships that exist in tables. The following example generates a department element for each department. Within this, it creates elements for all employees of the department. Within each employee, it lists the employee dependents:

CREATE TABLE scott.dependents (id NUMBER(4) PRIMARY KEY,
                                empno NUMBER(4),
                                name VARCHAR2(10));
INSERT INTO scott.dependents values (1, 7369, 'MARK');
INSERT INTO scott.dependents values (2, 7369, 'JACK');
INSERT INTO scott.dependents values (3, 7499, 'JANE');
INSERT INTO scott.dependents values (4, 7521, 'HELLEN');
INSERT INTO scott.dependents values (5, 7521, 'FRANK');
INSERT INTO scott.dependents values (6, 7566, 'JANUS');
INSERT INTO scott.dependents values (7, 7654, 'KATE');
INSERT INTO scott.dependents values (8, 7654, 'JEFF');
INSERT INTO scott.dependents values (9, 7654, 'JENNIFER');
INSERT INTO scott.dependents values (10, 7654, 'JOHN');
INSERT INTO scott.dependents values (11, 7698, 'BUSH');
INSERT INTO scott.dependents values (12, 7782, 'BUSH W');
INSERT INTO scott.dependents values (13, 7788, 'WALLACE');
INSERT INTO scott.dependents values (14, 7788, 'FRED');
INSERT INTO scott.dependents values (15, 7839, 'GALE');
INSERT INTO scott.dependents values (16, 7839, 'GARY');
INSERT INTO scott.dependents values (17, 7876, 'JOE');
INSERT INTO scott.dependents values (18, 7902, 'NAISON');
INSERT INTO scott.dependents values (19, 7902, 'JOYCE');
INSERT INTO scott.dependents values (20, 7902, 'NAISON');
INSERT INTO scott.dependents values (21, 7934, 'JOHNSON');
INSERT INTO scott.dependents values (22, 7934, 'BUCKS');

COMMIT;

SELECT
  XMLELEMENT(
    "Department",
    XMLATTRIBUTES(d.dname AS "name"),
    (SELECT
       XMLAGG(XMLELEMENT("emp", 
                         XMLATTRIBUTES(e.ename AS name),
                         (SELECT XMLAGG(XMLELEMENT("dependent",
                                        XMLATTRIBUTES(de.name AS "name")))
                          FROM dependents de
                          WHERE de.empno = e.empno)))
     FROM emp e
     WHERE e.deptno = d.deptno)) AS "dept_list"
   FROM dept d;

The query might produce a row containing the XMLType instance for each department:

dept_list
----------------------
<Department name="ACCOUNTING">
  <emp NAME="CLARK">
    <dependent name="BUSH W"/dependent>
  </emp>
  <emp NAME="KING"/>
    <dependent name="GALE"/dependent>
    <dependent name="GARY"/dependent>
  </emp>
  <emp NAME="MILLER">
    <dependent name="JOHNSON"/dependent>
    <dependent name="BUCKS"/dependent>
  </emp>
</Department>
<Department name="RESEARCH">
  <emp NAME="SMITH">
    <dependent name="MARK"/dependent>
    <dependent name="JACK"/dependent>
  </emp>
  <emp NAME="JONES">
    <dependent name="JANUS"/dependent>
  </emp>
  <emp NAME="SCOTT">
    <dependent name="WALLACE"/dependent>
    <dependent name="FRED"/dependent>
  </emp>
  <emp NAME="ADAMS">
    <dependent name="JOE"/dependent>
  </emp>
  <emp NAME="FORD">
    <dependent name="NAISON"/dependent>
    <dependent name="JOYCE"/dependent>
  </emp>
</Department>
<Department name="SALES">
  <emp NAME="ALLEN">
    <dependent name="JANE"/dependent>
    <dependent name="HELLEN"/dependent>
  </emp>
  <emp NAME="WARD">
    <dependent name="FRANK"/dependent>
  </emp>
  <emp NAME="MARTIN">
    <dependent name="KATE"/dependent>
    <dependent name="JEFF"/dependent>
    <dependent name="JENNIFER"/dependent>
    <dependent name="JOHN"/dependent>
  </emp>
  <emp NAME="BLAKE">
    <dependent name="BUSH"/dependent>
  </emp>
  <emp NAME="TURNER"></emp>
  <emp NAME="JAMES"></emp>
</Department>
<Department name="OPERATIONS"></Department>

4 rows selected.

XMLColAttVal() Function

XMLColAttVal() function generates a forest of XML column elements containing the value of the arguments passed in. This function is an Oracle Database extension to the SQL/XML ANSI-ISO standard functions. Figure 15-7 shows the XMLColAttVal() syntax.

Figure 15-7 XMLColAttVal() Syntax

Description of XMLColattval.gif follows
Description of the illustration XMLColattval.gif

The name of the arguments are put in the name attribute of the column element. Unlike the XMLForest() function, the name of the element is not escaped in any way and hence this function can be used to transport SQL columns and values without escaped names.

Example 15-15 XMLColAttVal(): Generating an Emp Element For Each Employee with Name Attribute and Elements with Start Date and Dept as Content

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

SELECT XMLELEMENT("Emp", XMLATTRIBUTES(e.fname ||' '||e.lname AS "name" ),
                  XMLCOLATTVAL(e.hire, e.department AS "department"))
  AS "result" 
  FROM employees e;

This query might produce the following XML result:

result
-------------
<Emp name="John Smith">
   <column name="HIRE">24-MAY-00</column>
   <column name="department">Accounting</column>
</Emp>
<Emp name="Mary Martin">
   <column name="HIRE">01-FEB-96</column>
   <column name="department">Shipping</column>
</Emp>

2 rows selected.

Because the name associated with each XMLColAttVal() argument is used to populate an attribute value, neither the fully escaped mapping nor the partially escaped mapping is used.

If NLS_DATE_FORMAT is set to YYYY-MM-DD, then the date is in XML schema date format. The same query then produces this result:

result
--------------
<Emp name="John Smith">
  <column name="HIRE">2000-05-24</column>
  <column name="department">Accounting</column>
</Emp>
<Emp name="Mary Martin">
  <column name="HIRE">1996-02-01</column>
  <column name="department">Shipping</column>
</Emp>

2 rows selected.

Generating XML from Oracle Database Using DBMS_XMLGEN

DBMS_XMLGEN creates XML documents from any SQL query by mapping the database query results into XML. It gets the XML document as a CLOB or XMLType. It provides a fetch interface whereby you can specify the maximum rows and rows to skip. This is useful for pagination requirements in Web applications. DBMS_XMLGEN also provides options for changing tag names for ROW, ROWSET, and so on.

The parameters of the package can restrict the number of rows retrieved, the enclosing tag names. To summarize, DBMS_XMLGEN PL/SQL package allows you:

Sample DBMS_XMLGEN Query Result

The following shows a sample result from executing a SELECT * FROM scott.emp query on a database:

<?xml version="1.0"?>
<ROWSET>
<ROW>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>17-DEC-80</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
</ROW>
<ROW>
  <EMPNO>7499</EMPNO>
  <ENAME>ALLEN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>20-FEB-81</HIREDATE>
  <SAL>1600</SAL>
  <COMM>300</COMM>
  <DEPTNO>30</DEPTNO>
</ROW>
...
</ROWSET>

The result of the getXML() using DBMS_XMLGen package is a CLOB. The default mapping is as follows:

  • Every row of the query result maps to an XML element with the default tag name ROW.

  • The entire result is enclosed in a ROWSET element. These names are both configurable, using the setRowTagName() and setRowSetTagName() procedures in DBMS_XMLGEN.

  • Each column in the SQL query result, maps as a subelement of the ROW element.

  • Binary data is transformed to its hexadecimal representation.

When the document is in a CLOB, it has the same encoding as the database character set. If the database character set is SHIFTJIS, then the XML document is SHIFTJIS.

DBMS_XMLGEN Calling Sequence

Figure 15-8 summarizes the DBMS_XMLGEN calling sequence.

Figure 15-8 DBMS_XMLGEN Calling Sequence

Description of adxdk108.gif follows
Description of the illustration adxdk108.gif

Here is the DBMS_XMLGEN calling sequence:

  1. Get the context from the package by supplying a SQL query and calling the newContext() call.

  2. Pass the context to all procedures or functions in the package to set the various options. For example, to set the ROW element name, use setRowTag(ctx), where ctx is the context got from the previous newContext() call.

  3. Get the XML result, using the getXML() or getXMLType(). By setting the maximum rows to be retrieved for each fetch using the setMaxRows() call, you can call this function repeatedly, getting the maximum number of row set for each call. The function returns null if there are no rows left in the query.

    getXML() and getXMLType() always return an XML document, even if there were no rows to retrieve. If you want to know if there were any rows retrieved, then use the function getNumRowsProcessed().

  4. You can reset the query to start again and repeat step 3.

  5. Close the closeContext() to free up any resource allocated inside.

Table 15-1 summarizes DBMS_XMLGEN functions and procedures.

Table 15-1 DBMS_XMLGEN Functions and Procedures

Function or Procedure Description

DBMS_XMLGEN Type definitions

SUBTYPE ctxHandle IS NUMBER

The context handle used by all functions.

Document Type Definition (DTD) or schema specifications:

NONE CONSTANT NUMBER:= 0; -- supported for this release.

DTD CONSTANT NUMBER:= 1;

SCHEMA CONSTANT NUMBER:= 2;

Can be used in getXML function to specify whether to generate a DTD or XML Schema or none. Only the NONE specification is supported in the getXML functions for this release.

FUNCTION PROTOTYPES

newContext()

Given a query string, generate a new context handle to be used in subsequent functions.

FUNCTION

newContext(queryString IN VARCHAR2)

Returns a new context

PARAMETERS: queryString (IN)- the query string, the result of which must be converted to XML

RETURNS: Context handle. Call this function first to obtain a handle that you can use in the getXML() and other functions to get the XML back from the result.

FUNCTION

newContext(queryString IN SYS_REFCURSOR) RETURN ctxHandle;

Creates a new context handle from a passed in PL/SQL ref cursor. The context handle can be used for the rest of the functions. See the example:

FUNCTION

newContextFromHierarchy(queryString IN VARCHAR2) RETURN ctxHandle;This is new in Oracle Database 10g release 1 (10.0.1).

Returns a new context

PARAMETERS: queryString (IN)- the query string, the result of which must be converted to XML. The query is a hierarchical query typically formed using a CONNECT BY clause, and the result must have the same property as the result set generated by a CONNECT BY query. The result set must have only two columns, the level number and an XML value. The level number is used to determine the hierarchical position of the XML value within the result XML document.

RETURNS: Context handle. Call this function first to obtain a handle that you can use in the getXML() and other functions to get a hierarchical XML with recursive elements back from the result.

setRowTag()

Sets the name of the element separating all the rows. The default name is ROW.

PROCEDURE

setRowTag(ctx IN ctxHandle,rowTag IN VARCHAR2);

PARAMETERS:

ctx(IN) - the context handle obtained from the newContext call,

rowTag(IN) - the name of the ROW element. NULL indicates that you do not want the ROW element to be present. Call this function to set the name of the ROW element, if you do not want the default ROW name to show up. You can also set this to NULL to suppress the ROW element itself. Its an error if both the row and the rowset are NULL and there is more than one column or row in the output.

setRowSetTag()

Sets the name of the document root element. The default name is ROWSET

PROCEDURE

setRowSetTag(ctx IN ctxHandle, rowSetTag IN VARCHAR2);

PARAMETERS:

ctx(IN) - the context handle obtained from the newContext call,

rowSetTag(IN) - the name of the document element. NULL indicates that you do not want the ROW element to be present. Call this to set the name of the document root element, if you do not want the default ROWSET name in the output. You can also set this to NULL to suppress the printing of this element. However, this is an error if both the ROW and the ROWSET are NULL and there is more than one column or row in the output.

getXML() Gets the XML document by fetching the maximum number of rows specified. It appends the XML document to the CLOB passed in.
PROCEDURE

getXML(ctx IN ctxHandle, clobval IN OUT NCOPY clob, dtdOrSchema IN number:= NONE);

PARAMETERS:

ctx(IN) - The context handle obtained from the newContext() call,

clobval(IN/OUT) - the CLOB to which the XML document is to be appended,

dtdOrSchema(IN) - whether you should generate the DTD or Schema. This parameter is NOT supported.

Use this version of the getXML function, to avoid any extra CLOB copies and if you want to reuse the same CLOB for subsequent calls. This getXML() call is more efficient than the next flavor, though this involves that you create the lob locator. When generating the XML, the number of rows indicated by the setSkipRows call are skipped, then the maximum number of rows as specified by the setMaxRows call (or the entire result if not specified) is fetched and converted to XML. Use the getNumRowsProcessed function to check if any rows were retrieved or not.

getXML()

Generates the XML document and returns it as a CLOB.
FUNCTION

getXML(ctx IN ctxHandle, dtdOrSchema IN number:= NONE) RETURN clob;

PARAMETERS: ctx(IN) - The context handle obtained from the newContext() call,

dtdOrSchema(IN) - whether we should generate the DTD or Schema. This parameter is NOT supported.

RETURNS: A temporary CLOB containing the document. Free the temporary CLOB obtained from this function using the dbms_lob.freetemporary call.

FUNCTION

getXMLType(ctx IN ctxHandle, dtdOrSchema IN number:= NONE) RETURN XMLType;

PARAMETERS: ctx(IN) - The context handle obtained from the newContext() call,

dtdOrSchema(IN) - whether we should generate the DTD or Schema. This parameter is NOT supported.

RETURNS: An XMLType instance containing the document.

FUNCTION

getXML(sqlQuery IN VARCHAR2, dtdOrSchema IN NUMBER := NONE) RETURN CLOB;
Converts the query results from the passed in SQL query string to XML format, and returns the XML as a CLOB.
FUNCTION

getXMLType(sqlQuery IN VARCHAR2, dtdOrSchema IN NUMBER := NONE) RETURN XMLType;

Converts the query results from the passed in SQL query string to XML format, and returns the XML as a CLOB.

getNumRowsProcessed()

Gets the number of SQL rows processed when generating the XML using the getXML call. This count does not include the number of rows skipped before generating the XML.
FUNCTION

getNumRowsProcessed(ctx IN ctxHandle) RETURN number;

PARAMETERS: queryString(IN)- the query string, the result of which needs to be converted to XML RETURNS:

This gets the number of SQL rows that were processed in the last call to getXML. You can call this to find out if the end of the result set has been reached. This does not include the number of rows skipped. Use this function to determine the terminating condition if you are calling getXML in a loop. Note that getXML would always generate a XML document even if there are no rows present.

setMaxRows()

Sets the maximum number of rows to fetch from the SQL query result for every invocation of the getXML call. It is an error to call this function on a context handle created by newContextFromHierary() function
PROCEDURE

setMaxRows(ctx IN ctxHandle, maxRows IN NUMBER);

PARAMETERS: ctx(IN) - the context handle corresponding to the query executed,

maxRows(IN) - the maximum number of rows to get for each call to getXML.

The maxRows parameter can be used when generating paginated results using this utility. For instance when generating a page of XML or HTML data, you can restrict the number of rows converted to XML and then in subsequent calls, you can get the next set of rows and so on. This also can provide for faster response times. It is an error to call this procedure on a context handle created by newContextFromHierary() function

setSkipRows()

Skips a given number of rows before generating the XML output for every call to the getXML() routine. It is an error to call this function on a context handle created by newContextFormHierarchy() function
PROCEDURE

setSkipRows(ctx IN ctxHandle, skipRows IN NUMBER);

PARAMETERS:ctx(IN) - the context handle corresponding to the query executed,

skipRows(IN) - the number of rows to skip for each call to getXML.

The skipRows parameter can be used when generating paginated results for stateless web pages using this utility. For instance when generating the first page of XML or HTML data, you can set skipRows to zero. For the next set, you can set the skipRows to the number of rows that you got in the first case. It is an error to call this function on a context handle created by newContextFromHierarchy() function.

setConvertSpecialChars()

Sets whether special characters in the XML data need to be converted into their escaped XML equivalent or not. For example, the "<" sign is converted to &lt;. The default is to perform conversions.
PROCEDURE

setConvertSpecialChars(ctx IN ctxHandle, conv IN boolean);

PARAMETERS: ctx(IN) - the context handle to use,

conv(IN) - true indicates that conversion is needed.

You can use this function to speed up the XML processing whenever you are sure that the input data cannot contain any special characters such as <, >, ", ' , and so on, which must be preceded by an escape character. Note that it is expensive to actually scan the character data to replace the special characters, particularly if it involves a lot of data. So in cases when the data is XML-safe, then this function can be called to improve performance.

useItemTagsForColl()

Sets the name of the collection elements. The default name for collection elements it the type name itself. You can override that to use the name of the column with the _ITEM tag appended to it using this function.
PROCEDURE

useItemTagsForColl(ctx IN ctxHandle);

PARAMETERS: ctx(IN) - the context handle.

If you have a collection of NUMBER, say, the default tag name for the collection elements is NUMBER. You can override this action and generate the collection column name with the _ITEM tag appended to it, by calling this procedure.

restartQuery()

Restarts the query and generate the XML from the first row again.
PROCEDURE

restartQuery(ctx IN ctxHandle);

PARAMETERS: ctx(IN) - the context handle corresponding to the current query. You can call this to start executing the query again, without having to create a new context.

closeContext()

Closes a given context and releases all resources associated with that context, including the SQL cursor and bind and define buffers, and so on.
PROCEDURE

closeContext(ctx IN ctxHandle);

PARAMETERS: ctx(IN) - the context handle to close. Closes all resources associated with this handle. After this you cannot use the handle for any other DBMS_XMLGEN function call.
Conversion Functions

FUNCTION

convert(xmlData IN varchar2, flag IN NUMBER := ENTITY_ENCODE) return varchar2;

Encodes or decodes the passed in XML data string.

  • Encoding refers to replacing entity references such as '<' to their escaped equivalent, such as '&lt;'.

  • Decoding refers to the reverse conversion.

FUNCTION

convert(xmlData IN CLOB, flag IN NUMBER := ENTITY_ENCODE) return CLOB;

Encodes or decodes the passed in XML CLOB data.
  • Encoding refers to replacing entity references such as '<' to their escaped equivalent, such as '&lt;'.

  • Decoding refers to the reverse conversion.

NULL Handling

PROCEDURE

setNullHandling(ctx IN ctxHandle, flag IN NUMBER);

This is new in Oracle9i release 2 (9.2.0.2).

The setNullHandling flag values are:
  • DROP_NULLS CONSTANT NUMBER := 0; This is the default setting and leaves out the tag for null elements.

  • NULL_ATTR CONSTANT NUMBER := 1; This sets xsi:nil="true".

  • EMPTY_TAG CONSTANT NUMBER := 2; This sets, for example, <foo/>.

PROCEDURE

useNullAttributeIndicator(ctx IN ctxHandle, attrind IN boolean := TRUE);

This is new in Oracle9i release 2 (9.2.0.2).

useNullAttributeIndicator is a short-cut for setNullHandling(ctx, NULL_ATTR).
PROCEDURE setBindValue(ctx IN ctxHandle, bindVariableName IN VARCHAR2, bindValue IN VARCHAR2);This is new in Oracle Database 10g release 1 (10.0.1). This procedure allows one to set bind value for the bind variable appearing in the query string associated with the context handle. The query string with bind variables cannot be executed until all the bind variables are set values using setBindValue() call.
PROCEDURE

clearBindValue(ctx IN ctxHandle);This is new in Oracle Database 10g release 1 (10.0.1).

This procedure clears all the bind values for all the bind variables appearing in the query string associated with the context handle. Afterwards, all the bind variables have to rebind new values using setBindValue() call.

Example 15-16 DBMS_XMLGEN: Generating Simple XML

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

CREATE TABLE temp_clob_tab(result CLOB);

DECLARE
   qryCtx DBMS_XMLGEN.ctxHandle;
   result CLOB;
BEGIN
  qryCtx := dbms_xmlgen.newContext('SELECT * from scott.emp');

  -- set the row header to be EMPLOYEE
  DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE');

  -- now 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:

SELECT * FROM temp_clob_tab;

RESULT
------------------------------------
<?xml version="1.0"?>
<ROWSET>
  <EMPLOYEE>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>17-DEC-80</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </EMPLOYEE>
  <EMPLOYEE>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>20-FEB-81</HIREDATE>
    <SAL>1600</SAL>
    <COMM>300</COMM>
    <DEPTNO>30</DEPTNO>
  </EMPLOYEE>
  ...
</ROWSET>

Example 15-17 DBMS_XMLGEN: Generating Simple XML with Pagination

Instead of generating all the XML for all rows, you can use the fetch interface that DBMS_XMLGEN provides to retrieve a fixed number of rows each time. This speeds up response time and also 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 illustrates how to use DBMS_XMLGEN to retrieve results from table scott.emp:

-- 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 scott.emp');
  -- set the maximum number of rows to be 5
  dbms_xmlgen.setMaxRows(qryCtx, 5);
  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;
/

Here, for each set of 5 rows, you generate an XML document.

Example 15-18 DBMS_XMLGEN: Generating Complex XML

Complex XML can be generated using 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, 'Marry', 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);
/
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>Marry</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>

Now, you can select the LOB data from the temp_clob_Tab table and verify the results. The result looks like the sample result shown in section "Sample DBMS_XMLGEN Query Result".

With relational data, the results are a flat non-nested XML document. To obtain nested XML structures, you can use object-relational data, where the mapping is as follows:

  • Object types map as an XML element -- see Chapter 5, " XML Schema Storage and Query: The Basics".

  • Attributes of the type, map to sub-elements of the parent element


    Note:

    Complex structures can be obtained by using object types and creating object views or object tables. A canonical mapping is used to map object instances to XML.

    The @ sign, when used in column or attribute names, is translated into an attribute of the enclosing XML element in the mapping.


Example 15-19 DBMS_XMLGEN: Generating Complex XML #2 - Inputting User Defined Types For Nested XML Documents

When you enter a user-defined type (UDT) value to DBMS_XMLGEN functions, the user-defined type is mapped to an XML document using canonical mapping. In the canonical mapping, user-defined type attributes are mapped to XML elements. Attributes with names starting with "@" are mapped to attributes of the preceding element.

User-defined types 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 defined with '@' in front 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));
/
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:
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
    -- ...
  END LOOP;
END;
/

The MULTISET operator treats the result of the subset of employees working in the department, as a list and the CAST around it, assigns it to the appropriate collection type. You then create a department instance around it and call the DBMS_XMLGEN routines to create the XML for the object instance. The result is:

<?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">
   <! .. other columns >
   </DEPTXML>
 </ROWSET>

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

Example 15-20 DBMS_XMLGEN: Generating a Purchase Order from the Database in XML Format

This example uses DBMS_XMLGEN.getXMLType() to generate PurchaseOrder in XML format from a relational database using object views. Note that the example is five pages long.

-- Create relational schema and define Object views
-- Note: DBMS_XMLGEN Package maps UDT attribute names
--       starting with '@' 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 nested 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))
ORGANIZATION INDEX OVERFLOW;

-- 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) /* PO in XML format */
/

--------------------
-- 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 DBMS_XMLGEN Package to generate purchase order in XML format
-- and store XMLType in po_xml table
-------------------------------------------------------
DECLARE
   qryCtx DBMS_XMLGEN.ctxHandle;
   pxml XMLType;
   cxml CLOB;
BEGIN
  -- get query context;
  qryCtx := DBMS_XMLGEN.newContext('SELECT pono,deref(cust_ref) customer,
                                           p.OrderDate,
                                           p.shipdate,
                                           lineitems_ntab lineitems,
                                           shiptoaddr
                                    FROM po p');
  -- set maximum number of rows to be 1,
  DBMS_XMLGEN.setMaxRows(qryCtx, 1);
  -- set ROWSET tag to null and ROW tag to PurchaseOrder
  DBMS_XMLGEN.setRowSetTag(qryCtx, NULL);
  DBMS_XMLGEN.setRowTag(qryCtx,'PurchaseOrder');
  LOOP 
    -- get purchase order in XML format
    pxml := DBMS_XMLGEN.getXMLType(qryCtx);
    -- if there were no rows processed, then quit
    EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
    -- Store XMLType po in po_xml table (get the pono out)
    INSERT INTO po_xml_tab(poid, poDoc)
      VALUES(pxml.extract('//PONO/text()').getNumberVal(), pxml);
  END LOOP;
END;
/
---------------------------
-- list XML PurchaseOrders
---------------------------
SET LONG 100000
SET PAGES 100
SELECT x.podoc.getClobVal() xpo FROM po_xml_tab x;

This produces the following purchase-order XML documents:

PurchaseOrder 1001:

<?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>
      <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 2001:

<?xml version="1.0"?>
  <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>

Example 15-21 DBMS_XMLGEN: Generating a New Context Handle from a Passed-in PL/SQL Ref Cursor

CREATE OR REPLACE FUNCTION joe3 RETURN CLOB
IS 
  ctx1    NUMBER := 2; 
  ctx2    NUMBER; 
  xmldoc  CLOB; 
  page    NUMBER := 0; 
  xmlpage BOOLEAN := TRUE; 
  refcur  SYS_REFCURSOR;
BEGIN 
   OPEN refcur FOR 'SELECT * FROM emp WHERE ROWNUM < :1' USING ctx1;
   ctx2 := DBMS_XMLGEN.newContext(refcur);
   ctx1 := 4;
   OPEN refcur FOR 'SELECT * FROM emp WHERE ROWNUM < :1' USING ctx1;
   ctx1 := 5;
   OPEN refcur FOR 'SELECT * FROM emp WHERE ROWNUM < :1' USING ctx1;
   DBMS_LOB.createtemporary(xmldoc, TRUE);   
    -- xmldoc will have 4 rows
   xmldoc := DBMS_XMLGEN.getXML(ctx2, DBMS_XMLGEN.NONE); 
   DBMS_XMLGEN.closeContext(ctx2);
   RETURN xmldoc;
END; 
/

Example 15-22 DBMS_XMLGEN: Specifying Null Handling

SQL> @tkxmgnd10
SQL> CONNECT system/manager
Connected.
SQL> 
SQL> GRANT CONNECT, RESOURCE TO gnd10 IDENTIFIED BY gnd10;
Grant succeeded.
SQL> CONNECT gnd10/gnd10
Connected.
SQL> SET SERVEROUTPUT ON SIZE 200000
SQL> -- null_handle: 1 => NULL_ATTR, 2 => EMPTY_TAG
SQL> CREATE OR REPLACE FUNCTION getXML(sql_query VARCHAR2, null_handle NUMBER,
  2                                    rset_tag VARCHAR2 := 'ROWSET',
  3                                    rtag VARCHAR2 := 'ROW')
  4  RETURN CLOB IS
  5    ctx NUMBER;
  6    xmldoc CLOB;
  7  BEGIN
  8    ctx := DBMS_XMLGEN.newContext(sql_query);
  9  
 10    IF (nvl(rset_tag, 'X') != 'ROWSET') THEN
 11       DBMS_XMLGEN.setRowSetTag(ctx, rset_tag);
 12    END IF;
 13  
 14    IF (nvl(rtag, 'Y') != 'ROW') THEN
 15       DBMS_XMLGEN.setRowTag(ctx, rtag);
 16    END IF;
 17  
 18    DBMS_XMLGEN.setNullHandling(ctx, null_handle);
 19  
 20    xmldoc := DBMS_XMLGEN.getXML(ctx);
 21    DBMS_XMLGEN.closeContext(ctx);
 22    RETURN xmldoc;
 23  END;
 24  /
Function created.

Example 15-23 DBMS_XMLGEN : Generating a Hierarchical XML Document Containing Recursive Elements From the Result of a Hierarchical Query (Formed by a CONNECT BY Clause)

The DBMS_XMLGEN package contains the function newContextFromHierarchy(). It takes a hierarchical query string, which is typically formulated with a CONNECT BY clause, as an argument and returns a context to be used to generate a hierarchical XML document with recursive elements.

The hierarchical query returns two columns, the level number (a pseudo-column generated by CONNECT BY query) and an XMLType. The level is used to determine the position of the XMLType value within the hierarchy of the result XML document.

Setting skip number of rows or maximum number of rows for a context created from newContextFromHierarchy() is an error.

For example, you can generate a Manager employee hierarchy by using DBMS_ XMLGEN.newContextFormHierarchy().

SQL> set serveroutput on size 200000
SQL> set long 200000
SQL>

SQL> create table sqlx_display(id number, xmldoc XMLType);

Table created.

SQL>
SQL> -- Test 2: XMLelement with scott schema
SQL> declare
  2          qryctx dbms_xmlgen.ctxhandle;
  3          result XMLType;
  4  begin
  5    qryctx := dbms_xmlgen.newcontextFromHierarchy(
  6    'select level, xmlelement("emp", xmlelement("enumber", empno),
  7           xmlelement("name", ename),
  8           xmlelement("Salary", sal),
  9           xmlelement("Hiredate", hiredate)) from scott.emp
 10          start with ename=''KING'' connect by prior empno=mgr
 11          order siblings by hiredate');
 12    result := dbms_xmlgen.getxmltype(qryctx);
 13    dbms_output.put_line('<result num rows>');
 14    dbms_output.put_line(to_char(dbms_xmlgen.getNumRowsProcessed(qryctx
)));
 15    dbms_output.put_line('</result num rows>');
 16    insett into sqlx_display values  (2, result);
 17    commit;
 18    dbms_xmlgen.closecontext(qryctx);
 19  end;
 20  /
<result num rows>
14
</result num rows>

PL/SQL procedure successfully completed.

SQL>
SQL> select xmldoc from sqlx_display where id = 2;

XMLDOC
------------------------------------------------------------------------
<?xml version="1.0"?>
<emp>
  <enumber>7839</enumber>
  <name>KING</name>
  <Salary>5000</Salary>
  <Hiredate>17-NOV-81</Hiredate>
  <emp>
    <enumber>7566</enumber>
    <name>JONES</name>
    <Salary>2975</Salary>
    <Hiredate>02-APR-81</Hiredate>
    <emp>
      <enumber>7902</enumber>
      <name>FORD</name>
      <Salary>3000</Salary>
      <Hiredate>03-DEC-81</Hiredate>
      <emp>
        <enumber>7369</enumber>
        <name>SMITH</name>
        <Salary>800</Salary>
        <Hiredate>17-DEC-80</Hiredate>
      </emp>
    </emp>
    <emp>
      <enumber>7788</enumber>
      <name>SCOTT</name>
      <Salary>3000</Salary>
      <Hiredate>19-APR-87</Hiredate>
      <emp>
        <enumber>7876</enumber>
        <name>ADAMS</name>
        <Salary>1100</Salary>
        <Hiredate>23-MAY-87</Hiredate>
      </emp>
    </emp>
  </emp>
  <emp>
    <enumber>7698</enumber>
    <name>BLAKE</name>
    <Salary>2850</Salary>
    <Hiredate>01-MAY-81</Hiredate>
    <emp>
      <enumber>7499</enumber>
      <name>ALLEN</name>
      <Salary>1600</Salary>
      <Hiredate>20-FEB-81</Hiredate>
    </emp>
    <emp>
      <enumber>7521</enumber>
      <name>WARD</name>
      <Salary>1250</Salary>
      <Hiredate>22-FEB-81</Hiredate>
    </emp>
    <emp>
      <enumber>7844</enumber>
      <name>TURNER</name>
      <Salary>1500</Salary>
      <Hiredate>08-SEP-81</Hiredate>
    </emp>
    <emp>
      <enumber>7654</enumber>
      <name>MARTIN</name>
      <Salary>1250</Salary>
      <Hiredate>28-SEP-81</Hiredate>
    </emp>
    <emp>
      <enumber>7900</enumber>
      <name>JAMES</name>
      <Salary>950</Salary>
      <Hiredate>03-DEC-81</Hiredate>
    </emp>
  </emp>
  <emp>
    <enumber>7782</enumber>
    <name>CLARK</name>
    <Salary>2450</Salary>
    <Hiredate>09-JUN-81</Hiredate>
    <emp>
      <enumber>7934</enumber>
      <name>MILLER</name>
      <Salary>1300</Salary>
      <Hiredate>23-JAN-82</Hiredate>
    </emp>
  </emp>
</emp>

1 row selected.

By default, the RowSet tag is NULL. That is, there is no default rowset tag used to enclose the XML result. However, you can explicitly set the rowset tag by using the setRowSetTag() procedure, as follows:

SQL>
SQL> create table gg(x XMLType);

Table created.
SQL> declare
  2          qryctx dbms_xmlgen.ctxhandle;
  3          result clob;
  4  begin
  5          qryctx := dbms_xmlgen.newcontextFromHierarchy('select level,
  6                  xmlelement("NAME", name) as myname from tc
  7                  connect by prior id = pid start with id = 1');
  8          dbms_xmlgen.setRowSetTag(qryctx, 'mynum_hierarchy');
  9          result:=dbms_xmlgen.getxml(qryctx);
 10          
 11          dbms_output.put_line('<result num rows>');
 12          dbms_output.put_line(to_char(dbms_xmlgen.getNumRowsProcessed(qryctx
)));
 13          dbms_output.put_line('</result num rows>');
 14          insert into gg values(xmltype(result));
 15          commit;
 16          dbms_xmlgen.closecontext(qryctx);
 17  end;
 18  /
<result num rows>
6
</result num rows>

PL/SQL procedure successfully completed.

SQL> select * from gg;

X
------------------------------------------------------------------------------
<?xml version="1.0"?>
<mynum_hierarchy>
  <NAME>top
    <NAME>second1
      <NAME>third3</NAME>
    </NAME>
    <NAME>second2
      <NAME>third1</NAME>
      <NAME>third2</NAME>
    </NAME>
  </NAME>
</mynum_hierarchy>

Example 15-24 DBMS_XMLGEN : Using setBindValue() to Bind Variables in the Query String for DBMS_XMLGEN

The query string that is used to create context can contain host variables and then bind values to it by using setBindValue() before the execution of the query.

SET SERVEROUTPUT ON SIZE 200000
SET LONG 200000
 
-- bind one variable 
DECLARE
  2  ctx NUMBER;
  3  xmldoc CLOB;
  4  BEGIN
  5    ctx := DBMS_XMLGEN.newContext('SELECT * FROM emp WHERE empno = :NO');
  6
  7    DBMS_XMLGEN.setBindValue(ctx, 'NO', '7369');
  8    xmldoc := DBMS_XMLGEN.getXML(ctx);
  9    printClobOut(xmldoc);
 10    DBMS_XMLGEN.closeContext(ctx);
 11  EXCEPTION
 12    WHEN OTHERS THEN
 13    DBMS_XMLGEN.closeContext(ctx);
 14    RAISE;
 15  END;
 16  /
| <?xml version="1.0"?>
| <ROWSET>
|   <ROW>
|     <EMPNO>7369</EMPNO>
|     <ENAME>SMITH</ENAME>
|     <JOB>CLERK</JOB>
|     <MGR>7902</MGR>
|     <HIREDATE>17-DEC-80</HIREDATE>
|     <SAL>800</SAL>
|     <DEPTNO>20</DEPTNO>
|   </ROW>
| </ROWSET>
 
SQL> --bind one variable twice with different values
SQL> DECLARE
  2  ctx NUMBER;
  3  xmldoc CLOB;
  4  BEGIN
  5    ctx := DBMS_XMLGEN.newContext('SELECT * FROM emp
                                      WHERE hiredate = :MDATE');
  6
  7    DBMS_XMLGEN.setBindValue(ctx, 'MDATE', '17-DEC-80');
  8    xmldoc := DBMS_XMLGEN.getXML(ctx);
  9    printClobOut(xmldoc);
 10
 11    DBMS_XMLGEN.setBindValue(ctx, 'MDATE', '20-FEB-81');
 12    xmldoc := DBMS_XMLGEN.getXML(ctx);
 13    printClobOut(xmldoc);
 14    DBMS_XMLGEN.closeContext(ctx);
 15  EXCEPTION
 16    WHEN OTHERS THEN
 17    DBMS_XMLGEN.closeContext(ctx);
 18    RAISE;
 19  END;
 20  /
| <?xml version="1.0"?>
| <ROWSET>
|   <ROW>
|     <EMPNO>7369</EMPNO>
|     <ENAME>SMITH</ENAME>
|     <JOB>CLERK</JOB>
|     <MGR>7902</MGR>
|     <HIREDATE>17-DEC-80</HIREDATE>
|     <SAL>800</SAL>
|     <DEPTNO>20</DEPTNO>
|   </ROW>
| </ROWSET>
| <?xml version="1.0"?>
| <ROWSET>
|   <ROW>
|     <EMPNO>7499</EMPNO>
|     <ENAME>ALLEN</ENAME>
|     <JOB>SALESMAN</JOB>
|     <MGR>7698</MGR>
|     <HIREDATE>20-FEB-81</HIREDATE>
|     <SAL>1600</SAL>
|     <COMM>300</COMM>
|     <DEPTNO>30</DEPTNO>
|   </ROW>
| </ROWSET>
 
PL/SQL procedure successfully completed.
SQL> -- bind two variables 
SQL> DECLARE
  2  ctx NUMBER;
  3  xmldoc CLOB;
  4  BEGIN
  5    ctx := DBMS_XMLGEN.newContext('SELECT * FROM emp
  6                                   WHERE empno = :NO
  7                                     AND hiredate = :MDATE');
  8    DBMS_XMLGEN.setBindValue(ctx, 'NO', '7369');
  9    DBMS_XMLGEN.setBindValue(ctx, 'MDATE', '17-DEC-80');
 10    xmldoc := DBMS_XMLGEN.getXML(ctx);
 11    printClobOut(xmldoc);
 12    DBMS_XMLGEN.closeContext(ctx);
 13  EXCEPTION
 14    WHEN OTHERS THEN
 15    DBMS_XMLGEN.closeContext(ctx);
 16    RAISE;
 17  END;
 18  /
| <?xml version="1.0"?>
| <ROWSET>
|   <ROW>
|     <EMPNO>7369</EMPNO>
|     <ENAME>SMITH</ENAME>
|     <JOB>CLERK</JOB>
|     <MGR>7902</MGR>
|     <HIREDATE>17-DEC-80</HIREDATE>
|     <SAL>800</SAL>
|     <DEPTNO>20</DEPTNO>
|   </ROW>
| </ROWSET>

Generating XML Using Oracle Database-Provided SQL Functions

In addition to the SQL standard functions, Oracle Database provides the SYS_XMLGEN and SYS_XMLAGG functions to aid in generating XML.

SYS_XMLGEN() Function

This Oracle Database-specific SQL function is similar to the XMLElement() except that it takes a single argument and converts the result to XML. Unlike the other XML generation functions, SYS_XMLGEN() always returns a well-formed XML document. Unlike DBMS_XMLGEN which operates at a query level, SYS_XMLGEN() operates at the row level returning a XML document for each row.

Example 15-25 Using SQL_XMLGEN to Create XML

SYS_XMLGEN() creates and queries XML instances in SQL queries, as follows:

SELECT SYS_XMLGEN(employee_id) AS "result"
   FROM employees WHERE fname LIKE 'John%';

The resulting XML document is:

result
---------------
<?xml version="1.0"?>
<EMPLOYEE_ID>1001</EMPLOYEE_ID>

1 row selected.

SYS_XMLGEN Syntax

SYS_XMLGEN() takes in a scalar value, object type, or XMLType instance to be converted to an XML document. It also takes an optional XMLFormat (the old name was XMLGenFormatType) object that you can use to specify formatting options for the resulting XML document. See Figure 15-9.

Figure 15-9 SYS_XMLGEN Syntax

Description of sys_xmlgen.gif follows
Description of the illustration sys_xmlgen.gif

SYS_XMLGEN() takes an expression that evaluates to a particular row and column of the database, and returns an instance of type XMLType containing an XML document. The expr can be a scalar value, a user-defined type, or a XMLType instance.

By default the elements of the XML document match the elements of expr. For example, if expr resolves to a column name, then the enclosing XML element will have the same name as the column. If you want to format the XML document differently, then specify fmt, which is an instance of the XMLFormat object.

The formatting argument for SYS_XMLGEN() accepts the schema and element name, and generates the XML document conforming to that registered schema.

CREATE OR REPLACE TYPE scott.emp_t AS OBJECT(EMPNO NUMBER(4),
                                              ENAME VARCHAR2(10),
                                              JOB VARCHAR2(9),
                                              MGR NUMBER(4),
                                              HIREDATE DATE,
                                              SAL NUMBER(7, 2),
                                              COMM NUMBER(7,2));
/
CREATE OR REPLACE TYPE scott.emplist_t AS TABLE OF emp_t;
/
CREATE OR REPLACE TYPE scott.dept_t AS OBJECT(DEPTNO NUMBER(2),
                                              DNAME VARCHAR2(14),
                                              LOC VARCHAR2(13),
                                              EMPLIST EMPLIST_T);
/
SELECT SYS_XMLGEN(dept_t(d.deptno, d.dname, d.loc,
                         CAST(MULTISET(
                                SELECT emp_t(e.empno, e.ename, e.job, e.mgr,
                                             e.hiredate, e.sal, e.comm)
                                FROM scott.emp e 
                                WHERE e.deptno = d.deptno)
                              AS emplist_t)), 
                  xmlformat.createformat('Department'))
FROM scott.dept d;
SELECT SYS_XMLGEN(x) FROM table_name WHERE x IS NOT NULL;

Suppressing <ROW/> Tags

To suppress <ROW/> tags with SYS_XMLGEN() if you do not want NULL values represented, just use a WHERE clause: as follows:

SELECT sys_xmlgen(x) from table_name WHERE x is NOT NULL;

Example 15-26 SYS_XMLGEN(): Retrieving Employee fname From Employees Table and Generating XML with FNAME Element

The following example retrieves the employee fname from the sample table oe.employees where the employee_id value is 205, and generates an instance of a XMLType containing an XML document with an FNAME element.

SELECT SYS_XMLGEN(fname).getStringVal()      
   FROM employees
   WHERE employee_id = 1001;

SYS_XMLGEN(FNAME).GETSTRINGVAL()
----------------------------------------
<?xml version="1.0"?>
<FNAME>John</FNAME>

What are the Advantages of Using SYS_XMLGEN(?

SYS_XMLGEN() is powerful for the following reasons:

SYS_XMLGEN() creates an XML document from either of the following:

and returns an XMLType instance contained in the document.

SYS_XMLGEN() also optionally inputs a XMLFormat object type through which you can customize the SQL results. A NULL format object implies that the default mapping action is to be used.

Using XMLFormat Object Type

You can use XMLFormat to specify formatting arguments for SYS_XMLGEN() and SYS_XMLAGG() functions.

SYS_XMLGEN() returns an instance of type XMLType containing an XML document. Oracle Database provides the XMLFormat object, which lets you format the output of the SYS_XMLGEN function.

Table 15-2 lists the XMLFormat attributes. of the XMLFormat object. The function that implements this type follows the table.

Table 15-2 Attributes of the XMLFormat Object

Attribute Datatype Purpose
enclTag VARCHAR2(100) The name of the enclosing tag for the result of the SYS_XMLGEN function. If the input to the function is a column name, then the default is the column name. Otherwise the default is ROW. When schemaType is set to USE_GIVEN_SCHEMA, this attribute also gives the name of the XMLSchema element.
schemaType VARCHAR2(100) The type of schema generation for the output document. Valid values are 'NO_SCHEMA' and 'USE_GIVEN_SCHEMA'. The default is 'NO_SCHEMA'.
schemaName VARCHAR2(4000) The name of the target schema Oracle Database uses if the value of the schemaType is 'USE_GIVEN_SCHEMA'. If you specify schemaName, then Oracle Database uses the enclosing tag as the element name.
targetNameSpace VARCHAR2(4000) The target namespace if the schema is specified (that is, schemaType is GEN_SCHEMA_*, or USE_GIVEN_SCHEMA)
dburl VARCHAR2(2000) The URL to the database to use if WITH_SCHEMA is specified. If this attribute is not specified, then Oracle Database declares the URL to the types as a relative URL reference.
processingIns VARCHAR2(4000) User-provided processing instructions, which are appended to the top of the function output before the element.

Example 15-27 Creating a Formatting Object with createFormat

You can use the static member function createformat to implement the XMLFormat object. This function has most of the values defaulted. For example:

STATIC FUNCTION createFormat(enclTag IN VARCHAR2 := 'ROWSET',
                             schemaType IN VARCHAR2 := 'NO_SCHEMA',
                             schemaName IN VARCHAR2 := NULL,
                             targetNameSpace IN VARCHAR2 := NULL,
                             dburlPrefix IN VARCHAR2 := NULL, 
                             processingIns IN VARCHAR2 := NULL)
RETURN 
  XMLGenFormatType,
  MEMBER PROCEDURE genSchema (spec IN VARCHAR2),
  MEMBER PROCEDURE setSchemaName(schemaName IN VARCHAR2),
  MEMBER PROCEDURE setTargetNameSpace(targetNameSpace IN VARCHAR2),
  MEMBER PROCEDURE setEnclosingElementName(enclTag IN VARCHAR2), 
  MEMBER PROCEDURE setDbUrlPrefix(prefix IN VARCHAR2),
  MEMBER PROCEDURE setProcessingIns(pi IN VARCHAR2),
  CONSTRUCTOR FUNCTION XMLGenFormatType(enclTag IN VARCHAR2 := 'ROWSET',
                                        schemaType IN VARCHAR2 := 'NO_SCHEMA',
                                        schemaName IN VARCHAR2 := NULL,
                                        targetNameSpace IN VARCHAR2 := NULL,
                                        dbUrlPrefix IN VARCHAR2 := NULL, 
                                        processingIns IN VARCHAR2 := NULL)
RETURN SELF AS RESULT

Note:

XMLFormat object is the new name for XMLGenFormatType. You can use either name.

Example 15-28 SYS_XMLGEN(): Converting a Scalar Value to an XML Document Element Contents

When you enter a scalar value to SYS_XMLGEN(), it converts the scalar value to an element containing the scalar value. For example:

SELECT SYS_XMLGEN(empno) FROM scott.emp WHERE ROWNUM < 2;

returns an XML document that contains the empno value as an element, as follows:

SYS_XMLGEN(EMPNO)
-------------------------
<?xml version="1.0"?>
<EMPNO>7369</EMPNO>

1 row selected.

The enclosing element name, in this case EMPNO, is derived from the column name passed to the operator. Also, note that the result of the SELECT statement is a row containing a XMLType.

Example 15-29 Generating Default Column Name, ROW

In the last example, you used the column name EMPNO for the document. If the column name cannot be derived directly, then the default name ROW is used. For example, in the following case:

SELECT SYS_XMLGEN(empno).getclobval()
FROM scott.emp 
WHERE ROWNUM < 2;

you get the following XML output:

SYS_XMLGEN(EMPNO).GETCLOBVAL()
--------------------------------------
<?xml version="1.0"?>
<EMPNO>7369</EMPNO>

because the function cannot infer the name of the expression. You can override the default ROW tag by supplying an XMLFormat (the old name was "XMLGenFormatType") object to the first argument of the operator.

Example 15-30 Overriding the Default Column Name: Supplying an XMLFormat Object to the Operator's First Argument

For example, in the last case, if you wanted the result to have EMPNO as the tag name, then you can supply a formatting argument to the function, as follows:

SELECT SYS_XMLGEN(empno *2,
                  xmlformat.createformat('EMPNO')).getClobVal()
FROM scott.emp
WHERE ROWNUM < 2;

This results in the following XML:

SYS_XMLGEN(EMPNO*2,XMLFORMAT.CREATEFORMAT('EMPNO')).GETCLOBVAL()
--------------------------------------------------------------------
<?xml version="1.0"?>
<EMPNO>14738</EMPNO>

1 row selected.

Example 15-31 SYS_XMLGEN(): Converting a User-Defined Type to XML

When you enter a user-defined type value to SYS_XMLGEN(), the user-defined type gets mapped to an XML document using a canonical mapping. In the canonical mapping the user-defined type attributes are mapped to XML elements.

Any type attributes with names starting with @ are mapped to an attribute of the preceding element. User-defined types can be used to get nesting within the result XML document.

Using the same example as given in the DBMS_XMLGEN section, you can generate a hierarchical XML for the employee, department example as follows:

CREATE OR REPLACE TYPE scott.emp_t AS OBJECT(empno    NUMBER(4),
                                             ename    VARCHAR2(10),
                                             job      VARCHAR2(9),
                                             mgr      NUMBER(4),
                                             hiredate DATE,
                                             sal      NUMBER(7, 2),
                                             comm     NUMBER(7, 2));
/
CREATE OR REPLACE TYPE scott.emplist_t AS TABLE OF emp_t;
/
CREATE OR REPLACE TYPE scott.dept_t AS OBJECT(deptno  NUMBER(2),
                                              dname   VARCHAR2(14),
                                              loc     VARCHAR2(13),
                                              emplist emplist_t);
/
SELECT SYS_XMLGEN(dept_t(deptno, dname, d.loc,
                         CAST(MULTISET(SELECT emp_t(e.empno, e.ename, e.job,
                                                    e.mgr, e.hiredate, e.sal,
                                                    e.comm)
                                       FROM scott.emp e
                                       WHERE e.deptno = d.deptno)
                              AS emplist_t))).getClobVal()
AS deptxml
FROM scott.dept d;

The MULTISET operator treats the result of the subset of employees working in the department as a list and the CAST around it, assigns it to the appropriate collection type. You then create a department instance around it and call SYS_XMLGEN() to create the XML for the object instance.

The result is:

DEPTXML
------------------
<?xml version="1.0"?>
<ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>Accounting</DNAME>
  <LOC>NEW YORK</LOC>
  <EMPLIST>
    <EMP_T>
      <EMPNO>7782</EMPNO>
      <ENAME>CLARK</ENAME>
      <JOB>MANAGER</JOB>
      <MGR>7839</MGR>
      <HIREDATE>09-JUN-81</HIREDATE>
      <SAL>2450</SAL>
    </EMP_T>
    <EMP_T>
      <EMPNO>7839</EMPNO>
      <ENAME>KING</ENAME>
      <JOB>PRESIDENT</JOB>
      <HIREDATE>17-NOV-81</HIREDATE>
      <SAL>5000</SAL>
    </EMP_T>
  </EMPLIST>
</ROW>
<?xml version="1.0"?>
<ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <LOC>DALLAS</LOC>
  <EMPLIST>
    <EMP_T>
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>
      <HIREDATE>17-DEC-80</HIREDATE>
      <SAL>800</SAL>
    </EMP_T>
    
    <EMP_T>
      <EMPNO>7566</EMPNO>
      <ENAME>JONES</ENAME>
      <JOB>MANAGER</JOB>
      <MGR>7839</MGR>
      <HIREDATE>02-APR-87</HIREDATE>
      <SAL>3000</SAL>
    </EMP_T>
    <EMP_T>
      <EMPNO>7788</EMPNO>
      <ENAME>SCOTT</ENAME>
      <JOB>ANALYST</JOB>
      <MGR>7566</MGR>
      <HIREDATE>19-APR-87</HIREDATE>
      <SAL>3000</SAL>
    </EMP_T>
    <EMP_T>
      <EMPNO>7876</EMPNO>
      <ENAME>ADAMS</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7788</MGR>
      <HIREDATE>23-MAY-87</HIREDATE>
      <SAL>1100</SAL>
    </EMP_T>
    <EMP_T>
      <EMPNO>7902</EMPNO>
      <ENAME>FORD</ENAME>
      <JOB>ANALYST</JOB>
      <MGR>7566</MGR>
      <HIREDATE>03-DEC-81</HIREDATE>
      <SAL>3000</SAL>
    </EMP_T>
</EMPLIST>
</ROW>
<?xml version="1.0"?>
<ROW>
  <DEPTNO>30</DEPTNO>
  <DNAME>SALES</DNAME>
  <LOC>CHICAGO</LOC>
  <EMPLIST>
    <EMP_T>
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>20-FEB-81</HIREDATE>
      <SAL>1600</SAL>
      <COMM>300</COMM>
    </EMP_T>
    
    <EMP_T>
      <EMPNO>7521</EMPNO>
      <ENAME>WARD</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>22-FEB-81</HIREDATE>
      <SAL>1250</SAL>
      <COMM>500</COMM>
    </EMP_T>
    <EMP_T>
      <EMPNO>7654</EMPNO>
      <ENAME>MARTIN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>28-SEP-81</HIREDATE>
      <SAL>1250</SAL>
      <COMM>1400</COMM>
    </EMP_T>
    <EMP_T>
      <EMPNO>7698</EMPNO>
      <ENAME>BLAKE</ENAME>
      <JOB>MANAGER</JOB>
      <MGR>7839</MGR>
      <HIREDATE>01-MAY-81</HIREDATE>
      <SAL>2850</SAL>
    </EMP_T>
    <EMP_T>
      <EMPNO>7844</EMPNO>
      <ENAME>TURNER</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>08-SEP-81</HIREDATE>
      <SAL>1500</SAL>
      <COMM>0</COMM>
    </EMP_T>
    <EMP_T>
      <EMPNO>7900</EMPNO>
      <ENAME>JAMES</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7698</MGR>
      <HIREDATE>03-DEC-81</HIREDATE>
      <SAL>950</SAL>
    </EMP_T>
  </EMPLIST>
</ROW>

<?xml version="1.0"?>
<ROW>
 <DEPTNO>40</DEPTNO>
 <DNAME>OPERATIONS</DNAME>
 <LOC>BOSTON</LOC>
 <EMPLIST/>
</ROW>

4 rows selected.

The default name ROW is present because the function cannot deduce the name of the input operand directly.


Note:

The difference between SYS_XMLGEN() function and DBMS_XMLGEN package is apparent from the preceding example:
  • SYS_XMLGEN works inside SQL queries and operates on the expressions and columns within the rows

  • DBMS_XMLGEN works on the entire result set


Example 15-32 SYS_XMLGEN(): Converting an XMLType Instance

If you pass an XML document into SYS_XMLGEN(), then SYS_XMLGEN() encloses the document (or fragment) with an element, whose tag name is the default ROW, or the name passed in through the formatting object. This functionality can be used to turn document fragments into well formed documents.

For example, the extract() operation on the following document, can return a fragment. If you extract out the EMPNO elements from the following document:

<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>

using the following statement:

SELECT e.podoc.extract('/DOCUMENT/EMPLOYEE/ENAME') 
  FROM po_xml_tab e;

then you get an XML document fragment such as the following:

<ENAME>John</ENAME>
<ENAME>Jack</ENAME>
<ENAME>Joseph</ENAME>

You can make this fragment a valid XML document, by calling SYS_XMLGEN() to put an enclosing element around the document, as follows:

SELECT SYS_XMLGEN(e.podoc.extract('/DOCUMENT/EMPLOYEE/ENAME')).getclobval()
  FROM po_xml_tab e;

This places an element ROW around the result, as follows:

<?xml version="1.0"?>
<ROW>
  <ENAME>John</ENAME>
  <ENAME>Jack</ENAME>
  <ENAME>Joseph</ENAME>
</ROW>

Note:

If the input was a column, then the column name would have been used as default. You can override the enclosing element name using the formatting object that can be passed in as an additional argument to the function. See "Using XMLFormat Object Type".

Example 15-33 SYS_XMLGEN(): Using SYS_XMLGEN() with Object Views

-- 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
-------------------------------------------------------
SET LONG 20000
SET PAGES 100
SELECT 
  SYS_XMLGEN(value(p),
             sys.xmlformat.createFormat('PurchaseOrder')).getClobVal() PO
  FROM po p 
  WHERE p.pono=1001;

This returns the Purchase Order in XML format:

<?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>

SYS_XMLAGG() Function

SYS_XMLAGG() function aggregates all XML documents or fragments represented by expr and produces a single XML document. It adds a new enclosing element with a default name, ROWSET. To format the XML document differently, use the 'fmt' parameter.

Figure 15-10 SYS_XMLAGG() Syntax

Description of sys_xmlagg.gif follows
Description of the illustration sys_xmlagg.gif

Generating XML Using XSQL Pages Publishing Framework

Oracle9i introduced XMLType for use with storing and querying XML-based database content. You can use these database XML features to produce XML for inclusion in your XSQL pages by using the <xsql:include-xml> action element.

The SELECT statement that appears inside the <xsql:include-xml> element should return a single row containing a single column. The column can either be a CLOB or a VARCHAR2 value containing a well-formed XML document. The XML document will be parsed and included in your XSQL page.

Example 15-34 Using XSQL Servlet <xsql:include-xml> and Nested XMLAgg() Functions to Aggregate the Results Into One XML Document

The following example uses nested xmlagg() functions to aggregate the results of a dynamically-constructed XML document containing departments and nested employees into a single XML result document, wrapped in a <DepartmentList> element:

<xsql:include-xml connection="orcl92" xmlns:xsql="urn:oracle-xsql">
  select XmlElement("DepartmentList",
           XmlAgg(
             XmlElement("Department", 
               XmlAttributes(deptno as "Id"),
               XmlForest(dname as "Name"),
               (select XmlElement("Employees",
                         XmlAgg( 
                           XmlElement("Employee",
                             XmlAttributes(empno as "Id"),
                             XmlForest(ename as "Name",
                                       sal   as "Salary",
                                       job   as "Job")
                           )
                         )
                       )
                 from emp e 
                where e.deptno = d.deptno
               )
             )
           )
         ).getClobVal()
   from dept d
  order by dname
</xsql:include-xml>

Example 15-35 Using XSQL Servlet <xsql:include-xml, XMLElement(), and XMLAgg() to Generate XML from Oracle Database

Because it is more efficient for the database to aggregate XML fragments into a single result document, the <xsql:include-xml> element encourages this approach by only retrieving the first row from the query you provide.

For example, if you have a number of <Movie> XML documents stored in a table of XmlType called MOVIES, then each document might look something like this:

<Movie Title="The Talented Mr. Ripley" RunningTime="139" Rating="R">
  <Director>
    <First>Anthony</First>
    <Last>Minghella</Last>
  </Director>
  <Cast>
    <Actor Role="Tom Ripley">
      <First>Matt</First>
      <Last>Damon</Last>
    </Actor>
    <Actress Role="Marge Sherwood">
      <First>Gwenyth</First>
      <Last>Paltrow</Last>
    </Actress>
    <Actor Role="Dickie Greenleaf">
      <First>Jude</First>
      <Last>Law</Last>
      <Award From="BAFTA" Category="Best Supporting Actor"/>
    </Actor>
  </Cast>
</Movie>

You can use the built-in Oracle Database XPath query features to extract an aggregate list of all cast members who have received Oscar awards from any movie in the database using a query like this:

SELECT xmlelement("AwardedActors",
                  xmlagg(extract(value(m),
                                 '/Movie/Cast/*[Award[@From="Oscar"]]')))
  FROM movies m;
-- To include this query result of XMLType in your XSQL page, 
-- paste the query inside an <xsql:include-xml> element, and add 
-- a getClobVal() method call to the query expression so that the result will 
-- be returned as a CLOB instead of as an XMLType to the client:
<xsql:include-xml connection="orcl92" xmlns:xsql="urn:oracle-xsql">
  select 
    xmlelement(
      "AwardedActors",
      xmlagg(extract(value(m),
                     '/Movie/Cast/*[Award[@From="Oscar"]]'))).getClobVal()
    from movies m
</xsql:include-xml>

Note:

Again we use the combination of XMLElement() and XMLAgg() to have the database aggregate all of the XML fragments identified by the query into a single, well-formed XML document.

Failing to do this results in an attempt by the XSQL page processor to parse a CLOB that looks like:

<Actor>...</Actor>
<Actress>...</Actress>

which is not well-formed XML because it does not have a single document element as required by the XML 1.0 recommendation. The combination of xmlelement() and xmlagg() work together to produce a well-formed result like this:

<AwardedActors>
  <Actor>...</Actor>
  <Actress>...</Actress>
</AwardedActors>

This well-formed XML is then parsed and included in your XSQL page.


See Also:

Oracle XML Developer's Kit Programmer's Guide, the chapter, 'XSQL Page Publishing Framework'


Using XSLT and XSQL

With XSQL Pages, you have control of whether XSLT is executed by the database, the middle-tier, or the client. For the database option, use the XMLTransform() operator (or equivalent) technique in your query. For the middle-tier option, add the <?xml-stylesheet?> line at the top of your template page. For the client option, just add the client="yes" attribute to your <?xml-stylesheet?> line.With XSQL Pages, you can even build pages that conditionally off-load the style-sheet processing to the client (for example, if you detect the requesting user agent is Internet Explorer 6.0), while other browsers will get the middle-tier fallback transform behavior.XSQL caches and pools XSLT style sheets in the middle tier (as well as database connections) to improve performance and throughput. Depending on the application you can further improve performance by avoiding transformation using Web Cache or other techniques as well as a further performance optimization to avoid retransforming the same (or static) data over and over.Also, XSQL Pages can include a mix of static XML and dynamically produced XML, so it already gives you some flexibility to only make the dynamic part of the page hit the database.

Generating XML Using XML SQL Utility (XSU)

Oracle XML SQL Utility (XSU) can still be used with Oracle Database to generate XML. This might be useful if you want to generate XML on the middle-tier or the client. XSU now additionally supports generating XML on tables with XMLType columns.

Example 15-36 Generating XML Using XSU for Java getXML

For example, if you have table, parts:

CREATE TABLE parts (PartNo NUMBER, PartName VARCHAR2(20), PartDesc XMLType);

then you can generate XML on this table using Java with the call:

java OracleXML getXML -user "scott/tiger" -rowTag "Part" "select * from parts"

This produces the result:

<Parts>
  <Part>
    <PartNo>1735</PartNo>
    <PartName>Gizmo</PartName>
    <PartDesc>
      <Description>
        <Title>Description of the Gizmo</Title>
        <Author>John Smith</Author>
        <Body>
          The <b>Gizmo</b> is <i>grand</i>.
        </Body>
      </Description>
    </PartDesc>
  </Part>
  ...
</Parts>

See :

Oracle XML Developer's Kit Programmer's Guide for more information on XSU

Guidelines for Generating XML With Oracle XML DB

This section describes additional guidelines for generating XML using Oracle XML DB.

Using XMLAgg ORDER BY Clause to Order Query Results Before Aggregation

To use the XMLAgg ORDER BY clause before aggregation, specify the ORDER BY clause following the first XMLAGG argument.

Example 15-37 Using XMLAgg ORDER BY Clause

For example, in the following expression the result is aggregated according to the order of the dev field:

SELECT XMLAgg(XMLElement("Dev", 
                          XMLAttributes(dev AS "id", 
                                        dev_total AS "total"),
                         devname AS "name") 
              ORDER BY dev) 
  FROM tab1 dev_total;

Using XMLSequence in the TABLE Clause to Return a Rowset

To use XMLSequence() with extract() to return a rowset with relevant portions of a document extracted as multiple rows, use XMLSequence() in the TABLE() clause as shown in the following example.

Example 15-38 Returning a Rowset using XMLSequence in the TABLE Clause

SELECT extractValue(value(t), '/*/Last') as LAST, 
       extractValue(value(t), '/*/First') as FIRST 
   FROM movies m, 
        table(xmlsequence(extract(value(m), 
                                  '/Movie/Cast/Actor | '/Movie/Cast/Actress)))
   ORDER BY LAST;

This returns a rowset with just the first and last names of the actors and actresses, ordered by last name.