Skip Headers
Oracle® XML DB Developer's Guide
11g Release 1 (11.1)

Part Number B28369-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
Contact Us

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

17 Generating XML Data from the Database

This chapter describes Oracle XML DB features for generating (constructing) XML data from relational data in the database. It describes the SQL/XML standard functions and Oracle Database-provided functions and packages for generating XML data from relational content.

This chapter contains these topics:

See Also:

Chapter 18, "Using XQuery with Oracle XML DB" for information about constructing XML data using SQL/XML functions XMLQuery and XMLTable

Overview of Generating XML Data From Oracle Database

This section provides an overview of the various ways you can generate XML data with Oracle Database.

Overview of Generating XML Using Standard SQL/XML Functions

You can generate XML data using any of the following standard SQL/XML functions supported by Oracle XML DB. This is described in "Generating XML Using SQL Functions".

Overview of Generating XML Using Oracle Database SQL Functions

You can generate XML data using any of the following Oracle Database SQL functions:

Overview of Generating XML Using DBMS_XMLGEN

You can generate XML from SQL queries using PL/SQL package DBMS_XMLGEN. This is described in "Generating XML Using DBMS_XMLGEN".

Overview of Generating XML with XSQL Pages Publishing Framework

You can generate XML using XSQL Pages Publishing Framework, also known as XSQL Servlet. This is described in Generating XML Using XSQL Pages Publishing Framework. XSQL Pages Publishing Framework is part of Oracle XML Developer's Kit for Java.

Overview of Generating XML Using XML SQL Utility (XSU)

You can use XML SQL Utility (XSU) 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.

Overview of Generating XML Using DBURIType

You can use a DBURIType instance to construct XML documents that contain database data and whose structure reflects the database structure. This is described in Chapter 20, "Accessing Data Through URIs".

Generating XML Using SQL Functions

This section describes SQL functions that you can use to construct XML data. Many of these functions belong to the SQL/XML standard, a SQL standard for XML:

These XML-generation functions are also known as XML publishing functions.

The SQL/XML standard is ISO/IEC 9075–14:2005(E), Information technology – Database languages – SQL – Part 14: XML-Related Specifications (SQL/XML). As part of the SQL standard, it is aligned with SQL:2003. It is being developed under the auspices of these two standards bodies:

This standardization process is ongoing. Please refer to http://www.sqlx.org for the latest information about XMLQuery and XMLTable.

Other XML-generating SQL functions presented in this section are Oracle Database-specific:

All of the XML-generation SQL functions convert scalars and user-defined data-type instances to their canonical XML format. In this canonical mapping, user-defined data-type attributes are mapped to XML elements.

See Also:

Chapter 18, "Using XQuery with Oracle XML DB" for information about constructing XML data using SQL/XML functions XMLQuery and XMLTable

XMLELEMENT and XMLATTRIBUTES SQL Functions

You use SQL/XML standard function XMLElement to construct XML elements from relational data. It takes as arguments an element name, an optional collection of attributes for the element, and zero or more additional arguments that make up the element content. It returns an XMLType instance.

Figure 17-1 XMLELEMENT Syntax

Description of Figure 17-1 follows
Description of "Figure 17-1 XMLELEMENT Syntax"

For an explanation of keywords ENTITYESCAPING and NOENTITYESCAPING, see "Escaping Characters in Generated XML Data".

The first argument to function XMLElement defines an identifier that names the root XML element to be created. The root-element identifier argument can be defined using a literal identifier (identifier, in Figure 17-1) or by EVALNAME followed by an expression (value_expr) that evaluates to an identifier. However it is defined, the identifier cannot be NULL; if it is, then an error is raised.

The optional XML-attributes-clause argument of function XMLElement specifies the attributes of the root element to be generated. Figure 17-2 shows the syntax of this argument.

In addition to the optional XML-attributes-clause argument, function XMLElement accepts zero or more value_expr arguments that make up the content of the root element (child elements and text content). If an XML-attributes-clause argument is also present, these content arguments must follow the XML-attributes-clause argument. Each of the content-argument expressions is evaluated, and the result is converted to XML format. If a value argument evaluates to NULL, then no content is created for that argument.

The optional XML-attributes-clause argument uses SQL/XML standard function XMLAttributes to specify the attributes of the root element. Function XMLAttributes can be used only in a call to function XMLElement; it cannot be used on its own.

Figure 17-2 XMLAttributes Clause Syntax (XMLATTRIBUTES)

Description of Figure 17-2 follows
Description of "Figure 17-2 XMLAttributes Clause Syntax (XMLATTRIBUTES)"

For an explanation of keywords ENTITYESCAPING and NOENTITYESCAPING, see "Escaping Characters in Generated XML Data".

Keywords SCHEMACHECK and NOSCHEMACHECK determine whether or not a runtime check is made of the generated attributes, to see if any of them specify a schema location that corresponds to an XML schema that is registered with Oracle XML DB, and, if so, to try to generate XML schema-based XML data accordingly. The default behavior is that provided by NOSCHEMACHECK: no check is made. In releases prior to 11g Release 1 (11.1), the default behavior is to perform the check; keyword SCHEMACHECK can be used to obtain backward compatibility.

Note that a similar check is always made at compile time, regardless of the presence or absence of NOSCHEMACHECK. This means, in particular, that if you use a string literal to specify an XML schema location attribute value, then a (compile-time) check will be made, and, if appropriate, XML schema-based data will be generated accordingly.

Note:

If a view is created to generate XML data, function XMLAttributes is used to add XML-schema location references, and the target XML schema has not yet been registered with Oracle XML DB, then the XML data generated will not be XML schema-based. If the XML schema is subsequently registered, then XML data generated thereafter will also not be XML-schema-based. To create XML schema-based data, you must recompile the view.

Argument XML-attributes-clause itself contains one or more value_expr expressions as arguments to function XMLAttributes. These are evaluated to obtain the values for the attributes of the root element. (Do not confuse these value_expr arguments to function XMLAttributes with the value_expr arguments to function XMLElement, which specify the content of the root element.) The optional AS c_alias clause for each value_expr specifies that the attribute name is c_alias, which can be either a string literal or EVALNAME followed by an expression that evaluates to a string literal.

If an attribute value expression evaluates to NULL, then no corresponding attribute is created. The data type of an attribute value expression cannot be an object type or a collection.

Escaping Characters in Generated XML Data

As specified by the SQL/XML standard, characters in explicit identifiers are not escaped in any way – it is up to you to ensure that valid XML names are used. This applies to all SQL/XML functions; in particular, it applies to the root-element identifier of XMLElement (identifier, in Figure 17-1) and to attribute identifier aliases named with AS clauses of XMLAttributes (see Figure 17-2).

However, other XML data that is generated is escaped, by default, to ensure that only valid XML NameChar characters are generated. As part of generating a valid XML element or attribute name from a SQL identifier, each character that is disallowed in an XML name is replaced with an underscore character (_), followed by the hexadecimal Unicode representation of the original character, followed by a second underscore character. For example, the colon character (:) is escaped by replacing it with _003A_, where 003A is the hexadecimal Unicode representation.

Escaping applies to characters in the evaluated value_expr arguments to all SQL/XML functions, including XMLElement and XMLAttributes. It applies also to the characters of an attribute identifier that is defined implicitly from an XMLAttributes attribute value expression that is not followed by an AS clause: the escaped form of the SQL column name is used as the name of the attribute.

In some cases, you might not need or want character escaping. If you know, for example, that the XML data being generated is well-formed, then you can save some processing time by inhibiting escaping. You can do that by specifying the keyword NOENTITYESCAPING for SQL functions XMLElement and XMLAttributes. Keyword ENTITYESCAPING imposes escaping, which is the default behavior.

Formatting of XML Dates and Timestamps

The XML Schema standard specifies that dates and timestamps in XML data be in standard formats. XML generation functions in Oracle XML DB produce XML dates and timestamps according to this standard.

In releases prior to Oracle Database 10g Release 2, the database settings for date and timestamp formats, not the XML Schema standard formats, were used for XML. You can reproduce this previous behavior by setting the database event 19119, level 0x8, as follows:

ALTER SESSION SET EVENTS '19119 TRACE NAME CONTEXT FOREVER, LEVEL 0x8';

If you otherwise need to produce a non-standard XML date or timestamp, use SQL function to_char – see Example 17-1.

See Also:

http://www.w3.org/TR/2004/REC-xmlschema-2-20041028/datatypes.html#isoformats for the XML Schema specification of XML date and timestamp formats

XMLElement Examples

This section provides examples that use SQL function XMLElement.

Example 17-1 XMLELEMENT: Formatting a Date

This example shows how to produce an XML date with a format different from the XML Schema standard format.

-- With standard XML date format:
SELECT XMLElement("Date", hire_date)
  FROM hr.employees
  WHERE employee_id = 203;
 
XMLELEMENT("DATE",HIRE_DATE)
----------------------------
<Date>1994-06-07</Date>
 
1 row selected.
 
-- With an alternative date format:
SELECT XMLElement("Date", to_char(hire_date))
  FROM hr.employees
  WHERE employee_id = 203;
 
XMLELEMENT("DATE",TO_CHAR(HIRE_DATE))
-------------------------------------
<Date>07-JUN-94</Date>
 
1 row selected.

Example 17-2 XMLELEMENT: Generating an Element for Each Employee

This example produces an Emp element for each employee, with the employee name as its content:

SELECT e.employee_id, 
       XMLELEMENT ("Emp", e.first_name ||' '|| e.last_name) AS "RESULT"
   FROM hr.employees e
   WHERE employee_id > 200;

This query produces the following typical result:

EMPLOYEE_ID RESULT
----------- -----------------------------------
        201 <Emp>Michael Hartstein</Emp>
        202 <Emp>Pat Fay</Emp>
        203 <Emp>Susan Mavris</Emp>
        204 <Emp>Hermann Baer</Emp>
        205 <Emp>Shelley Higgins</Emp>
        206 <Emp>William Gietz</Emp>
 
6 rows selected.

SQL function XMLElement can also be nested, to produce XML data with a nested structure.

Example 17-3 XMLELEMENT: Generating Nested XML

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

SELECT XMLElement("Emp", 
                   XMLElement("name", e.first_name ||' '|| e.last_name),
                   XMLElement("hiredate", e.hire_date)) AS "RESULT" 
FROM hr.employees e 
WHERE employee_id > 200 ;

This query produces the following typical XML result:

RESULT
-----------------------------------------------------------------------
<Emp><name>Michael Hartstein</name><hiredate>1996-02-17</hiredate></Emp>
<Emp><name>Pat Fay</name><hiredate>1997-08-17</hiredate></Emp>
<Emp><name>Susan Mavris</name><hiredate>1994-06-07</hiredate></Emp>
<Emp><name>Hermann Baer</name><hiredate>1994-06-07</hiredate></Emp>
<Emp><name>Shelley Higgins</name><hiredate>1994-06-07</hiredate></Emp>
<Emp><name>William Gietz</name><hiredate>1994-06-07</hiredate></Emp>
 
6 rows selected.

Example 17-4 XMLELEMENT: Generating Employee Elements with ID and Name Attributes

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.first_name ||' ' || e.last_name AS "name"))
  AS "RESULT"
  FROM hr.employees e
  WHERE employee_id > 200;

This query produces the following typical XML result fragment:

