This chapter describes Oracle XML DB options for generating XML from the database. It explains the SQL/XML standard functions and Oracle Database-provided functions and packages for generating XML data from relational content.
This chapter contains these topics:
Oracle Database supports native XML generation. Oracle provides you with several options for generating or regenerating XML data when stored in:
Oracle Database, in general
Oracle Database in XMLTypes
columns and tables
The following discussion illustrates the Oracle XML DB options you can use to generate XML from Oracle Database.
You can generate XML data using any of the following standard SQL/XML functions supported by Oracle XML DB:
You can generate XML data using any of the following Oracle Database SQL functions:
XMLSEQUENCE SQL Function.
Only the cursor version of this function generates XML.
Generating XML Using SQL Function SYS_XMLGEN. This operates on rows, generating XML documents.
Generating XML Using SQL Function SYS_XMLAGG. This operates on groups of rows, aggregating several XML documents into one.
You can generate XML from SQL queries using PL/SQL package DBMS_XMLGEN
.
See Also:
Oracle Database PL/SQL Packages and Types Reference, description of DBMS_XMLGEN
You can generate XML using XSQL Pages Publishing Framework. XSQL Pages Publishing Framework, also known as XSQL Servlet, is part of the XDK for Java.
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.
This section describes Oracle XML DB SQL functions that you can use to generate XML data. Many of these functions belong to the SQL/XML standard, a SQL standard for XML:
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:
ISO/IEC JTC1/SC32 ("International Organization for Standardization and International Electrotechnical Committee Joint Technical Committee 1, Information technology, Subcommittee 32, Data Management and Interchange").
INCITS Technical Committee H2 ("INCITS" stands for "International Committee for Information Technology Standards"). INCITS is an Accredited Standards Development Organization operating under the policies and procedures of ANSI, the American National Standards Institute. Committee H2 is the committee responsible for SQL and SQL/MM.
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:
XMLSEQUENCE SQL Function.
Only the cursor version of this function generates XML.
Generating XML Using SQL Function SYS_XMLGEN. This operates on relational rows, generating XML documents.
Generating XML Using SQL Function SYS_XMLAGG. This operates on groups of relational rows, aggregating several XML documents into one.
All of the XML-generation SQL functions convert scalars and user-defined datatype instances to their canonical XML format. In this canonical mapping, user-defined datatype attributes are mapped to XML elements.
You use SQL function XMLElement
to construct XML instances 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.
The first argument to function XMLElement
(identifier
, in Figure 16-1) is an identifier that names the root XML element to be created. This root-element identifier need not be a column name or a column reference, and it cannot be an expression to be evaluated. If the identifier
is NULL
, then no root element is generated.
The optional XML-attributes-clause
argument of function XMLElement
specifies the attributes of the root element to be generated. Figure 16-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 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 16-2 XMLAttributes Clause Syntax (XMLATTRIBUTES)
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
(a string literal).
If an attribute value expression evaluates to NULL
, then no corresponding attribute is created. The datatype of an attribute value expression cannot be an object type or a collection.
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 16-1) and to attribute identifier aliases named with AS
clauses of XMLAttributes
(see Figure 16-2).
However, other XML data that is generated is escaped, to provide 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.
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 were used for XML, instead of the XML Schema standard formats. 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 16-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 formatsThis section provides examples that use SQL function XMLElement
.
Example 16-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 16-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 16-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 16-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 16-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 actual result is not pretty-printed).
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 16-6 XMLELEMENT: Generating an Element from a User-Defined Datatype Instance
Example 16-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.
SQL function XMLForest
produces a forest of XML elements from its arguments, which are expressions to be evaluated, with optional aliases. Figure 16-3 describes the XMLForest
syntax.
Each of the value expressions (value_expr
in Figure 16-3) is converted to XML format, and, optionally, identifier c_alias
is used as the attribute identifier.
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 16-7 XMLFOREST: Generating Elements with Attribute and Child Elements
This example generates an Emp
element for each employee, with a name
attribute and elements with the employee hire date and department as the content.
SELECT XMLElement("Emp", XMLAttributes(e.first_name ||' '|| e.last_name AS "name"), XMLForest(e.hire_date, e.department AS "department")) AS "RESULT" FROM employees e WHERE e.department_id = 20;
(The WHERE
clause is used here to keep the example brief.) This query produces the following XML result:
RESULT ------------------------------------- <Emp name="Michael Hartstein"> <HIRE_DATE>1996-02-17</HIRE_DATE> <department>20</department> </Emp> <Emp name="Pat Fay"> <HIRE_DATE>1997-08-17</HIRE_DATE> <department>20</department> </Emp> 2 rows selected.
Example 16-8 XMLFOREST: Generating an Element from a User-Defined Datatype Instance
You can also use SQL function XMLForest
to generate hierarchical XML from user-defined datatype instances.
SELECT XMLForest( dept_t(department_id, department_name, CAST (MULTISET (SELECT employee_id, last_name FROM hr.employees e WHERE e.department_id = d.department_id) AS emplist_t)) AS "Department") AS deptxml FROM hr.departments d WHERE department_id=10;
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 16-6 and Example 16-27.
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 16-4.
Example 16-9 XMLSEQUENCE Returns Only Top-Level Element Nodes
Function XMLSequence
returns only top-level element nodes. That is, it will not shred attributes or text nodes.
SELECT value(T).getstringval() Attribute_Value FROM table(XMLSequence(extract(XMLType('<A><B>V1</B><B>V2</B><B>V3</B></A>'), '/A/B'))) T; ATTRIBUTE_VALUE ---------------------- <B>V1</B> <B>V2</B> <B>V3</B> 3 rows selected.
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 shred 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.
Function XMLSequence
is essential for effective SQL queries involving XMLType
instances.
Example 16-10 XMLSEQUENCE: Generating One XML Document from Another
Consider the following XMLType table containing an XML document with employee information:
CREATE TABLE emp_xml_tab OF XMLType; Table created. INSERT INTO emp_xml_tab VALUES( XMLType('<EMPLOYEES> <EMP> <EMPNO>112</EMPNO> <EMPNAME>Joe</EMPNAME> <SALARY>50000</SALARY> </EMP> <EMP> <EMPNO>217</EMPNO> <EMPNAME>Jane</EMPNAME> <SALARY>60000</SALARY> </EMP> <EMP> <EMPNO>412</EMPNO> <EMPNAME>Jack</EMPNAME> <SALARY>40000</SALARY> </EMP> </EMPLOYEES>')); 1 row created. COMMIT;
To create a new XML document containing only employees who earn $50,000 or more, you can use the following query:
SELECT sys_XMLAgg(value(em), XMLFormat('EMPLOYEES')) FROM emp_xml_tab doc, table(XMLSequence(extract(value(doc), '/EMPLOYEES/EMP'))) em WHERE extractValue(value(em), '/EMP/SALARY') >= 50000;
These are the steps involved in this query:
Function extract
returns a fragment of EMP
elements.
Function XMLSequence
gathers a collection of these top-level elements into XMLType
instances and returns that.
Function table
makes a table value from the collection. The table value is then used in the query FROM
clause.
The query returns the following XML document:
SYS_XMLAGG(VALUE(EM),XMLFORMAT('EMPLOYEES')) -------------------------------------------- <?xml version="1.0"?> <EMPLOYEES> <EMP> <EMPNO>112</EMPNO> <EMPNAME>Joe</EMPNAME> <SALARY>50000</SALARY> </EMP> <EMP> <EMPNO>217</EMPNO> <EMPNAME>Jane</EMPNAME> <SALARY>60000</SALARY> </EMP> </EMPLOYEES> 1 row selected.
Example 16-11 XMLSEQUENCE: Generate a Document for Each Row of a Cursor
In this example, SQL function XMLSequence
is used to create an XML document for each row of a cursor expression, and it returns an XMLSequenceType
value (a varray of XMLType
instances).
SELECT value(em).getClobVal() AS "XMLTYPE" FROM table(XMLSequence(Cursor(SELECT * FROM hr.employees WHERE employee_id = 104))) em;
This query returns the following XML:
XMLTYPE -------------------------------------------------- <ROW> <EMPLOYEE_ID>104</EMPLOYEE_ID> <FIRST_NAME>Bruce</FIRST_NAME> <LAST_NAME>Ernst</LAST_NAME> <EMAIL>BERNST</EMAIL> <PHONE_NUMBER>590.423.4568</PHONE_NUMBER> <HIRE_DATE>21-MAY-91</HIRE_DATE> <JOB_ID>IT_PROG</JOB_ID> <SALARY>6000</SALARY> <MANAGER_ID>103</MANAGER_ID> <DEPARTMENT_ID>60</DEPARTMENT_ID> </ROW> 1 row selected.
The tag used for each row can be changed using the XMLFormat
object.
Example 16-12 XMLSEQUENCE: Unnesting Collections in XML Documents into SQL Rows
Because SQL function XMLSequence
is a table function, it can be used to unnest the elements inside an XML document. For example, consider the following XMLType
table dept_xml_tab
containing XML documents:
CREATE TABLE dept_xml_tab OF XMLType; Table created. INSERT INTO dept_xml_tab VALUES( XMLType('<Department deptno="100"> <DeptName>Sports</DeptName> <EmployeeList> <Employee empno="200"><Ename>John</Ename><Salary>33333</Salary> </Employee> <Employee empno="300"><Ename>Jack</Ename><Salary>333444</Salary> </Employee> </EmployeeList> </Department>')); 1 row created. INSERT INTO dept_xml_tab VALUES ( XMLType('<Department deptno="200"> <DeptName>Sports</DeptName> <EmployeeList> <Employee empno="400"><Ename>Marlin</Ename><Salary>20000</Salary> </Employee> </EmployeeList> </Department>')); 1 row created. COMMIT;
You can use SQL function XMLSequence
to unnest the Employee
list items as top-level SQL rows:
SELECT extractValue(OBJECT_VALUE, '/Department/@deptno') AS deptno, extractValue(value(em), '/Employee/@empno') AS empno, extractValue(value(em), '/Employee/Ename') AS ename FROM dept_xml_tab, table(XMLSequence(extract(OBJECT_VALUE, '/Department/EmployeeList/Employee'))) em;
This returns the following:
DEPTNO EMPNO ENAME --------------------------------- 100 200 John 100 300 Jack 200 400 Marlin 3 rows selected
For each row in table dept_xml_tab
, function table
is applied. Here, function extract
creates a new XMLType
instance that contains a fragment of all employee elements. This is fed to SQL function XMLSequence
, which creates a collection of all employees.
Function TABLE
then explodes the collection elements into multiple rows which are correlated with the parent table dept_xml_tab
. Thus you get a list of all the parent dept_xml_tab
rows with the associated employees.
Function extractValue
extracts out the scalar values for the department number, employee number, and name.
SQL function XMLConcat
concatenates all of its arguments to create an XML fragment. Figure 16-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 XMLTypes
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.
Example 16-13 XMLCONCAT: Concatenating XMLType Instances from a Sequence
This example uses function XMLConcat
to return a concatenation of XMLType
instances from an XMLSequenceType
value (a varray of XMLType
instances).
SELECT XMLConcat(XMLSequenceType( XMLType('<PartNo>1236</PartNo>'), XMLType('<PartName>Widget</PartName>'), XMLType('<PartPrice>29.99</PartPrice>'))).getClobVal() AS "RESULT" FROM DUAL;
This query returns a single XML fragment (the actual output is not pretty-printed):
RESULT --------------- <PartNo>1236</PartNo> <PartName>Widget</PartName> <PartPrice>29.99</PartPrice> 1 row selected.
Example 16-14 XMLCONCAT: Concatenating XML Elements
The following example creates an XML element for the first and the last names and then concatenates the result:
SELECT XMLConcat(XMLElement("first", e.first_name), XMLElement("last", e.last_name)) AS "RESULT" FROM employees e;
This query produces the following XML fragment:
RESULT -------------------------------------------- <first>Den</first><last>Raphaely</last> <first>Alexander</first><last>Khoo</last> <first>Shelli</first><last>Baida</last> <first>Sigal</first><last>Tobias</last> <first>Guy</first><last>Himuro</last> <first>Karen</first><last>Colmenares</last> 6 rows selected.
SQL function XMLAgg
is an aggregate function that produces a forest of XML elements from a collection of XML elements.
Figure 16-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 allows 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 16-15 XMLAGG: Generating Department Elements with a List of Employee Elements
This example produces a Department
element containing Employee
elements with employee job ID and last name as the contents of the elements. It also orders the employee XML elements in the department by their last name. (The actual result is not pretty-printed.)
SELECT XMLElement("Department", XMLAgg(XMLElement("Employee",
e.job_id||' '||e.last_name)
ORDER BY e.last_name))
AS "Dept_list"
FROM hr.employees e
WHERE e.department_id = 30 OR e.department_id = 40;
Dept_list
------------------
<Department>
<Employee>PU_CLERK Baida</Employee>
<Employee>PU_CLERK Colmenares</Employee>
<Employee>PU_CLERK Himuro</Employee>
<Employee>PU_CLERK Khoo</Employee>
<Employee>HR_REP Mavris</Employee>
<Employee>PU_MAN Raphaely</Employee>
<Employee>PU_CLERK Tobias</Employee>
</Department>
1 row selected.
The result is a single row, because XMLAgg
aggregates the rows. You can use the GROUP
BY
clause to group the returned set of rows into multiple groups. (The actual result of the following query is not pretty-printed.)
SELECT XMLElement("Department", XMLAttributes(department_id AS "deptno"), XMLAgg(XMLElement("Employee", e.job_id||' '||e.last_name))) AS "Dept_list" FROM hr.employees e GROUP BY e.department_id; Dept_list ------------------ <Department deptno="30"> <Employee>PU_MAN Raphaely</Employee> <Employee>PU_CLERK Khoo</Employee> <Employee>PU_CLERK Baida</Employee> <Employee>PU_CLERK Himuro</Employee> <Employee>PU_CLERK Colmenares</Employee> <Employee>PU_CLERK Tobias</Employee> </Department> <Department deptno="40"> <Employee>HR_REP Mavris</Employee> </Department> 2 rows selected.
You can order the employees within each department by using the ORDER BY
clause inside the XMLAgg
expression.
Note:
Within theorder_by_clause
, Oracle Database does not interpret number literals as column positions, as it does in other uses of this clause, but simply as number literals.Example 16-16 XMLAGG: Generating Nested Elements
Function XMLAgg
can be used to reflect the hierarchical nature of some relationships that exist in tables. This example generates a department element for department 30. Within this element is a child element for each employee of the department. Within each employee element is a dependent
element for each dependent of that employee.
First, this query shows the employees of department 30.
SELECT last_name, employee_id FROM employees WHERE department_id = 30; LAST_NAME EMPLOYEE_ID ------------------------- ----------- Raphaely 114 Khoo 115 Baida 116 Tobias 117 Himuro 118 Colmenares 119 6 rows selected.
A dependents
table is created, to hold the dependents of each employee.
CREATE TABLE hr.dependents (id NUMBER(4) PRIMARY KEY, employee_id NUMBER(4), name VARCHAR2(10)); Table created. INSERT INTO dependents VALUES (1, 114, 'MARK'); 1 row created. INSERT INTO dependents VALUES (2, 114, 'JACK'); 1 row created. INSERT INTO dependents VALUES (3, 115, 'JANE'); 1 row created. INSERT INTO dependents VALUES (4, 116, 'HELEN'); 1 row created. INSERT INTO dependents VALUES (5, 116, 'FRANK'); 1 row created. COMMIT; Commit complete.
This query generates the XML data for department that contains the information on dependents (the actual output is not pretty-printed):
SELECT XMLElement( "Department", XMLAttributes(d.department_name AS "name"), (SELECT XMLAgg(XMLElement("emp", XMLAttributes(e.last_name AS name), (SELECT XMLAgg(XMLElement("dependent", XMLAttributes(de.name AS "name"))) FROM dependents de WHERE de.employee_id = e.employee_id))) FROM employees e WHERE e.department_id = d.department_id)) AS "dept_list" FROM departments d WHERE department_id = 30; dept_list -------------------------------------------------------------------------------- <Department name="Purchasing"> <emp NAME="Raphaely"> <dependent name="MARK"></dependent> <dependent name="JACK"></dependent> </emp><emp NAME="Khoo"> <dependent name="JANE"></dependent> </emp> <emp NAME="Baida"> <dependent name="HELEN"></dependent> <dependent name="FRANK"></dependent> </emp><emp NAME="Tobias"></emp> <emp NAME="Himuro"></emp> <emp NAME="Colmenares"></emp> </Department> 1 row selected.
You use SQL function XMLPI
to generate XML processing instructions (PIs). Figure 16-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 betweeen "<?"
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
.
You use SQL function XMLComment
to generate XML comments. Figure 16-8 shows the 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
.
You use SQL function 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 16-9 shows the syntax of XMLRoot
:
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
.
You use SQL function XMLSerialize
to obtain a string or a LOB representation of XML data. Figure 16-10 shows the 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 datatype 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 well-formedness.
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
.
You use SQL function XMLParse
to parse a string containing XML data and generate a corresponding value of XMLType
. Figure 16-11 shows the 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 in fact well-formed.
Function XMLParse
returns an instance of XMLType
. If value_expr
evaluates to NULL
, then the function returns NULL
.
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 fragmentsSQL 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 16-12 shows the 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 16-22 XMLCOLATTVAL: Generating Elements with Attribute and Child Elements
This example generates an Emp
element for each employee, with a name
attribute and elements with the employee hire date and department as the content.
SELECT XMLElement("Emp", XMLAttributes(e.first_name ||' '||e.last_name AS "fullname" ), XMLColAttVal(e.hire_date, e.department_id AS "department")) AS "RESULT" FROM hr.employees e WHERE e.department_id = 30;
This query produces the following XML result (the actual result is not pretty-printed):
RESULT ----------------------------------------------------------- <Emp fullname="Den Raphaely"> <column name = "HIRE_DATE">1994-12-07</column> <column name = "department">30</column> </Emp> <Emp fullname="Alexander Khoo"> <column name = "HIRE_DATE">1995-05-18</column> <column name = "department">30</column> </Emp> <Emp fullname="Shelli Baida"> <column name = "HIRE_DATE">1997-12-24</column> <column name = "department">30</column> </Emp> <Emp fullname="Sigal Tobias"> <column name = "HIRE_DATE">1997-07-24</column> <column name = "department">30</column> </Emp> <Emp fullname="Guy Himuro"> <column name = "HIRE_DATE">1998-11-15</column> <column name = "department">30</column> </Emp> <Emp fullname="Karen Colmenares"> <column name = "HIRE_DATE">1999-08-10</column> <column name = "department">30</column> </Emp> 6 rows selected.
You use SQL function XMLCDATA
to generate an XML CDATA
section. Figure 16-13 shows the 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
.
SELECT XMLElement("PurchaseOrder", XMLElement("Address", XMLCDATA('100 Pennsylvania Ave.'), XMLElement("City", 'Washington, D.C.'))) AS RESULT FROM DUAL;
This results in the following output (the actual output is not pretty-printed):
RESULT -------------------------- <PurchaseOrder> <Address> <![CDATA[100 Pennsylvania Ave.]]> <City>Washington, D.C.</City> </Address> </PurchaseOrder>
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.
See Also:
"Generating XML with XSU's OracleXMLQuery", in Oracle XML Developer's Kit Programmer's Guide (compare the functionality ofOracleXMLQuery
with DBMS_XMLGEN
)Figure 16-14 illustrates how to use package DBMS_XMLGEN
. The steps are as follows:
Get the context from the package by supplying a SQL query and calling the newContext()
call.
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.
Get the XML result, using getXML()
or getXMLType()
. By setting the maximum number of rows to be retrieved for each fetch using 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 function getNumRowsProcessed(
).
You can reset the query to start again and repeat step 3.
Close the closeContext()
to free up any resource allocated inside.
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.
Table 16-1 describes the functions and procedures of package DBMS_XMLGEN
.
Table 16-1 DBMS_XMLGEN Functions and Procedures
Example 16-24 DBMS_XMLGEN: Generating Simple XML
This example creates an XML document by selecting employee data from an object-relational table and putting the resulting CLOB
value into a table.
CREATE TABLE temp_clob_tab(result CLOB); DECLARE qryCtx DBMS_XMLGEN.ctxHandle; result CLOB; BEGIN qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM hr.employees'); -- Set the row header to be EMPLOYEE DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE'); -- Get the result result := DBMS_XMLGEN.getXML(qryCtx); INSERT INTO temp_clob_tab VALUES(result); --Close context DBMS_XMLGEN.closeContext(qryCtx); END; /
This query example generates the following XML (only part of the result is shown):
SELECT * FROM temp_clob_tab WHERE ROWNUM = 1;
RESULT
-------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<EMPLOYEE>
<EMPLOYEE_ID>100</EMPLOYEE_ID>
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>King</LAST_NAME>
<EMAIL>SKING</EMAIL>
<PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
<HIRE_DATE>17-JUN-87</HIRE_DATE>
<JOB_ID>AD_PRES</JOB_ID>
<SALARY>24000</SALARY>
<DEPARTMENT_ID>90</DEPARTMENT_ID>
</EMPLOYEE>
...
1 row selected.
Example 16-25 DBMS_XMLGEN: Generating Simple XML with Pagination (fetch)
Instead of generating all the XML data for all rows, you can use the fetch
interface of DBMS_XMLGEN
to retrieve a fixed number of rows each time. This speeds up response time and can help in scaling applications that need a Document Object Model (DOM) Application Program Interface (API) on the resulting XML, particularly if the number of rows is large.
The following example uses package DBMS_XMLGEN
to retrieve results from table hr.employees
:
-- Create a table to hold the results CREATE TABLE temp_clob_tab(result clob); DECLARE qryCtx DBMS_XMLGEN.ctxHandle; result CLOB; BEGIN -- Get the query context; qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM hr.employees'); -- Set the maximum number of rows to be 2 DBMS_XMLGEN.setMaxRows(qryCtx, 2); LOOP -- Get the result result := DBMS_XMLGEN.getXML(qryCtx); -- If no rows were processed, then quit EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0; -- Do some processing with the lob data -- Here, we insert the results into a table. -- You can print the lob out, output it to a stream, -- put it in a queue, or do any other processing. INSERT INTO temp_clob_tab VALUES(result); END LOOP; --close context DBMS_XMLGEN.closeContext(qryCtx); END; / SELECT * FROM temp_clob_tab WHERE rownum <3; RESULT ---------------------------------------------------------- <?xml version="1.0"?> <ROWSET> <ROW> <EMPLOYEE_ID>100</EMPLOYEE_ID> <FIRST_NAME>Steven</FIRST_NAME> <LAST_NAME>King</LAST_NAME> <EMAIL>SKING</EMAIL> <PHONE_NUMBER>515.123.4567</PHONE_NUMBER> <HIRE_DATE>17-JUN-87</HIRE_DATE> <JOB_ID>AD_PRES</JOB_ID> <SALARY>24000</SALARY> <DEPARTMENT_ID>90</DEPARTMENT_ID> </ROW> <ROW> <EMPLOYEE_ID>101</EMPLOYEE_ID> <FIRST_NAME>Neena</FIRST_NAME> <LAST_NAME>Kochhar</LAST_NAME> <EMAIL>NKOCHHAR</EMAIL> <PHONE_NUMBER>515.123.4568</PHONE_NUMBER> <HIRE_DATE>21-SEP-89</HIRE_DATE> <JOB_ID>AD_VP</JOB_ID> <SALARY>17000</SALARY> <MANAGER_ID>100</MANAGER_ID> <DEPARTMENT_ID>90</DEPARTMENT_ID> </ROW> </ROWSET> <?xml version="1.0"?> <ROWSET> <ROW> <EMPLOYEE_ID>102</EMPLOYEE_ID> <FIRST_NAME>Lex</FIRST_NAME> <LAST_NAME>De Haan</LAST_NAME> <EMAIL>LDEHAAN</EMAIL> <PHONE_NUMBER>515.123.4569</PHONE_NUMBER> <HIRE_DATE>13-JAN-93</HIRE_DATE> <JOB_ID>AD_VP</JOB_ID> <SALARY>17000</SALARY> <MANAGER_ID>100</MANAGER_ID> <DEPARTMENT_ID>90</DEPARTMENT_ID> </ROW> <ROW> <EMPLOYEE_ID>103</EMPLOYEE_ID> <FIRST_NAME>Alexander</FIRST_NAME> <LAST_NAME>Hunold</LAST_NAME> <EMAIL>AHUNOLD</EMAIL> <PHONE_NUMBER>590.423.4567</PHONE_NUMBER> <HIRE_DATE>03-JAN-90</HIRE_DATE> <JOB_ID>IT_PROG</JOB_ID> <SALARY>9000</SALARY> <MANAGER_ID>102</MANAGER_ID> <DEPARTMENT_ID>60</DEPARTMENT_ID> </ROW> </ROWSET> 2 rows selected.
Example 16-26 DBMS_XMLGEN: Generating Nested XML With Object Types
This example uses object types to represent nested structures.
CREATE TABLE new_departments(department_id NUMBER PRIMARY KEY, department_name VARCHAR2(20)); CREATE TABLE new_employees(employee_id NUMBER PRIMARY KEY, last_name VARCHAR2(20), department_id NUMBER REFERENCES new_departments); CREATE TYPE emp_t AS OBJECT("@employee_id" NUMBER, last_name VARCHAR2(20)); / INSERT INTO new_departments VALUES(10, 'SALES'); INSERT INTO new_departments VALUES(20, 'ACCOUNTING'); INSERT INTO new_employees VALUES(30, 'Scott', 10); INSERT INTO new_employees VALUES(31, 'Mary', 10); INSERT INTO new_employees VALUES(40, 'John', 20); INSERT INTO new_employees VALUES(41, 'Jerry', 20); COMMIT; CREATE TYPE emplist_t AS TABLE OF emp_t; / CREATE TYPE dept_t AS OBJECT("@department_id" NUMBER, department_name VARCHAR2(20), emplist emplist_t); / CREATE TABLE temp_clob_tab(result CLOB); DECLARE qryCtx DBMS_XMLGEN.ctxHandle; result CLOB; BEGIN DBMS_XMLGEN.setRowTag(qryCtx, NULL); qryCtx := DBMS_XMLGEN.newContext ('SELECT dept_t(department_id, department_name, CAST(MULTISET (SELECT e.employee_id, e.last_name FROM new_employees e WHERE e.department_id = d.department_id) AS emplist_t)) AS deptxml FROM new_departments d'); -- now get the result result := DBMS_XMLGEN.getXML(qryCtx); INSERT INTO temp_clob_tab VALUES (result); -- close context DBMS_XMLGEN.closeContext(qryCtx); END; / SELECT * FROM temp_clob_tab;
Here is the resulting XML:
RESULT -------------------------------------------- <?xml version="1.0"?> <ROWSET> <ROW> <DEPTXML department_id="10"> <DEPARTMENT_NAME>SALES</DEPARTMENT_NAME> <EMPLIST> <EMP_T employee_id="30"> <LAST_NAME>Scott</LAST_NAME> </EMP_T> <EMP_T employee_id="31"> <LAST_NAME>Mary</LAST_NAME> </EMP_T> </EMPLIST> </DEPTXML> </ROW> <ROW> <DEPTXML department_id="20"> <DEPARTMENT_NAME>ACCOUNTING</DEPARTMENT_NAME> <EMPLIST> <EMP_T employee_id="40"> <LAST_NAME>John</LAST_NAME> </EMP_T> <EMP_T employee_id="41"> <LAST_NAME>Jerry</LAST_NAME> </EMP_T> </EMPLIST> </DEPTXML> </ROW> </ROWSET> 1 row selected.
With relational data, the result is an XML document without nested elements. To obtain nested XML structures, you can use object-relational data, where the mapping is as follows:
Object types map as an XML element – see Chapter 5, "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 16-27 DBMS_XMLGEN: Generating Nested XML With User-Defined Datatype Instances
When you provide a user-defined datatype instance to DBMS_XMLGEN
functions, the user-defined datatype instance is mapped to an XML document using canonical mapping: the attributes of the user-defined datatype are mapped to XML elements. Attributes with names starting with an at-sign (@
) are mapped to attributes of the preceding element.
User-defined datatype instances can be used for nesting in the resulting XML document. For example, consider tables, emp
and dept
:
CREATE TABLE dept(deptno NUMBER PRIMARY KEY, dname VARCHAR2(20)); CREATE TABLE emp(empno NUMBER PRIMARY KEY, ename VARCHAR2(20), deptno NUMBER REFERENCES dept);
To generate a hierarchical view of the data, that is, departments with employees in them, you can define suitable object types to create the structure inside the database as follows:
-- empno is preceded by an at-sign (@) to indicate that it must -- be mapped as an attribute of the enclosing Employee element. CREATE TYPE emp_t AS OBJECT("@empno" NUMBER, -- empno defined as attribute ename VARCHAR2(20)); / INSERT INTO DEPT VALUES(10, 'Sports'); INSERT INTO DEPT VALUES(20, 'Accounting'); INSERT INTO EMP VALUES(200, 'John', 10); INSERT INTO EMP VALUES(300, 'Jack', 10); INSERT INTO EMP VALUES(400, 'Mary', 20); INSERT INTO EMP VALUES(500, 'Jerry', 20); COMMIT; CREATE TYPE emplist_t AS TABLE OF emp_t; / CREATE TYPE dept_t AS OBJECT("@deptno" NUMBER, dname VARCHAR2(20), emplist emplist_t); / -- Department type dept_t contains a list of employees. -- We can now query the employee and department tables and get -- the result as an XML document, as follows: CREATE TABLE temp_clob_tab(result CLOB); DECLARE qryCtx DBMS_XMLGEN.ctxHandle; RESULT CLOB; BEGIN -- get query context qryCtx := DBMS_XMLGEN.newContext( 'SELECT dept_t(deptno, dname, CAST(MULTISET(SELECT empno, ename FROM emp e WHERE e.deptno = d.deptno) AS emplist_t)) AS deptxml FROM dept d'); -- set maximum number of rows to 5 DBMS_XMLGEN.setMaxRows(qryCtx, 5); -- set no row tag for this result, since there is a single ADT column DBMS_XMLGEN.setRowTag(qryCtx, NULL); LOOP -- get result result := DBMS_XMLGEN.getXML(qryCtx); -- if there were no rows processed, then quit EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0; -- do something with the result INSERT INTO temp_clob_tab VALUES (result); END LOOP; END; /
Function MULTISET
treats the employees working in the department as a list, and function CAST
assigns this list to the appropriate collection type. A department instance is created, and DBMS_XMLGEN
routines create the XML for the object instance.
SELECT * FROM temp_clob_tab; RESULT --------------------------------- <?xml version="1.0"?> <ROWSET> <DEPTXML deptno="10"> <DNAME>Sports</DNAME> <EMPLIST> <EMP_T empno="200"> <ENAME>John</ENAME> </EMP_T> <EMP_T empno="300"> <ENAME>Jack</ENAME> </EMP_T> </EMPLIST> </DEPTXML> <DEPTXML deptno="20"> <DNAME>Accounting</DNAME> <EMPLIST> <EMP_T empno="400"> <ENAME>Mary</ENAME> </EMP_T> <EMP_T empno="500"> <ENAME>Jerry</ENAME> </EMP_T> </EMPLIST> </DEPTXML> </ROWSET> 1 row selected.
The default name ROW
is not present because we set that to NULL
. The deptno
and empno
have become attributes of the enclosing element.
Example 16-28 DBMS_XMLGEN: Generating an XML Purchase Order
This example uses DBMS_XMLGEN.getXMLType()
to generate a purchase order in XML format using object views.
-- Create relational schema and define object views -- DBMS_XMLGEN maps user-defined datatype attribute names that start -- with an at-sign (@) to XML attributes -- Purchase Order Object View Model -- PhoneList varray object type CREATE TYPE phonelist_vartyp AS VARRAY(10) OF VARCHAR2(20) / -- Address object type CREATE TYPE address_typ AS OBJECT(Street VARCHAR2(200), City VARCHAR2(200), State CHAR(2), Zip VARCHAR2(20)) / -- Customer object type CREATE TYPE customer_typ AS OBJECT(CustNo NUMBER, CustName VARCHAR2(200), Address address_typ, PhoneList phonelist_vartyp) / -- StockItem object type CREATE TYPE stockitem_typ AS OBJECT("@StockNo" NUMBER, Price NUMBER, TaxRate NUMBER) / -- LineItems object type CREATE TYPE lineitem_typ AS OBJECT("@LineItemNo" NUMBER, Item stockitem_typ, Quantity NUMBER, Discount NUMBER) / -- LineItems nested table CREATE TYPE lineitems_ntabtyp AS TABLE OF lineitem_typ / -- Purchase Order object type CREATE TYPE po_typ AUTHID CURRENT_USER AS OBJECT(PONO NUMBER, Cust_ref REF customer_typ, OrderDate DATE, ShipDate TIMESTAMP, LineItems_ntab lineitems_ntabtyp, ShipToAddr address_typ) / -- Create Purchase Order relational model tables -- Customer table CREATE TABLE customer_tab(CustNo NUMBER NOT NULL, CustName VARCHAR2(200), Street VARCHAR2(200), City VARCHAR2(200), State CHAR(2), Zip VARCHAR2(20), Phone1 VARCHAR2(20), Phone2 VARCHAR2(20), Phone3 VARCHAR2(20), CONSTRAINT cust_pk PRIMARY KEY (CustNo)) ORGANIZATION INDEX OVERFLOW; -- Purchase Order table CREATE TABLE po_tab (PONo NUMBER, /* purchase order number */ Custno NUMBER /* foreign KEY referencing customer */ CONSTRAINT po_cust_fk REFERENCES customer_tab, OrderDate DATE, /* date of order */ ShipDate TIMESTAMP, /* date to be shipped */ ToStreet VARCHAR2(200), /* shipto address */ ToCity VARCHAR2(200), ToState CHAR(2), ToZip VARCHAR2(20), CONSTRAINT po_pk PRIMARY KEY(PONo)); --Stock Table CREATE TABLE stock_tab (StockNo NUMBER CONSTRAINT stock_uk UNIQUE, Price NUMBER, TaxRate NUMBER); --Line Items table CREATE TABLE lineitems_tab(LineItemNo NUMBER, PONo NUMBER CONSTRAINT li_po_fk REFERENCES po_tab, StockNo NUMBER, Quantity NUMBER, Discount NUMBER, CONSTRAINT li_pk PRIMARY KEY (PONo, LineItemNo)); -- Create Object views -- Customer Object View CREATE OR REPLACE VIEW customer OF customer_typ WITH OBJECT IDENTIFIER(CustNo) AS SELECT c.custno, c.custname, address_typ(c.street, c.city, c.state, c.zip), phonelist_vartyp(phone1, phone2, phone3) FROM customer_tab c; --Purchase order view CREATE OR REPLACE VIEW po OF po_typ WITH OBJECT IDENTIFIER (PONo) AS SELECT p.pono, MAKE_REF(Customer, P.Custno), p.orderdate, p.shipdate, CAST(MULTISET( SELECT lineitem_typ(l.lineitemno, stockitem_typ(l.stockno, s.price, s.taxrate), l.quantity, l.discount) FROM lineitems_tab l, stock_tab s WHERE l.pono = p.pono AND s.stockno=l.stockno) AS lineitems_ntabtyp), address_typ(p.tostreet,p.tocity, p.tostate, p.tozip) FROM po_tab p; -- Create table with XMLType column to store purchase order in XML format CREATE TABLE po_xml_tab(poid NUMBER, podoc XMLType) / -- 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 16-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 User's Guide and Reference for more information on cursor variables (REF CURSOR
)Example 16-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, 'Marry', 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', 'Marry'); 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>Marry</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 16-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 16-32 DBMS_XMLGEN : Binding Query Variables with setBindValue()
If the query string used to create a context contains host variables, you can use 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.
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 a XML document for each row.
Example 16-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.
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 16-15.
Expression expr
evaluates to a particular row and column of the database. It can be a scalar value, a user-defined datatype instance, or an XMLType
instance.
If expr
evaluates to a scalar value, then the function returns an XML element containing the scalar value.
If expr
evaluates to a user-defined datatype instance, then the function maps the user-defined datatype attributes to XML elements.
If expr
evaluates to an XMLType
instance, then the function encloses the document in an XML element whose default tag name is ROW
.
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 16-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:
You can create and query XML instances within SQL queries.
Using the object-relational infrastructure, you can create complex and nested XML instances from simple relational tables. For example, when you use an XMLType
view that uses sys_XMLGen
on top of an object type, Oracle XML DB rewrites these queries when possible. See also Chapter 6, "XPath Rewrite".
sys_XMLGen
creates an XML document from a user-defined datatype 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.
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 16-2 lists the attributes of object XMLFormat
.
Table 16-2 Attributes of the XMLFormat Object
Attribute | Datatype | Purpose |
---|---|---|
|
|
The name of the enclosing tag for the result of the |
|
|
The type of schema generation for the output document. Valid values are ' |
|
|
The name of the target schema used if |
|
|
The target namespace if the schema is specified (that is, |
|
|
The URL to the database to be used if |
|
|
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 16-37 for an example of using createFormat()
to name the root element that is output by sys_XMLGen
Oracle Database SQL Reference for more information on sys_XMLGen
and the XMLFormat
object
Example 16-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 16-36 SYS_XMLGEN: Default Element Name ROW
In Example 16-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 simply 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 16-37 for an example.
Example 16-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 16-38 SYS_XMLGEN: Converting a User-Defined Datatype Instance to XML
When you provide a user-defined datatype instance as an argument to sys_XMLGen
, the instance is canonically mapped to an XML document. In this mapping, the user-defined datatype attributes are mapped to XML elements.
Any datatype attributes with names that start with an at sign (@
) are mapped to attributes of the preceding XML element. User-defined datatype 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 functionsys_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 16-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 tosys_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 16-40 Using SYS_XMLGEN with Object Views
For any undefined entities here, refer to the code in Example 16-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.
SQL function sys_XMLAgg
aggregates all XML documents or fragments represented by expr and produces a single XML document. It adds a new enclosing element with a default name, ROWSET
. To format the XML document differently, use the fmt
parameter.
See Also:
Oracle Database SQL ReferenceOracle9i 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 on element<xsql:include-xml>
and XSQL pagesExample 16-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 16-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 (the actual result is not pretty-printed):
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'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 retransforming the same (or static) data over and over.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.
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 on XSUThis section describes additional guidelines for generating XML using Oracle XML DB.
To use the XMLAgg ORDER BY
clause before aggregation, specify the ORDER BY
clause following the first XMLAGG
argument.
Example 16-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 actual result is not pretty-printed):
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.
To use SQL function XMLSequence
with extract
to return a rowset with relevant portions of a document extracted as multiple rows, use XMLSequence
as an argument to function TABLE
, as shown in Example 16-44.
Example 16-44 Returning a Rowset using XMLSEQUENCE, EXTRACT, and TABLE
This example uses the purchaseorder
table in sample schema OE
.
CONNECT OE/OE; Connected. SELECT extractValue(value(item), '/*/Description') AS descr, extractValue(value(item), '/*/Part/@Id') AS partid FROM purchaseorder p, table(XMLSequence(extract(value(p), '/PurchaseOrder/LineItems/LineItem'))) item WHERE extractValue(value(item), '/*/Part/@Id') = '715515012027' OR extractValue(value(item), '/*/Part/@Id') = '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 argumentdata-type
to be present, but it is optional in the Oracle XML DB implementation of the standard, for ease of use.