RESULT
-----------------------------------------------
<Emp ID="201" name="Michael Hartstein"></Emp>
<Emp ID="202" name="Pat Fay"></Emp>
<Emp ID="203" name="Susan Mavris"></Emp>
<Emp ID="204" name="Hermann Baer"></Emp>
<Emp ID="205" name="Shelley Higgins"></Emp>
<Emp ID="206" name="William Gietz"></Emp>
 
6 rows selected.

As mentioned in "Escaping Characters in Generated XML Data", characters in the root-element name and the names of any attributes defined by AS clauses are not escaped. Characters in an identifier name are escaped only if the name is created from an evaluated expression (such as a column reference). The following query shows that the root-element name and the attribute name are not escaped. Invalid XML is produced because greater-than sign (>) and a comma (,) are not allowed in XML element and attribute names.

SELECT XMLElement("Emp->Special", 
                  XMLAttributes(e.last_name || ', ' || e.first_name
                                AS "Last,First"))
   AS "RESULT"
   FROM hr.employees e
   WHERE employee_id = 201;

This query produces the following result, which is not well-formed XML:

RESULT
--------------------------------------------------------------------
<Emp->Special Last,First="Hartstein, Michael"></Emp->Special>

1 row selected.

A full description of character escaping is included in the SQL/XML standard.

Example 17-5 XMLELEMENT: Using Namespaces to Create a Schema-Based XML Document

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

SELECT XMLElement("Employee", 
                  XMLAttributes('http://www.w3.org/2001/XMLSchema' AS
                                  "xmlns:xsi",
                                'http://www.oracle.com/Employee.xsd' AS
                                  "xsi:nonamespaceSchemaLocation"),
                  XMLForest(employee_id, last_name, salary)) AS "RESULT"
   FROM hr.employees
   WHERE department_id = 10;

This creates the following XML document that conforms to XML schema Employee.xsd. (The result is shown here pretty-printed, for clarity.)

RESULT
-----------------------------------------------------------------------------
<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema"
          xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd">
   <EMPLOYEE_ID>200</EMPLOYEE_ID>
   <LAST_NAME>Whalen</LAST_NAME>
   <SALARY>4400</SALARY>
</Employee>

1 row selected.

Example 17-6 XMLELEMENT: Generating an Element from a User-Defined Data-Type Instance

Example 17-10 shows an XML document with employee information. You can generate a hierarchical XML document with the employee and department information as follows:

CREATE OR REPLACE TYPE emp_t AS OBJECT ("@EMPNO" NUMBER(4),
                                         ENAME VARCHAR2(10));
/
Type created.

CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t;
/
Type created.

CREATE OR REPLACE TYPE dept_t AS OBJECT ("@DEPTNO" NUMBER(2),
                                         DNAME VARCHAR2(14),
                                         EMP_LIST emplist_t);
/
Type created.

SELECT XMLElement("Department",
                  dept_t(department_id,
                         department_name,
                         CAST(MULTISET(SELECT employee_id, last_name
                                         FROM hr.employees e
                                         WHERE e.department_id = d.department_id)
                              AS emplist_t)))
  AS deptxml
  FROM hr.departments d
  WHERE d.department_id = 10;

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

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

You use SQL/XML standard function XMLForest to construct a forest of XML elements. Its arguments are expressions to be evaluated, with optional aliases. Figure 17-3 describes the XMLForest syntax.

Figure 17-3 XMLFOREST Syntax

Description of Figure 17-3 follows
Description of "Figure 17-3 XMLFOREST Syntax"

Each of the value expressions (value_expr in Figure 17-3) is converted to XML format, and, optionally, identifier c_alias is used as the attribute identifier (c_alias can be a string literal or EVALNAME followed by an expression that evaluates to a string literal).

For an object type or collection, the AS clause is required. For other types, the AS clause is optional. For a given expression, if the AS clause is omitted, then characters in the evaluated value expression are escaped to form the name of the enclosing tag of the element. The escaping is as defined in "Escaping Characters in Generated XML Data". If the value expression evaluates to NULL, then no element is created for that expression.

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

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

SELECT XMLElement("Emp", 
                  XMLAttributes(e.first_name ||' '|| e.last_name AS "name"),
                  XMLForest(e.hire_date, e.department AS "department"))
AS "RESULT"
FROM employees e WHERE e.department_id = 20;

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

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

2 rows selected.

Example 17-8 XMLFOREST: Generating an Element from a User-Defined Data-Type Instance

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

SELECT XMLForest(
  dept_t(department_id,
         department_name,
         CAST (MULTISET (SELECT employee_id, last_name
                           FROM hr.employees e
                           WHERE e.department_id = d.department_id)
               AS emplist_t))
         AS "Department")
  AS deptxml
  FROM hr.departments d
  WHERE department_id=10;

This produces an XML document with element Department containing attribute DEPTNO and child element DNAME.

DEPTXML
---------------------------------
<Department DEPTNO="10">
  <DNAME>Administration</DNAME>
    <EMP_LIST>
      <EMP_T EMPNO="200">
        <ENAME>Whalen</ENAME>
      </EMP_T>
    </EMP_LIST>
</Department>

1 row selected.

You may want to compare this example with Example 17-6 and Example 17-27.

XMLSEQUENCE SQL Function

Oracle SQL function XMLSequence returns an XMLSequenceType value (a varray of XMLType instances). Because it returns a collection, this function can be used in the FROM clause of SQL queries. See Figure 17-4.

Figure 17-4 XMLSEQUENCE Syntax

Description of Figure 17-4 follows
Description of "Figure 17-4 XMLSEQUENCE Syntax"

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

Function XMLSequence returns only top-level element nodes. It will not break up attribute nodes or text nodes.

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

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

3 rows selected.

Function XMLSequence has two forms:

  • The first form takes as input an XMLType instance, and returns a varray of top-level nodes. This form can be used to break up XML fragments into multiple rows.

  • The second form takes as input a REFCURSOR instance and an optional instance of the XMLFormat object, and returns a varray of XMLType instances corresponding to each row of the cursor. This form can be used to construct XMLType instances from arbitrary SQL queries. This use of XMLFormat does not support XML schemas.

The first form is effectively superseded by standard SQL/XML function XMLTable, which provides for more readable SQL code. Prior to Oracle Database 10g Release 2, XMLSequence was used with SQL function table to do some of what can now be done better with standard function XMLTable.

See Also:

Chapter 18, "Using XQuery with Oracle XML DB" for more information about SQL/XML function XMLTable

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

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

CREATE TABLE emp_xml_tab OF XMLType;
 
Table created.

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

1 row created.

COMMIT;

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

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

These are the steps involved in this query:

  1. Function extract returns a fragment of EMP elements.

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

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

The query returns the following XML document:

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

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

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

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

This query returns the following XML:

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

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

Example 17-12 XMLSEQUENCE: Un-Nesting Collections in XML Documents into SQL Rows

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

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

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

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

This returns the following:

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

3 rows selected

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

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

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

XMLCONCAT SQL Function

You use SQL/XML standard function XMLConcat to construct an XML fragment by concatenating multiple XMLType instances. Figure 17-5 shows the XMLConcat syntax. Function XMLConcat has two forms:

  • The first form takes an XMLSequenceType value (a varray of XMLType instances) and returns a single XMLType instance that is the concatenation of all of the elements of the varray. This form is useful to collapse lists of XMLType instances into a single instance.

  • The second form takes an arbitrary number of XMLType instances and concatenates them together. If one of the values is NULL, then it is ignored in the result. If all the values are NULL, then the result is NULL. This form is used to concatenate arbitrary number of XMLType instances in the same row. Function XMLAgg can be used to concatenate XMLType instances across rows.

Figure 17-5 XMLCONCAT Syntax

Description of Figure 17-5 follows
Description of "Figure 17-5 XMLCONCAT Syntax"

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

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

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

This query returns a single XML fragment. (The result is shown here pretty-printed, for clarity.)

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

1 row selected.

Example 17-14 XMLCONCAT: Concatenating XML Elements

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

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

This query produces the following XML fragment:

RESULT
--------------------------------------------
<first>Den</first><last>Raphaely</last>
<first>Alexander</first><last>Khoo</last>
<first>Shelli</first><last>Baida</last>
<first>Sigal</first><last>Tobias</last>
<first>Guy</first><last>Himuro</last>
<first>Karen</first><last>Colmenares</last>
 
6 rows selected.

XMLAGG SQL Function

You use SQL/XML standard function XMLAgg to construct a forest of XML elements from a collection of XML elements — it is an aggregate function.

Figure 17-6 XMLAGG Syntax

Description of Figure 17-6 follows
Description of "Figure 17-6 XMLAGG Syntax"

Figure 17-6 describes the XMLAgg() syntax, where the order_by_clause is the following:

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

Numeric literals are not interpreted as column positions. For example, ORDER BY 1 does not mean order by the first column. Instead, numeric literals are interpreted as any other literals.

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

Function XMLAgg 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.

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

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

SELECT XMLElement("Department", XMLAgg(XMLElement("Employee",
                                                  e.job_id||' '||e.last_name)
                                       ORDER BY e.last_name))
  AS "Dept_list"     
  FROM hr.employees e
  WHERE e.department_id = 30 OR e.department_id = 40;

Dept_list
------------------
<Department>
  <Employee>PU_CLERK Baida</Employee>
  <Employee>PU_CLERK Colmenares</Employee>
  <Employee>PU_CLERK Himuro</Employee>
  <Employee>PU_CLERK Khoo</Employee>
  <Employee>HR_REP Mavris</Employee>
  <Employee>PU_MAN Raphaely</Employee>
  <Employee>PU_CLERK Tobias</Employee>
</Department>

1 row selected.

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

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

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

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

2 rows selected.

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

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.

Example 17-16 XMLAGG: Generating Nested Elements

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

First, this query shows the employees of department 30.

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

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

CREATE TABLE hr.dependents (id NUMBER(4) PRIMARY KEY,
                            employee_id NUMBER(4),
                            name VARCHAR2(10));
Table created.
INSERT INTO dependents VALUES (1, 114, 'MARK');
1 row created.
INSERT INTO dependents VALUES (2, 114, 'JACK');
1 row created.
INSERT INTO dependents VALUES (3, 115, 'JANE');
1 row created.
INSERT INTO dependents VALUES (4, 116, 'HELEN');
1 row created.
INSERT INTO dependents VALUES (5, 116, 'FRANK');
1 row created.
COMMIT;
Commit complete.
 

This query generates the XML data for department that contains the information about dependents. (The result is shown here pretty-printed, for clarity.)

SELECT
  XMLElement(
    "Department",
    XMLAttributes(d.department_name AS "name"),
    (SELECT
       XMLAgg(XMLElement("emp",
                         XMLAttributes(e.last_name AS name),
                         (SELECT XMLAgg(XMLElement("dependent",
                                        XMLAttributes(de.name AS "name")))
                            FROM dependents de
                            WHERE de.employee_id = e.employee_id)))
       FROM employees e
       WHERE e.department_id = d.department_id)) AS "dept_list"
  FROM departments d
  WHERE department_id = 30;
 
dept_list
--------------------------------------------------------------------------------
<Department name="Purchasing">
  <emp NAME="Raphaely">
    <dependent name="MARK"></dependent>
    <dependent name="JACK"></dependent>
  </emp><emp NAME="Khoo">
    <dependent name="JANE"></dependent>
  </emp>
  <emp NAME="Baida">
    <dependent name="HELEN"></dependent>
    <dependent name="FRANK"></dependent>
  </emp><emp NAME="Tobias"></emp>
  <emp NAME="Himuro"></emp>
  <emp NAME="Colmenares"></emp>
</Department>
 
1 row selected.

XMLPI SQL Function

You use SQL/XML standard function XMLPI to construct an XML processing instruction (PI). Figure 17-7 shows the syntax:

Argument value_expr is evaluated, and the string result is appended to the optional identifier (identifier), separated by a space. This concatenation is then enclosed between "<?" and "?>" to create the processing instruction. That is, if string-result is the result of evaluating value_expr, then the generated processing instruction is <?identifier string-result?>. If string-result is the empty string, '', then the function returns <?identifier?>.

As an alternative to using keyword NAME followed by a literal string identifier, you can use keyword EVALNAME followed by an expression that evaluates to a string to be used as the identifier.

An error is raised if the constructed XML is not a legal XML processing instruction. In particular:

  • identifier must not be the word "xml" (uppercase, lowercase, or mixed case).

  • string-result must not contain the character sequence "?>".

Function XMLPI returns an instance of XMLType. If string-result is NULL, then it returns NULL.

Example 17-17 Using XMLPI

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

This results in the following output:

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

1 row selected.

XMLCOMMENT SQL Function

You use SQL/XML standard function XMLComment to construct an XML comment. Figure 17-8 shows the syntax:

Figure 17-8 XMLComment Syntax

Description of Figure 17-8 follows
Description of "Figure 17-8 XMLComment Syntax"

Argument value_expr is evaluated to a string, and the result is used as the body of the generated XML comment; that is, the result is <!--string-result-->, where string-result is the string result of evaluating value_expr. If string-result is the empty string, then the comment is empty: <!---->.

An error is raised if the constructed XML is not a legal XML comment. In particular, string-result must not contain two consecutive hyphens (-): "--".

Function XMLComment returns an instance of XMLType. If string-result is NULL, then the function returns NULL.

Example 17-18 Using XMLCOMMENT

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

This query results in the following output:

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

XMLROOT SQL Function

SQL function XMLRoot was at one time part of the SQL/XML standard, but it is deprecated as a standard function as of SQL/XML 2005. It remains available in Oracle XML DB, as an Oracle function.

You use XMLRoot to add a VERSION property, and optionally a STANDALONE property, to the root information item of an XML value. Typically, this is done to ensure data-model compliance. Figure 17-9 shows the syntax of XMLRoot:

Figure 17-9 XMLRoot Syntax

Description of Figure 17-9 follows
Description of "Figure 17-9 XMLRoot Syntax"

First argument xml-expression is evaluated, and the indicated properties (VERSION, STANDALONE) and their values are added to a new prolog for the resulting XMLType instance. If the evaluated xml-expression already contains a prolog, then an error is raised.

Second argument string-valued-expression (which follows keyword VERSION) is evaluated, and the resulting string is used as the value of the prolog version property. The value of the prolog standalone property (lowercase) is taken from the optional third argument STANDALONE YES or NO value. If NOVALUE is used for VERSION, then "version=1.0" is used in the resulting prolog. If NOVALUE is used for STANDALONE, then the standalone property is omitted from the resulting prolog.

Function XMLRoot returns an instance of XMLType. If first argument xml-expression evaluates to NULL, then the function returns NULL.

Example 17-19 Using XMLRoot

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

This results in the following output:

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

1 row selected.

XMLSERIALIZE SQL Function

You use SQL/XML standard function XMLSerialize to obtain a string or a LOB representation of XML data. Figure 17-10 shows the syntax:

Figure 17-10 XMLSerialize Syntax

Description of Figure 17-10 follows
Description of "Figure 17-10 XMLSerialize Syntax"

Argument value_expr is evaluated, and the resulting XMLType instance is serialized to produce the content of the created string or LOB. If presentFoot 1 , the specified datatype must be one of the following (the default data type is CLOB):

  • VARCHAR2

  • VARCHAR

  • CLOB

If you specify DOCUMENT, then the result of evaluating value_expr must be a well-formed document; in particular, it must have a single root. If the result is not a well-formed document, then an error is raised. If you specify CONTENT, however, then the result of value_expr is not checked for being well-formed.

If the underlying CLOB value or string has encoding information, then an appropriate encoding="..." declaration is added to the prolog.

If value_expr evaluates to NULL or to the empty string (''), then function XMLSerialize returns NULL.

Example 17-20 Using XMLSERIALIZE

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

This results in the following output:

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

XMLPARSE SQL Function

You use SQL/XML standard function XMLParse to parse a string containing XML data and construct a corresponding XMLType instance. Figure 17-11 shows the syntax:

Figure 17-11 XMLParse Syntax

Description of Figure 17-11 follows
Description of "Figure 17-11 XMLParse Syntax"

Argument value_expr is evaluated to produce the string that is parsed. If you specify DOCUMENT, then value_expr must correspond to a singly rooted, well-formed XML document. If you specify CONTENT, then value_expr need only correspond to a well-formed XML fragment; that is, it need not be singly rooted.

Keyword WELLFORMED is an Oracle XML DB extension to the SQL/XML standard. When you specify WELLFORMED, you are informing the parser that argument value_expr is well-formed, so Oracle XML DB does not check to ensure that it is well-formed.

Function XMLParse returns an instance of XMLType. If value_expr evaluates to NULL, then the function returns NULL.

Example 17-21 Using XMLPARSE

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

This results in the following output:

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

See Also:

http://www.w3.org/TR/REC-xml/, Extensible Markup Language (XML) 1.0, for the definition of well-formed XML documents and fragments

XMLCOLATTVAL SQL Function

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

Figure 17-12 XMLCOLATTVAL Syntax

Description of Figure 17-12 follows
Description of "Figure 17-12 XMLCOLATTVAL Syntax"

The arguments are used as the values of the name attribute of the column element. The c_alias values are used as the attribute identifiers.

As an alternative to using keyword AS followed by a literal string c_alias, you can use AS EVALNAME followed by an expression that evaluates to a string to be used as the attribute identifier.

Because argument values value_expr are used only as attribute values, they need not be escaped in any way. This is in contrast to function XMLForest. It means that you can use XMLColAttVal to transport SQL columns and values without escaping.

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

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

SELECT XMLElement("Emp", 
                  XMLAttributes(e.first_name ||' '||e.last_name AS "fullname" ),
                  XMLColAttVal(e.hire_date, e.department_id AS "department"))
  AS "RESULT" 
  FROM hr.employees e
  WHERE e.department_id = 30;

This query produces the following XML result. (The result is shown here pretty-printed, for clarity.)

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

XMLCDATA SQL Function

You use Oracle Database SQL function XMLCDATA to generate an XML CDATA section. Figure 17-13 shows the syntax:

Figure 17-13 XMLCDATA Syntax

Description of Figure 17-13 follows
Description of "Figure 17-13 XMLCDATA Syntax"

Argument value_expr is evaluated to a string, and the result is used as the body of the generated XML CDATA section, <![CDATA[string-result]]>, where string-result is the result of evaluating value_expr. If string-result is the empty string, then the CDATA section is empty: <![CDATA[]]>.

An error is raised if the constructed XML is not a legal XML CDATA section. In particular, string-result must not contain two consecutive right brackets (]): "]]".

Function XMLCDATA returns an instance of XMLType. If string-result is NULL, then the function returns NULL.

Example 17-23 Using XMLCDATA

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

This results in the following output. (The result is shown here pretty-printed, for clarity.)

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

Generating XML Using DBMS_XMLGEN

PL/SQL package DBMS_XMLGEN creates XML documents from SQL query results. It retrieves an XML document as a CLOB or XMLType value.

It provides a fetch interface, whereby you can specify the maximum number of rows to retrieve and the number of rows to skip. For example, the first fetch could retrieve a maximum of ten rows, skipping the first four. This is especially useful for pagination requirements in Web applications.

Package 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 and the enclosing tag names.

Using DBMS_XMLGEN

Figure 17-14 illustrates how to use package DBMS_XMLGEN. The steps are as follows:

  1. Get the context from the package by supplying a SQL query and calling PL/SQL function newContext.

  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 PL/SQL function getXML or getXMLType. By setting the maximum number of rows to be retrieved for each fetch using PL/SQL procedure setMaxRows, you can call either of these functions repeatedly, retrieving up to the maximum number of rows for each call. These functions return XML data (as a CLOB value and as an instance of XMLType, respectively), unless there are no rows retrieved; in that case, these functions return NULL. To determine how many rows were retrieved, use PL/SQL function getNumRowsProcessed.

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

  5. Call PL/SQL procedure closeContext to free up any previously allocated resources.

Figure 17-14 Using DBMS_XMLGEN

Description of Figure 17-14 follows
Description of "Figure 17-14 Using DBMS_XMLGEN"

In conjunction with a SQL query, method DBMS_XMLGEN.getXML() typically returns a result like the following, as a CLOB value:

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPLOYEE_ID>100</EMPLOYEE_ID>
  <FIRST_NAME>Steven</FIRST_NAME>
  <LAST_NAME>King</LAST_NAME>
  <EMAIL>SKING</EMAIL>
  <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
  <HIRE_DATE>17-JUN-87</HIRE_DATE>
  <JOB_ID>AD_PRES</JOB_ID>
  <SALARY>24000</SALARY>
  <DEPARTMENT_ID>90</DEPARTMENT_ID>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>101</EMPLOYEE_ID>
  <FIRST_NAME>Neena</FIRST_NAME>
  <LAST_NAME>Kochhar</LAST_NAME>
  <EMAIL>NKOCHHAR</EMAIL>
  <PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
  <HIRE_DATE>21-SEP-89</HIRE_DATE>
  <JOB_ID>AD_VP</JOB_ID>
  <SALARY>17000</SALARY>
  <MANAGER_ID>100</MANAGER_ID>
  <DEPARTMENT_ID>90</DEPARTMENT_ID>
 </ROW>
</ROWSET>

The default mapping between relational data and XML data is as follows:

  • Each row returned by the SQL query maps to an XML element with the default element name ROW.

  • Each column returned by the SQL query maps to a child element of the ROW element.

  • The entire result is wrapped in a ROWSET element.

  • Binary data is transformed to its hexadecimal representation.

Element names ROW and ROWSET can be replaced with names you choose, using DBMS_XMLGEN procedures setRowTagName() and setRowSetTagName(), respectively.

The CLOB value returned by getXML() has the same encoding as the database character set. If the database character set is SHIFTJIS, then the XML document returned is also SHIFTJIS.

Functions and Procedures of Package DBMS_XMLGEN

Table 17-1 describes the functions and procedures of package DBMS_XMLGEN.

Table 17-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;

DTD CONSTANT NUMBER:= 1;

SCHEMA CONSTANT NUMBER:= 2;

Can be used in function getXML to specify whether to generate a DTD or XML schema or neither (NONE). Only the NONE specification is supported.

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

Parameter: 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 PL/SQL cursor variable. The context handle can be used for the rest of the functions.

FUNCTION

newContextFromHierarchy(
  queryString IN VARCHAR2)
  RETURN ctxHandle;

Parameter: 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. A NULL value for rowTag indicates that you do not want the ROW element to be present.

Call this procedure to set the name of the ROW element, if you do not want the default ROW name to show up. You can also set rowTag to NULL to suppress the ROW element itself.

However, since getXML returns complete XML documents, not XML fragments, there must be a (single) root element. Therefore, an error is raised if both the rowTag value and the rowSetTag value (see setRowSetTag, next) 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 root element to be used in the output. A NULL value for rowSetTag indicates that you do not want the ROWSET element to be present.

Call this procedure to set the name of the document root element, if you do not want the default name ROWSET to be used. You can set rowSetTag to NULL to suppress printing of the document root element.

However, since function getXML returns complete XML documents, not XML fragments, there must be a (single) root element. Therefore, an error is raised if both the rowTag value and the rowSetTag value (see setRowTag, previous) are NULL and there is more than one column or row in the output, or if the rowSetTag value is NULL and there is more than one 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 XML data using function getXML. This count does not include the number of rows skipped before generating XML data.

FUNCTION

getNumRowsProcessed(ctx IN ctxHandle) 
  RETURN number;

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

Returns: 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 before generating XML data. Use this function to determine the terminating condition if you are calling getXML in a loop. Note that getXML would always generate an 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 function newContextFormHierarchy().

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 escape 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. It is expensive to 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, 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);

Parameter: 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);

Parameter: 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);

Parameter: 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 XML data string argument.

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

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

useNullAttributeIndicator is a shortcut for setNullHandling(ctx, NULL_ATTR).

PROCEDURE

setBindValue(
  ctx IN ctxHandle, 
  bindVariableName IN VARCHAR2, 
  bindValue IN VARCHAR2);

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

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.


DBMS_XMLGEN Examples

Example 17-24 DBMS_XMLGEN: Generating Simple XML

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

CREATE TABLE temp_clob_tab(result CLOB);

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

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

SELECT * FROM temp_clob_tab WHERE ROWNUM = 1;

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

...
 
1 row selected.

Example 17-25 DBMS_XMLGEN: Generating Simple XML with Pagination (Fetch)

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

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

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

SELECT * FROM temp_clob_tab WHERE rownum <3;

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

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

This example uses object types to represent nested structures.

CREATE TABLE new_departments(department_id   NUMBER PRIMARY KEY,
                             department_name VARCHAR2(20));
CREATE TABLE new_employees(employee_id       NUMBER PRIMARY KEY,
                           last_name         VARCHAR2(20),
                           department_id     NUMBER REFERENCES new_departments);
CREATE TYPE emp_t AS OBJECT("@employee_id"   NUMBER,
                            last_name        VARCHAR2(20));
/
INSERT INTO new_departments VALUES(10, 'SALES');
INSERT INTO new_departments VALUES(20, 'ACCOUNTING');
INSERT INTO new_employees   VALUES(30, 'Scott', 10);
INSERT INTO new_employees   VALUES(31, 'Mary',  10);
INSERT INTO new_employees   VALUES(40, 'John',  20);
INSERT INTO new_employees   VALUES(41, 'Jerry', 20);
COMMIT;
CREATE TYPE emplist_t AS TABLE OF emp_t;
/
CREATE TYPE dept_t AS OBJECT("@department_id" NUMBER,
                             department_name  VARCHAR2(20),
                             emplist          emplist_t);
/
CREATE TABLE temp_clob_tab(result CLOB);
DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  result CLOB;
BEGIN
  DBMS_XMLGEN.setRowTag(qryCtx, NULL);
  qryCtx := DBMS_XMLGEN.newContext
    ('SELECT dept_t(department_id,
                    department_name,
                    CAST(MULTISET
                           (SELECT e.employee_id, e.last_name
                              FROM new_employees e
                              WHERE e.department_id = d.department_id)
                         AS emplist_t))
        AS deptxml
        FROM new_departments d');
  -- now get the result
  result := DBMS_XMLGEN.getXML(qryCtx);
  INSERT INTO temp_clob_tab VALUES (result);
  -- close context
  DBMS_XMLGEN.closeContext(qryCtx);
END;
/
SELECT * FROM temp_clob_tab;

Here is the resulting XML:

RESULT
--------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTXML department_id="10">
   <DEPARTMENT_NAME>SALES</DEPARTMENT_NAME>
   <EMPLIST>
    <EMP_T employee_id="30">
     <LAST_NAME>Scott</LAST_NAME>
    </EMP_T>
    <EMP_T employee_id="31">
     <LAST_NAME>Mary</LAST_NAME>
    </EMP_T>
   </EMPLIST>
  </DEPTXML>
 </ROW>
 <ROW>
  <DEPTXML department_id="20">
   <DEPARTMENT_NAME>ACCOUNTING</DEPARTMENT_NAME>
   <EMPLIST>
    <EMP_T employee_id="40">
     <LAST_NAME>John</LAST_NAME>
    </EMP_T>
    <EMP_T employee_id="41">
     <LAST_NAME>Jerry</LAST_NAME>
    </EMP_T>
   </EMPLIST>
  </DEPTXML>
 </ROW>
</ROWSET>
 
1 row selected.

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

  • Object types map as an XML element – see Chapter 6, "XML Schema Storage and Query: Basic".

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

    When used in column names or attribute names, the at-sign (@) is translated into an attribute of the enclosing XML element in the mapping.

Example 17-27 DBMS_XMLGEN: Generating Nested XML With User-Defined Data-Type Instances

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

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

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

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

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

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

SELECT * FROM temp_clob_tab;

RESULT
---------------------------------
<?xml version="1.0"?>
<ROWSET>
 <DEPTXML deptno="10">
  <DNAME>Sports</DNAME>
  <EMPLIST>
   <EMP_T empno="200">
    <ENAME>John</ENAME>
   </EMP_T>
   <EMP_T empno="300">
    <ENAME>Jack</ENAME>
   </EMP_T>
  </EMPLIST>
 </DEPTXML>
 <DEPTXML deptno="20">
  <DNAME>Accounting</DNAME>
  <EMPLIST>
   <EMP_T empno="400">
    <ENAME>Mary</ENAME>
   </EMP_T>
   <EMP_T empno="500">
    <ENAME>Jerry</ENAME>
   </EMP_T>
  </EMPLIST>
 </DEPTXML>
</ROWSET>
 
1 row selected.

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

Example 17-28 DBMS_XMLGEN: Generating an XML Purchase Order

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

-- Create relational schema and define object views
-- DBMS_XMLGEN maps user-defined data-type attribute names that start
--    with an at-sign (@) to XML attributes
 
-- Purchase Order Object View Model
 
-- PhoneList varray object type
CREATE TYPE phonelist_vartyp AS VARRAY(10) OF VARCHAR2(20)
/
-- Address object type
CREATE TYPE address_typ AS OBJECT(Street VARCHAR2(200),
                                  City   VARCHAR2(200),
                                  State  CHAR(2),
                                  Zip    VARCHAR2(20))
/
-- Customer object type
CREATE TYPE customer_typ AS OBJECT(CustNo    NUMBER,
                                   CustName  VARCHAR2(200),
                                   Address   address_typ,
                                   PhoneList phonelist_vartyp)
/
-- StockItem object type
CREATE TYPE stockitem_typ AS OBJECT("@StockNo" NUMBER,
                                    Price      NUMBER,
                                    TaxRate    NUMBER)
/
-- LineItems object type
CREATE TYPE lineitem_typ AS OBJECT("@LineItemNo" NUMBER,
                                   Item          stockitem_typ,
                                   Quantity      NUMBER,
                                   Discount      NUMBER)
/
-- LineItems ordered collection table
CREATE TYPE lineitems_ntabtyp AS TABLE OF lineitem_typ 
/
-- Purchase Order object type
CREATE TYPE po_typ AUTHID CURRENT_USER
  AS OBJECT(PONO            NUMBER,
            Cust_ref        REF customer_typ,
            OrderDate       DATE,
            ShipDate        TIMESTAMP,
            LineItems_ntab  lineitems_ntabtyp,
            ShipToAddr      address_typ)
/
-- Create Purchase Order relational model tables
-- Customer table
CREATE TABLE customer_tab(CustNo     NUMBER NOT NULL,
                          CustName   VARCHAR2(200),
                          Street     VARCHAR2(200),
                          City       VARCHAR2(200),
                          State      CHAR(2),
                          Zip        VARCHAR2(20),
                          Phone1     VARCHAR2(20),
                          Phone2     VARCHAR2(20),
                          Phone3     VARCHAR2(20),
                          CONSTRAINT cust_pk PRIMARY KEY (CustNo));
-- Purchase Order table
CREATE TABLE po_tab (PONo       NUMBER,        /* purchase order number */
                     Custno     NUMBER     /*  foreign KEY referencing customer */
                                CONSTRAINT po_cust_fk REFERENCES customer_tab, 
                     OrderDate  DATE,          /*  date of order */  
                     ShipDate   TIMESTAMP,     /* date to be shipped */    
                     ToStreet   VARCHAR2(200), /* shipto address */    
                     ToCity     VARCHAR2(200),    
                     ToState    CHAR(2),    
                     ToZip      VARCHAR2(20),
                     CONSTRAINT po_pk PRIMARY KEY(PONo));    
--Stock Table
CREATE TABLE stock_tab (StockNo NUMBER CONSTRAINT stock_uk UNIQUE,
                        Price   NUMBER,
                        TaxRate NUMBER);
--Line Items table
CREATE TABLE lineitems_tab(LineItemNo NUMBER,
                           PONo       NUMBER
                                      CONSTRAINT li_po_fk REFERENCES po_tab,
                           StockNo    NUMBER,
                           Quantity   NUMBER,
                           Discount   NUMBER,
                           CONSTRAINT li_pk PRIMARY KEY (PONo, LineItemNo));
-- Create Object views
-- Customer Object View
CREATE OR REPLACE VIEW customer OF customer_typ
  WITH OBJECT IDENTIFIER(CustNo)
  AS SELECT c.custno, c.custname,
            address_typ(c.street, c.city, c.state, c.zip),
            phonelist_vartyp(phone1, phone2, phone3)
       FROM customer_tab c;
--Purchase order view
CREATE OR REPLACE VIEW po OF po_typ
  WITH OBJECT IDENTIFIER (PONo)
  AS SELECT p.pono, make_ref(Customer, P.Custno), p.orderdate, p.shipdate,
            CAST(MULTISET(
                   SELECT lineitem_typ(l.lineitemno, stockitem_typ(l.stockno,
                                                                   s.price,
                                                                   s.taxrate),
                                       l.quantity, l.discount)
                     FROM lineitems_tab l, stock_tab s
                     WHERE l.pono = p.pono AND s.stockno=l.stockno)
                 AS lineitems_ntabtyp),
            address_typ(p.tostreet,p.tocity, p.tostate, p.tozip)
       FROM po_tab p;
-- Create table with XMLType column to store purchase order in XML format
CREATE TABLE po_xml_tab(poid  NUMBER, podoc XMLType)
/
-- Populate data
-------------------
-- Establish Inventory
INSERT INTO stock_tab VALUES(1004, 6750.00, 2);
INSERT INTO stock_tab VALUES(1011, 4500.23, 2);
INSERT INTO stock_tab VALUES(1534, 2234.00, 2);
INSERT INTO stock_tab VALUES(1535, 3456.23, 2);
-- Register Customers
INSERT INTO customer_tab
  VALUES (1, 'Jean Nance', '2 Avocet Drive',
          'Redwood Shores', 'CA', '95054',
          '415-555-1212', NULL, NULL);
INSERT INTO customer_tab
  VALUES (2, 'John Nike', '323 College Drive',
          'Edison', 'NJ', '08820',
          '609-555-1212', '201-555-1212', NULL);
-- Place orders
INSERT INTO po_tab
  VALUES (1001, 1, '10-APR-1997', '10-MAY-1997',
          NULL, NULL, NULL, NULL);
INSERT INTO po_tab
  VALUES (2001, 2, '20-APR-1997', '20-MAY-1997',
          '55 Madison Ave', 'Madison', 'WI', '53715');
-- Detail line items
INSERT INTO lineitems_tab VALUES(01, 1001, 1534, 12,  0);
INSERT INTO lineitems_tab VALUES(02, 1001, 1535, 10, 10);
INSERT INTO lineitems_tab VALUES(01, 2001, 1004,  1,  0);
INSERT INTO lineitems_tab VALUES(02, 2001, 1011,  2,  1);
 
-- Use package DBMS_XMLGEN to generate purchase order in XML format
--   and store XMLType in table po_xml 
DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  pxml XMLType;
  cxml CLOB;
BEGIN
  -- get query context;
  qryCtx := DBMS_XMLGEN.newContext('SELECT pono,deref(cust_ref) customer,
                                           p.orderdate,
                                           p.shipdate,
                                           lineitems_ntab lineitems,
                                           shiptoaddr
                                      FROM po p');
  -- set maximum number of rows to be 1,
  DBMS_XMLGEN.setMaxRows(qryCtx, 1);
  -- set ROWSET tag to NULL and ROW tag to PurchaseOrder
  DBMS_XMLGEN.setRowSetTag(qryCtx, NULL);
  DBMS_XMLGEN.setRowTag(qryCtx, 'PurchaseOrder');
  LOOP 
    -- get purchase order in XML format
    pxml := DBMS_XMLGEN.getXMLType(qryCtx);
    -- if there were no rows processed, then quit
    EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
    -- Store XMLType po in po_xml table (get the pono out)
    INSERT INTO po_xml_tab(poid, poDoc)
      VALUES(pxml.extract('//PONO/text()').getNumberVal(), pxml);
  END LOOP;
END;
/

This query then produces two XML purchase-order documents:

SELECT x.podoc.getCLOBVal() xpo FROM po_xml_tab x;

XPO
---------------------------------------------------
 <PurchaseOrder>
  <PONO>1001</PONO>
  <CUSTOMER>
   <CUSTNO>1</CUSTNO>
   <CUSTNAME>Jean Nance</CUSTNAME>
   <ADDRESS>
    <STREET>2 Avocet Drive</STREET>
    <CITY>Redwood Shores</CITY>
    <STATE>CA</STATE>
    <ZIP>95054</ZIP>
   </ADDRESS>
   <PHONELIST>
    <VARCHAR2>415-555-1212</VARCHAR2>
   </PHONELIST>
  </CUSTOMER>
  <ORDERDATE>10-APR-97</ORDERDATE>
  <SHIPDATE>10-MAY-97 12.00.00.000000 AM</SHIPDATE>
  <LINEITEMS>
   <LINEITEM_TYP LineItemNo="1">
    <ITEM StockNo="1534">
     <PRICE>2234</PRICE>
     <TAXRATE>2</TAXRATE>
    </ITEM>
    <QUANTITY>12</QUANTITY>
    <DISCOUNT>0</DISCOUNT>
   </LINEITEM_TYP>
   <LINEITEM_TYP LineItemNo="2">
    <ITEM StockNo="1535">
     <PRICE>3456.23</PRICE>
     <TAXRATE>2</TAXRATE>
    </ITEM>
    <QUANTITY>10</QUANTITY>
    <DISCOUNT>10</DISCOUNT>
   </LINEITEM_TYP>
  </LINEITEMS>
  <SHIPTOADDR/>
 </PurchaseOrder>
 
 <PurchaseOrder>
  <PONO>2001</PONO>
  <CUSTOMER>
   <CUSTNO>2</CUSTNO>
   <CUSTNAME>John Nike</CUSTNAME>
   <ADDRESS>
    <STREET>323 College Drive</STREET>
    <CITY>Edison</CITY>
    <STATE>NJ</STATE>
    <ZIP>08820</ZIP>
   </ADDRESS>
   <PHONELIST>
    <VARCHAR2>609-555-1212</VARCHAR2>
    <VARCHAR2>201-555-1212</VARCHAR2>
   </PHONELIST>
  </CUSTOMER>
  <ORDERDATE>20-APR-97</ORDERDATE>
  <SHIPDATE>20-MAY-97 12.00.00.000000 AM</SHIPDATE>
  <LINEITEMS>
   <LINEITEM_TYP LineItemNo="1">
    <ITEM StockNo="1004">
     <PRICE>6750</PRICE>
     <TAXRATE>2</TAXRATE>
    </ITEM>
    <QUANTITY>1</QUANTITY>
    <DISCOUNT>0</DISCOUNT>
   </LINEITEM_TYP>
   <LINEITEM_TYP LineItemNo="2">
    <ITEM StockNo="1011">
     <PRICE>4500.23</PRICE>
     <TAXRATE>2</TAXRATE>
    </ITEM>
    <QUANTITY>2</QUANTITY>
    <DISCOUNT>1</DISCOUNT>
   </LINEITEM_TYP>
  </LINEITEMS>
  <SHIPTOADDR>
   <STREET>55 Madison Ave</STREET>
   <CITY>Madison</CITY>
   <STATE>WI</STATE>
   <ZIP>53715</ZIP>
  </SHIPTOADDR>
 </PurchaseOrder>
 
2 rows selected.

Example 17-29 DBMS_XMLGEN: Generating a New Context Handle from a REF Cursor

This example shows how to open a cursor variable for a query and use that cursor variable to create a new context handle for DBMS_XMLGEN.

CREATE TABLE emp_tab(emp_id       NUMBER PRIMARY KEY,
                     name         VARCHAR2(20),
                     dept_id      NUMBER);
Table created.
INSERT INTO emp_tab VALUES(122, 'Scott',  301);
1 row created.
INSERT INTO emp_tab VALUES(123, 'Mary',   472);
1 row created.
INSERT INTO emp_tab VALUES(124, 'John',   93);
1 row created.
INSERT INTO emp_tab VALUES(125, 'Howard', 488);
1 row created.
INSERT INTO emp_tab VALUES(126, 'Sue',    16);
1 row created.
COMMIT;
 
DECLARE
  ctx     NUMBER;
  maxrow  NUMBER;
  xmldoc  CLOB;
  refcur  SYS_REFCURSOR;
BEGIN
  DBMS_LOB.createtemporary(xmldoc, TRUE);
  maxrow := 3;
  OPEN refcur FOR 'SELECT * FROM emp_tab WHERE ROWNUM <= :1' USING maxrow;
  ctx := DBMS_XMLGEN.newContext(refcur);
   -- xmldoc will have 3 rows
  DBMS_XMLGEN.getXML(ctx, xmldoc, DBMS_XMLGEN.NONE);
  DBMS_OUTPUT.put_line(xmldoc);
  DBMS_LOB.freetemporary(xmldoc);
  CLOSE refcur;
  DBMS_XMLGEN.closeContext(ctx);
END;
/
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMP_ID>122</EMP_ID>
  <NAME>Scott</NAME>
  <DEPT_ID>301</DEPT_ID>
 </ROW>
 <ROW>
  <EMP_ID>123</EMP_ID>
  <NAME>Mary</NAME>
  <DEPT_ID>472</DEPT_ID>
 </ROW>
 <ROW>
  <EMP_ID>124</EMP_ID>
  <NAME>John</NAME>
  <DEPT_ID>93</DEPT_ID>
 </ROW>
</ROWSET>
 
PL/SQL procedure successfully completed.

See Also:

Oracle Database PL/SQL Language Reference for more information about cursor variables (REF CURSOR)

Example 17-30 DBMS_XMLGEN: Specifying NULL Handling

CREATE TABLE emp_tab(emp_id       NUMBER PRIMARY KEY,
                     name         VARCHAR2(20),
                     dept_id      NUMBER);
Table created.
INSERT INTO emp_tab VALUES(30, 'Scott', NULL);
1 row created.
INSERT INTO emp_tab VALUES(31, 'Mary', NULL);
1 row created.
INSERT INTO emp_tab VALUES(40, 'John', NULL);
1 row created.
COMMIT;
CREATE TABLE temp_clob_tab(result CLOB);
Table created.
 
DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  result CLOB;
BEGIN
  qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM emp_tab where name = :NAME');
  -- Set the row header to be EMPLOYEE
  DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE');
  -- Drop nulls
  DBMS_XMLGEN.setBindValue(qryCtx, 'NAME', 'Scott');
  DBMS_XMLGEN.setNullHandling(qryCtx, DBMS_XMLGEN.DROP_NULLS);
  result := DBMS_XMLGEN.getXML(qryCtx);
  INSERT INTO temp_clob_tab VALUES(result);
  -- Null attribute
  DBMS_XMLGEN.setBindValue(qryCtx, 'NAME', 'Mary');
  DBMS_XMLGEN.setNullHandling(qryCtx, DBMS_XMLGEN.NULL_ATTR);
  result := DBMS_XMLGEN.getXML(qryCtx);
  INSERT INTO temp_clob_tab VALUES(result);
  -- Empty tag
  DBMS_XMLGEN.setBindValue(qryCtx, 'NAME', 'John');
  DBMS_XMLGEN.setNullHandling(qryCtx, DBMS_XMLGEN.EMPTY_TAG);
  result := DBMS_XMLGEN.getXML(qryCtx);
  INSERT INTO temp_clob_tab VALUES(result);
  --Close context
  DBMS_XMLGEN.closeContext(qryCtx);
END;
/
 
PL/SQL procedure successfully completed.
 
SELECT * FROM temp_clob_tab;
 
RESULT
-------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <EMPLOYEE>
  <EMP_ID>30</EMP_ID>
  <NAME>Scott</NAME>
 </EMPLOYEE>
</ROWSET>
 
<?xml version="1.0"?>
<ROWSET xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">
 <EMPLOYEE>
  <EMP_ID>31</EMP_ID>
  <NAME>Mary</NAME>
  <DEPT_ID xsi:nil = "true"/>
 </EMPLOYEE>
</ROWSET>
 
<?xml version="1.0"?>
<ROWSET>
 <EMPLOYEE>
  <EMP_ID>40</EMP_ID>
  <NAME>John</NAME>
  <DEPT_ID/>
 </EMPLOYEE>
</ROWSET>
 
3 rows selected.

Example 17-31 DBMS_XMLGEN: Generating Recursive XML with a Hierarchical Query

Function DBMS_XMLGEN.newContextFromHierarchy takes as argument a hierarchical query string, which is typically formulated with a CONNECT BY clause. It returns a context that can be used to generate a hierarchical XML document with recursive elements.

The hierarchical query returns two columns, the level number (a pseudocolumn 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().

CREATE TABLE sqlx_display(id NUMBER, xmldoc XMLType);
Table created.
DECLARE
  qryctx DBMS_XMLGEN.ctxhandle;
  result XMLType;
BEGIN
  qryctx := 
    DBMS_XMLGEN.newcontextFromHierarchy(
      'SELECT level, 
              XMLElement("employees", 
                         XMLElement("enumber", employee_id),
                         XMLElement("name", last_name),
                         XMLElement("Salary", salary),
                         XMLElement("Hiredate", hire_date))
         FROM hr.employees
         START WITH last_name=''De Haan'' CONNECT BY PRIOR employee_id=manager_id
         ORDER SIBLINGS BY hire_date');
  result := DBMS_XMLGEN.getxmltype(qryctx);
  DBMS_OUTPUT.put_line('<result num rows>');
  DBMS_OUTPUT.put_line(to_char(DBMS_XMLGEN.getNumRowsProcessed(qryctx)));
  DBMS_OUTPUT.put_line('</result num rows>');
  INSERT INTO sqlx_display VALUES (2, result);
  COMMIT;
  DBMS_XMLGEN.closecontext(qryctx);
END;
/
<result num rows>
6
</result num rows>
PL/SQL procedure successfully completed.

SELECT xmldoc FROM sqlx_display WHERE id = 2;

XMLDOC
-----------------------------------------------------
<?xml version="1.0"?>
<employees>
  <enumber>102</enumber>
  <name>De Haan</name>
  <Salary>17000</Salary>
  <Hiredate>1993-01-13</Hiredate>
  <employees>
    <enumber>103</enumber>
    <name>Hunold</name>
    <Salary>9000</Salary>
    <Hiredate>1990-01-03</Hiredate>
    <employees>
      <enumber>104</enumber>
      <name>Ernst</name>
      <Salary>6000</Salary>
      <Hiredate>1991-05-21</Hiredate>
    </employees>
    <employees>
      <enumber>105</enumber>
      <name>Austin</name>
      <Salary>4800</Salary>
      <Hiredate>1997-06-25</Hiredate>
    </employees>
    <employees>
      <enumber>106</enumber>
      <name>Pataballa</name>
      <Salary>4800</Salary>
      <Hiredate>1998-02-05</Hiredate>
    </employees>
    <employees>
      <enumber>107</enumber>
      <name>Lorentz</name>
      <Salary>4200</Salary>
      <Hiredate>1999-02-07</Hiredate>
    </employees>
  </employees>
</employees>

1 row selected.

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

CREATE TABLE gg(x XMLType);
Table created.
DECLARE
  qryctx DBMS_XMLGEN.ctxhandle;
  result CLOB;
BEGIN
  qryctx := DBMS_XMLGEN.newcontextFromHierarchy(
              'SELECT level,
                      XMLElement("NAME", last_name) AS myname FROM hr.employees
               CONNECT BY PRIOR employee_id=manager_id
               START WITH employee_id = 102');
  DBMS_XMLGEN.setRowSetTag(qryctx, 'mynum_hierarchy');
  result:=DBMS_XMLGEN.getxml(qryctx);
  DBMS_OUTPUT.put_line('<result num rows>');
  DBMS_OUTPUT.put_line(to_char(DBMS_XMLGEN.getNumRowsProcessed(qryctx)));
  DBMS_OUTPUT.put_line('</result num rows>');
  INSERT INTO gg VALUES(XMLType(result));
  COMMIT;
  DBMS_XMLGEN.closecontext(qryctx);
END;
/
<result num rows>
6
</result num rows>
PL/SQL procedure successfully completed.

SELECT * FROM gg;
 
X
----------------------------------------------------------
<?xml version="1.0"?>
<mynum_hierarchy>
  <NAME>De Haan
    <NAME>Hunold
      <NAME>Ernst</NAME>
      <NAME>Austin</NAME>
      <NAME>Pataballa</NAME>
      <NAME>Lorentz</NAME>
    </NAME>
  </NAME>
</mynum_hierarchy>
 
1 row selected.

Example 17-32 DBMS_XMLGEN: Binding Query Variables with Method setBindValue()

If the query string used to create a context contains host variables, you can use method setBindValue() to give the variables values before query execution.

-- Bind one variable
DECLARE
  ctx NUMBER;
  xmldoc CLOB;
BEGIN
  ctx := DBMS_XMLGEN.newContext(
           'SELECT * FROM employees WHERE employee_id = :NO');
  DBMS_XMLGEN.setBindValue(ctx, 'NO', '145');
  xmldoc := DBMS_XMLGEN.getXML(ctx);
  DBMS_OUTPUT.put_line(xmldoc);
  DBMS_XMLGEN.closeContext(ctx);
EXCEPTION
  WHEN OTHERS THEN DBMS_XMLGEN.closeContext(ctx);
  RAISE;
END;
/
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPLOYEE_ID>145</EMPLOYEE_ID>
  <FIRST_NAME>John</FIRST_NAME>
  <LAST_NAME>Russell</LAST_NAME>
  <EMAIL>JRUSSEL</EMAIL>
  <PHONE_NUMBER>011.44.1344.429268</PHONE_NUMBER>
  <HIRE_DATE>01-OCT-96</HIRE_DATE>
  <JOB_ID>SA_MAN</JOB_ID>
  <SALARY>14000</SALARY>
  <COMMISSION_PCT>.4</COMMISSION_PCT>
  <MANAGER_ID>100</MANAGER_ID>
  <DEPARTMENT_ID>80</DEPARTMENT_ID>
 </ROW>
</ROWSET>
 
PL/SQL procedure successfully completed.

--Bind one variable twice with different values
DECLARE
  ctx NUMBER;
  xmldoc CLOB;
BEGIN
  ctx := DBMS_XMLGEN.newContext('SELECT * FROM employees
                                   WHERE hire_date = :MDATE');
  DBMS_XMLGEN.setBindValue(ctx, 'MDATE', '01-OCT-96');
  xmldoc := DBMS_XMLGEN.getXML(ctx);
  DBMS_OUTPUT.put_line(xmldoc);
  DBMS_XMLGEN.setBindValue(ctx, 'MDATE', '10-MAR-97');
  xmldoc := DBMS_XMLGEN.getXML(ctx);
  DBMS_OUTPUT.put_line(xmldoc);
  DBMS_XMLGEN.closeContext(ctx);
EXCEPTION
  WHEN OTHERS THEN DBMS_XMLGEN.closeContext(ctx);
  RAISE;
END;
/
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPLOYEE_ID>145</EMPLOYEE_ID>
  <FIRST_NAME>John</FIRST_NAME>
  <LAST_NAME>Russell</LAST_NAME>
  <EMAIL>JRUSSEL</EMAIL>
  <PHONE_NUMBER>011.44.1344.429268</PHONE_NUMBER>
  <HIRE_DATE>01-OCT-96</HIRE_DATE>
  <JOB_ID>SA_MAN</JOB_ID>
  <SALARY>14000</SALARY>
  <COMMISSION_PCT>.4</COMMISSION_PCT>
  <MANAGER_ID>100</MANAGER_ID>
  <DEPARTMENT_ID>80</DEPARTMENT_ID>
 </ROW>
</ROWSET>
 
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPLOYEE_ID>147</EMPLOYEE_ID>
  <FIRST_NAME>Alberto</FIRST_NAME>
  <LAST_NAME>Errazuriz</LAST_NAME>
  <EMAIL>AERRAZUR</EMAIL>
  <PHONE_NUMBER>011.44.1344.429278</PHONE_NUMBER>
  <HIRE_DATE>10-MAR-97</HIRE_DATE>
  <JOB_ID>SA_MAN</JOB_ID>
  <SALARY>12000</SALARY>
  <COMMISSION_PCT>.3</COMMISSION_PCT>
  <MANAGER_ID>100</MANAGER_ID>
  <DEPARTMENT_ID>80</DEPARTMENT_ID>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>159</EMPLOYEE_ID>
  <FIRST_NAME>Lindsey</FIRST_NAME>
  <LAST_NAME>Smith</LAST_NAME>
  <EMAIL>LSMITH</EMAIL>
  <PHONE_NUMBER>011.44.1345.729268</PHONE_NUMBER>
  <HIRE_DATE>10-MAR-97</HIRE_DATE>
  <JOB_ID>SA_REP</JOB_ID>
  <SALARY>8000</SALARY>
  <COMMISSION_PCT>.3</COMMISSION_PCT>
  <MANAGER_ID>146</MANAGER_ID>
  <DEPARTMENT_ID>80</DEPARTMENT_ID>
 </ROW>
</ROWSET>
PL/SQL procedure successfully completed.
-- Bind two variables 
DECLARE
  ctx NUMBER;
  xmldoc CLOB;
BEGIN
  ctx := DBMS_XMLGEN.newContext('SELECT * FROM employees
                                   WHERE employee_id = :NO
                                     AND hire_date = :MDATE');
  DBMS_XMLGEN.setBindValue(ctx, 'NO', '145');
  DBMS_XMLGEN.setBindValue(ctx, 'MDATE', '01-OCT-96');
  xmldoc := DBMS_XMLGEN.getXML(ctx);
  DBMS_OUTPUT.put_line(xmldoc);
  DBMS_XMLGEN.closeContext(ctx);
EXCEPTION
  WHEN OTHERS THEN DBMS_XMLGEN.closeContext(ctx);
  RAISE;
END;
/
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPLOYEE_ID>145</EMPLOYEE_ID>
  <FIRST_NAME>John</FIRST_NAME>
  <LAST_NAME>Russell</LAST_NAME>
  <EMAIL>JRUSSEL</EMAIL>
  <PHONE_NUMBER>011.44.1344.429268</PHONE_NUMBER>
  <HIRE_DATE>01-OCT-96</HIRE_DATE>
  <JOB_ID>SA_MAN</JOB_ID>
  <SALARY>14000</SALARY>
  <COMMISSION_PCT>.4</COMMISSION_PCT>
  <MANAGER_ID>100</MANAGER_ID>
  <DEPARTMENT_ID>80</DEPARTMENT_ID>
 </ROW>
</ROWSET>
PL/SQL procedure successfully completed.

Generating XML Using SQL Function SYS_XMLGEN

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

Example 17-33 Using SYS_XMLGEN to Create XML

In this query, SQL function sys_XMLGen queries XML instances and returns an XML document for each row of relational data:

SELECT sys_XMLGen(employee_id) AS "result"
  FROM employees WHERE first_name LIKE 'John%';

The resulting XML documents are as follows:

result
---------------
<?xml version="1.0"?>
<EMPLOYEE_ID>110</EMPLOYEE_ID>
 
<?xml version="1.0"?>
<EMPLOYEE_ID>139</EMPLOYEE_ID>
 
<?xml version="1.0"?>
<EMPLOYEE_ID>145</EMPLOYEE_ID>
 
3 rows selected.

SYS_XMLGEN Syntax

SQL function sys_XMLGen takes as argument a scalar value, object type, or XMLType instance to be converted to an XML document. It also takes an optional XMLFormat object (previously called XMLGenFormatType), which you can use to specify formatting options for the resulting XML document. The syntax is shown in Figure 17-15.

Figure 17-15 SYS_XMLGEN Syntax

Description of Figure 17-15 follows
Description of "Figure 17-15 SYS_XMLGEN Syntax"

Expression expr evaluates to a particular row and column of the database. It can be a scalar value, a user-defined data-type instance, or an XMLType instance.

By default, the elements of the XML document match the 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.

You can use a WHERE clause in a query to suppress <ROW/> tags with sys_XMLGen, if you do not want NULL values represented:

SELECT sys_XMLGen(x) FROM table_name WHERE x IS NOT NULL;

Example 17-34 SYS_XMLGEN: Generating an XML Element from a Database Column

The following example retrieves the employee first_name from sample-schema table hr.employees, where the employee_id value is 110, and generates an XMLType instance containing an XML document with an FIRST_NAME element.

SELECT sys_XMLGen(first_name).getStringVal()      
  FROM employees
  WHERE employee_id = 110;

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

1 row selected.

Advantages of Using SYS_XMLGEN

SQL function sys_XMLGen has the following advantages:

sys_XMLGen creates an XML document from a user-defined data-type instance, a scalar value, or an XMLType instance. It returns an XMLType instance.

sys_XMLGen also accepts an optional XMLFormat object as argument, which you can use to customize the result. A NULL format object implies that the default mapping action is to be used.

Using XMLFormat Object Type

You can use the XMLFormat object to specify formatting arguments for SQL functions sys_XMLGen and sys_XMLAgg.

Function sys_XMLGen returns an XMLType instance containing an XML document. Oracle Database provides the XMLFormat object to format the output of sys_XMLGen.

Table 17-2 lists the attributes of object XMLFormat.

Table 17-2 Attributes of the XMLFormat Object

Attribute Data Type 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 column name is used as the default value. Otherwise, the default value is ROWSET. When schemaType is set to USE_GIVEN_SCHEMA, this attribute also provides the name of the XML schema element.

schemaType

VARCHAR2(100)

The type of schema generation for the output document. Valid values are 'NO_SCHEMA' and 'USE_GIVEN_SCHEMA'. The default value is 'NO_SCHEMA'.

schemaName

VARCHAR2(4000)

The name of the target schema used if schemaType is 'USE_GIVEN_SCHEMA'. If you specify schemaName, then the enclosing tag is used 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 be used if WITH_SCHEMA is specified. If this attribute is not specified, then a relative URL reference is used for the URL to the types.

processingIns

VARCHAR2(4000)

User-provided processing instructions. They are appended to the top of the function output, before the element.


You can use method createFormat() to implement the XMLFormat object. Method createFormat() of object XMLFormat accepts as arguments the enclosing element name, the XML-schema type, and the XML-schema name. Default values are provided for the other XMLFormat attributes.

See Also:

Example 17-35 SYS_XMLGEN: Converting a Scalar Value to XML Element Contents

SQL function sys_XMLGen converts a scalar value to an element that contains the scalar value. For example, the following query returns an XML document that contains the employee_id value as an element containing that value:

SELECT sys_XMLGen(employee_id) FROM hr.employees WHERE ROWNUM < 2;
 
SYS_XMLGEN(EMPLOYEE_ID)
---------------------------
<?xml version="1.0"?>
<EMPLOYEE_ID>100</EMPLOYEE_ID>
 
1 row selected.

The enclosing element name, in this case EMPLOYEE_ID, is derived from the column name passed to sys_XMLGen. The query result is a single row containing an XMLType instance that corresponds to a complete XML document.

Example 17-36 SYS_XMLGEN: Default Element Name ROW

In Example 17-35, the column name EMPLOYEE_ID is used by default for the XML element name. If the column name cannot be derived directly, then the default name ROW is used instead:

SELECT sys_XMLGen(employee_id*2) FROM hr.employees WHERE ROWNUM < 2;
 
SYS_XMLGEN(EMPLOYEE_ID*2)
-------------------------
<?xml version="1.0"?>
<ROW>200</ROW>
 
1 row selected.

In this example, the argument to sys_XMLGen is not a simple column name, so the name of the output element tag cannot be a column name – the default element name, ROW, is used.

You can override the default ROW tag by supplying an XMLFormat object as the second sys_XMLGen argument – see Example 17-37 for an example.

Example 17-37 Overriding the Default Element Name: Using SYS_XMLGEN with XMLFormat

In this example, a formatting argument is supplied to sys_XMLGen, to name the element explicitly:

SELECT sys_XMLGen(employee_id*2, 
                  XMLFormat.createformat('DOUBLE_ID')).getclobval()
 FROM hr.employees WHERE ROWNUM < 2;
 
SYS_XMLGEN(EMPLOYEE_ID*2,XMLFORMAT.CREATEFORMAT('EMPLOYEE_ID')).GETCLOBVAL()
----------------------------------------------------------------------------
<?xml version="1.0"?>
<DOUBLE_ID>200</DOUBLE_ID>
 
1 row selected.

Example 17-38 SYS_XMLGEN: Converting a User-Defined Data-Type Instance to XML

When you provide a user-defined data-type instance as an argument to sys_XMLGen, the instance is canonically mapped to an XML document. In this mapping, the user-defined data-type attributes are mapped to XML elements.

Any data-type attributes with names that start with an at sign (@) are mapped to attributes of the preceding XML element. User-defined data-type instances can be used to obtain nesting in the resulting XML document.

You can generate hierarchical XML for the employee-and-department example (see "Generating XML Using DBMS_XMLGEN") as follows:

CREATE OR REPLACE TYPE hr.emp_t AS OBJECT(empno    NUMBER(6),
                                          ename    VARCHAR2(25),
                                          job      VARCHAR2(10),
                                          mgr      NUMBER(6),
                                          hiredate DATE,
                                          sal      NUMBER(8,2),
                                          comm     NUMBER(2,2));
/
Type created.
CREATE OR REPLACE TYPE hr.emplist_t AS TABLE OF emp_t;
/
Type created.
CREATE OR REPLACE TYPE hr.dept_t AS OBJECT(deptno  NUMBER(4),
                                           dname   VARCHAR2(30),
                                           loc     VARCHAR2(4),
                                           emplist emplist_t);
/
Type created.
 
SELECT sys_XMLGen(
         dept_t(department_id,
                department_name,
                d.location_id,
                CAST(MULTISET(SELECT emp_t(e.employee_id, e.last_name, e.job_id,
                                           e.manager_id, e.hire_date, e.salary,
                                           e.commission_pct)
                                FROM hr.employees e
                                WHERE e.department_id = d.department_id)
                     AS emplist_t))).getCLOBVal()
  AS deptxml
  FROM hr.departments d WHERE department_id = 10 OR department_id = 20;

SQL function MULTISET treats the result of the subset of employees working in the department as a list, and the CAST then assigns this to the appropriate collection type. A department-type (dept_t) element is wrapped around this to create the XML data for the object instance.

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

DEPTXML
-------------------------------------
<?xml version="1.0"?>
<ROW>
 <DEPTNO>10</DEPTNO>
 <DNAME>Administration</DNAME>
 <LOC>1700</LOC>
 <EMPLIST>
  <EMP_T>
   <EMPNO>200</EMPNO>
   <ENAME>Whalen</ENAME>
   <JOB>AD_ASST</JOB>
   <MGR>101</MGR>
   <HIREDATE>17-SEP-87</HIREDATE>
   <SAL>4400</SAL>
  </EMP_T>
 </EMPLIST>
</ROW>
 
<?xml version="1.0"?>
<ROW>
 <DEPTNO>20</DEPTNO>
 <DNAME>Marketing</DNAME>
 <LOC>1800</LOC>
 <EMPLIST>
  <EMP_T>
   <EMPNO>201</EMPNO>
   <ENAME>Hartstein</ENAME>
   <JOB>MK_MAN</JOB>
   <MGR>100</MGR>
   <HIREDATE>17-FEB-96</HIREDATE>
   <SAL>13000</SAL>
  </EMP_T>
  <EMP_T>
   <EMPNO>202</EMPNO>
   <ENAME>Fay</ENAME>
   <JOB>MK_REP</JOB>
   <MGR>201</MGR>
   <HIREDATE>17-AUG-97</HIREDATE>
   <SAL>6000</SAL>
  </EMP_T>
 </EMPLIST>
</ROW>
 
2 rows selected.

Note:

The difference between using SQL function sys_XMLGen and PL/SQL package DBMS_XMLGEN is apparent from the preceding example. Function sys_XMLGen works inside SQL queries, and operates on the expressions and columns within the rows; package DBMS_XMLGEN works on the entire result set.

Example 17-39 SYS_XMLGEN: Converting an XMLType Instance

If you pass an XML document to function sys_XMLGen, this function encloses the document (or fragment) with an element, whose tag name is the default ROW, or the name passed in through the XMLFormat formatting object. This functionality can be used to turn XML fragments into well-formed documents. Consider this XML data:

CREATE TABLE po_xml_tab(podoc XMLType);
Table created.
INSERT INTO po_xml_tab VALUES(XMLType('<DOCUMENT>
                                         <EMPLOYEE>
                                           <ENAME>John</ENAME>
                                           <EMPNO>200</EMPNO>
                                         </EMPLOYEE>
                                         <EMPLOYEE>
                                           <ENAME>Jack</ENAME>
                                           <EMPNO>400</EMPNO>
                                         </EMPLOYEE>
                                         <EMPLOYEE>
                                           <ENAME>Joseph</ENAME>
                                           <EMPNO>300</EMPNO>
                                         </EMPLOYEE>
                                       </DOCUMENT>'));
1 row created.
COMMIT;

This query extracts ENAME elements:

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

The query result is an XML document fragment:

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

You can make such a fragment into a valid XML document by calling sys_XMLGen to wrap a root element around the fragment, as follows:

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

This places a ROW element around the fragment, as follows:

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

Note:

If the input to sys_XMLGen is a column, then the column name is used as the default element name. You can override the element name using the XMLFormat formatting object as a second argument to sys_XMLGen. See "Using XMLFormat Object Type".

Example 17-40 Using SYS_XMLGEN with Object Views

For any undefined entities here, refer to the code in Example 17-28.

-- Create purchase order object type
CREATE OR REPLACE TYPE po_typ AUTHID CURRENT_USER
  AS OBJECT(pono            NUMBER,
            customer        customer_typ,
            orderdate       DATE,
            shipdate        TIMESTAMP,
            lineitems_ntab  lineitems_ntabtyp,
            shiptoaddr      address_typ)
/
--Purchase order view
CREATE OR REPLACE VIEW po OF po_typ
  WITH OBJECT IDENTIFIER (PONO)
  AS SELECT p.pono, customer_typ(p.custno, c.custname, c.address, c.phonelist),
            p.orderdate, p.shipdate,
            CAST(MULTISET(
                   SELECT 
                     lineitem_typ(l.lineitemno,
                                  stockitem_typ(l.stockno, s.price, s.taxrate),
                                                l.quantity, l.discount)
                     FROM lineitems_tab l, stock_tab s
                     WHERE l.pono = p.pono AND s.stockno=l.stockno)
                 AS lineitems_ntabtyp),
            address_typ(p.tostreet, p.tocity, p.tostate, p.tozip)
       FROM po_tab p, customer c
       WHERE p.custno=c.custno;
 
-- Use sys_XMLGen to generate PO in XML format
SELECT  sys_XMLGen(OBJECT_VALUE,
                   XMLFormat.createFormat('PurchaseOrder')).getCLOBVal() PO
  FROM po p 
  WHERE p.pono=1001;

The query returns the purchase order in XML format:

PO
----------------------------------------------
<?xml version="1.0"?>
<PurchaseOrder>
 <PONO>1001</PONO>
 <CUSTOMER>
  <CUSTNO>1</CUSTNO>
  <CUSTNAME>Jean Nance</CUSTNAME>
  <ADDRESS>
   <STREET>2 Avocet Drive</STREET>
   <CITY>Redwood Shores</CITY>
   <STATE>CA</STATE>
   <ZIP>95054</ZIP>
  </ADDRESS>
  <PHONELIST>
   <VARCHAR2>415-555-1212</VARCHAR2>
  </PHONELIST>
 </CUSTOMER>
 <ORDERDATE>10-APR-97</ORDERDATE>
 <SHIPDATE>10-MAY-97 12.00.00.000000 AM</SHIPDATE>
 <LINEITEMS_NTAB>
  <LINEITEM_TYP LineItemNo="1">
   <ITEM StockNo="1534">
    <PRICE>2234</PRICE>
    <TAXRATE>2</TAXRATE>
   </ITEM>
   <QUANTITY>12</QUANTITY>
   <DISCOUNT>0</DISCOUNT>
  </LINEITEM_TYP>
  <LINEITEM_TYP LineItemNo="2">
   <ITEM StockNo="1535">
    <PRICE>3456.23</PRICE>
    <TAXRATE>2</TAXRATE>
   </ITEM>
   <QUANTITY>10</QUANTITY>
   <DISCOUNT>10</DISCOUNT>
  </LINEITEM_TYP>
 </LINEITEMS_NTAB>
 <SHIPTOADDR/>
</PurchaseOrder>
 
1 row selected.

Generating XML Using SQL Function SYS_XMLAGG

SQL function sys_XMLAgg aggregates all XML documents or fragments represented by an expression 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 17-16 SYS_XMLAGG Syntax

Description of Figure 17-16 follows
Description of "Figure 17-16 SYS_XMLAGG Syntax"

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 a <xsql:include-xml> element should return a single row containing a single column. The column can be either a CLOB instance or a VARCHAR2 value. It must contain a well-formed XML document. The XML document is parsed and included in your XSQL page.

See Also:

Oracle XML Developer's Kit Programmer's Guide for information about element <xsql:include-xml> and XSQL pages

Example 17-41 Using XSQL Servlet <xsql:include-xml> with Nested XMLAgg Functions

This example uses nested calls to function XMLAgg to aggregate the results of a dynamically-constructed XML document containing departments and their employees into a single XML result document, which is wrapped in a DepartmentList element. The call to method getCLOBVal() provides XSQL Servlet with a CLOB value instead of an XMLType instance. To display the results, XSQL Servlet needs a special environment, such as the XSQL Command-Line Utility, XSQL Servlet installed in a Web server, Java Server Pages (JSP), or a Java XSQLRequest object.

<xsql:include-xml connection="orcl92" xmlns:xsql="urn:oracle-xsql">
  SELECT
    XMLElement("DepartmentList",
               XMLAgg(XMLElement(
                        "Department",
                        XMLAttributes(department_id as "Id"),
                        XMLForest(department_name as "Name"),
                        (SELECT XMLElement("Employees",
                                           XMLAgg(XMLElement(
                                                    "Employee",
                                                    XMLAttributes(
                                                      employee_id as "Id"),
                                                    XMLForest(
                                                      last_name as "Name",
                                                      salary as "Salary",
                                                      job_id as "Job"))))
                           FROM employees e 
                           WHERE e.department_id=d.department_id)))).getCLOBVal()
    FROM departments d
    ORDER BY department_name
</xsql:include-xml>

The query itself produces the following result:

XMLELEMENT("DEPARTMENTLIST",XMLAGG(XMLELEMENT("DEPARTMENT",XMLATTRIBUTES(DEPARTM
--------------------------------------------------------------------------------
<DepartmentList><Department Id="10"><Name>Administration</Name><Employees><Emplo
yee Id="200"><Name>Whalen</Name><Salary>4400</Salary><Job>AD_ASST</Job></Employe
e></Employees></Department><Department Id="20"><Name>Marketing</Name><Employees>
<Employee Id="201"><Name>Hartstein</Name><Salary>13000</Salary><Job>MK_MAN</Job>
</Employee><Employee Id="202"><Name>Fay</Name><Salary>6000</Salary><Job>MK_REP</
Job></Employee></Employees></Department>
...
</DepartmentList>
 
1 row selected.

Example 17-42 Using XSQL Servlet <xsql:include-xml> with XMLElement and XMLAgg

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

You can use the built-in Oracle Database XPath query features to extract an aggregate list of all purchase orders of the film Grand Illusion. This example uses the purchaseorder table in sample schema OE.

CONNECT oe/oe;
Connected.

SELECT
  XMLElement(
    "GrandIllusionOrders",
    XMLAgg(extract(OBJECT_VALUE,
                   '/PurchaseOrder/LineItems/*[Part[@Id="37429121924"]]')))
  FROM purchaseorder;

This produces the following result.

XMLELEMENT("GRANDILLUSIONORDERS",XMLAGG(EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LIN
--------------------------------------------------------------------------------
<GrandIllusionOrders>
  <LineItem ItemNumber="14">
    <Description>Grand Illusion</Description>
    <Part Id="37429121924" UnitPrice="39.95" Quantity="2"/>
  </LineItem>
  <LineItem ItemNumber="14">
    <Description>Grand Illusion</Description>
    <Part Id="37429121924" UnitPrice="39.95" Quantity="2"/>
  </LineItem>
  <LineItem ItemNumber="6">
    <Description>Grand Illusion</Description>
    <Part Id="37429121924" UnitPrice="39.95" Quantity="2"/>
  </LineItem>
  <LineItem ItemNumber="19">
    <Description>Grand Illusion</Description>
    <Part Id="37429121924" UnitPrice="39.95" Quantity="4"/>
  </LineItem>
  <LineItem ItemNumber="21">
    <Description>Grand Illusion</Description>
    <Part Id="37429121924" UnitPrice="39.95" Quantity="3"/>
  </LineItem>
  <LineItem ItemNumber="15">
    <Description>Grand Illusion</Description>
    <Part Id="37429121924" UnitPrice="39.95" Quantity="3"/>
  </LineItem>
  <LineItem ItemNumber="3">
    <Description>Grand Illusion</Description>
    <Part Id="37429121924" UnitPrice="39.95" Quantity="2"/>
  </LineItem>
  <LineItem ItemNumber="8">
    <Description>Grand Illusion</Description>
    <Part Id="37429121924" UnitPrice="39.95" Quantity="1"/>
  </LineItem>
  <LineItem ItemNumber="17">
    <Description>Grand Illusion</Description>
    <Part Id="37429121924" UnitPrice="39.95" Quantity="4"/>
  </LineItem>
</GrandIllusionOrders>

1 row selected.

To include this XMLType query result in your XSQL page, paste the query inside an <xsql:include-xml> element, and call method getCLOBVal(), so that the result is returned to the client as a CLOB value instead of as an XMLType instance:

<xsql:include-xml connection="orcl92" xmlns:xsql="urn:oracle-xsql">
  SELECT
    XMLElement(
      "GrandIllusionOrders",
      XMLAgg(
        extract(
          OBJECT_VALUE,
          '/PurchaseOrder/LineItems/*[Part[@Id="37429121924"]]'))).getCLOBval()
    FROM purchaseorder;
</xsql:include-xml>

SQL functions XMLElement and XMLAgg are used together here to 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 value that looks like this:

<LineItem>...</LineItem>
<LineItem>...</LineItem>
...

This is not well-formed XML because it does not have a single root element as required by the XML 1.0 recommendation. Functions XMLElement and XMLAgg work together to produce a well-formed result with single root element GrandIllusionOrders. 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 over where XSLT is executed: in the database, the middle-tier, or the client. For database execution, use SQL function XMLtransform (or the equivalent) in your query. For middle-tier execution, add <?xml-stylesheet?> at the top of your template page. For client execution, add attribute client="yes" to PI <?xml-stylesheet?>.With XSQL Pages, you can build pages that conditionally off-load style-sheet processing to the client, depending, for example, on what browser is used.

To improve performance and throughput, XSQL caches and pools XSLT style sheets (as well as database connections) in the middle tier. 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 transforming the same (or static) data repeatedly.XSQL Pages can include a mix of static XML and dynamically produced XML. You can take advantage of this by using the database to create only the dynamic part of the page.

Generating XML Using XML SQL Utility (XSU)

Oracle XML SQL Utility (XSU) can be used with Oracle Database to generate XML. You can use XSU l to generate XML on either the middle tier or the client. XSU also supports generating XML on tables with XMLType columns.

See Also:

Oracle XML Developer's Kit Programmer's Guide for information about 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 17-43 Using XMLAGG ORDER BY Clause

Consider this table:

CREATE TABLE dev_tab (dev         NUMBER,
                      dev_total   NUMBER,
                      devname     VARCHAR2(20));
Table created.
INSERT INTO dev_tab VALUES (16, 5,  'Alexis');
1 row created.
INSERT INTO dev_tab VALUES (2,  14, 'Han');
1 row created.
INSERT INTO dev_tab VALUES (1,  2,  'Jess');
1 row created.
INSERT INTO dev_tab VALUES (9,  88, 'Kurt');
1 row created.
COMMIT;

In this example, the result is aggregated according to the order of the dev column. (The result is shown here pretty-printed, for clarity.)

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

XMLAGG(XMLELEMENT("DEV",XMLATTRIBUTES(DEVAS"ID",DEV_TOTALAS"TOTAL"),DEVNAME)ORDE
--------------------------------------------------------------------------------
<Dev id="1" total="2">Jess</Dev>
<Dev id="2" total="14">Han</Dev>
<Dev id="9" total="88">Kurt</Dev>
<Dev id="16" total="5">Alexis</Dev>
 
1 row selected.

Using XMLTABLE to Return a Rowset

You can use standard SQL/XML function XMLTable to return a rowset with relevant portions of a document extracted as multiple rows, as shown in Example 17-44.

Example 17-44 Returning a Rowset using XMLTABLE

This example uses the purchaseorder table in sample schema OE.

CONNECT oe/oe;
Connected.

SELECT item.descr, item.partid
  FROM purchaseorder,
       XMLTable('/PurchaseOrder/LineItems/LineItem' PASSING OBJECT_VALUE
                COLUMNS descr  VARCHAR2(256) PATH 'Description',
                        partid VARCHAR2(14)  PATH 'Part/@Id') item
  WHERE item.partid = '715515012027'
     OR item.partid = '715515011921'
  ORDER BY partid;

This returns a rowset with just the descriptions and part IDs, ordered by part ID.

DESCR
--------------
PARTID
--------------
My Man Godfrey
715515011921
 
My Man Godfrey
715515011921

My Man Godfrey
715515011921

My Man Godfrey
715515011921
 
My Man Godfrey
715515011921
 
My Man Godfrey
715515011921
 
My Man Godfrey
715515011921
 
Mona Lisa
715515012027
 
Mona Lisa
715515012027
 
Mona Lisa
715515012027
 
Mona Lisa
715515012027
 
Mona Lisa
715515012027
 
Mona Lisa
715515012027
 
Mona Lisa
715515012027
 
Mona Lisa
715515012027
 
Mona Lisa
715515012027
 
16 rows selected.


Footnote Legend

Footnote 1: The SQL/XML standard requires argument data-type to be present, but it is optional in the Oracle XML DB implementation of the standard, for ease of use.