SYS_XMLAGG

Syntax

sys_xmlagg::=

Text description of sys_xmlagg

Purpose

The SYS_XMLAGG function aggregates all of the XML documents or fragments represented by expr and produces a single XML document. It adds a new enclosing element with a default name ROWSET. If you want to format the XML document differently, specify fmt, which is an instance of the SYS.XMLGenFormatType object.

See Also: "XML Format Model" for using the attributes of the XMLGenFormatType type to format SYS_XMLGEN results SYS_XMLGEN Oracle9i XML Reference and Oracle9i Application Developer's Guide - XML for information on XML types and their use

Example

The following example uses the SYS_XMLGEN function to generate an XML document for each row of the sample table employees where the employee's last name begins with the letter R, and then aggregates all of the rows into a single XML document in the default enclosing element ROWSET:

SELECT SYS_XMLAGG(SYS_XMLGEN(last_name)).getClobVal()
   FROM employees
   WHERE last_name LIKE 'R%';

SYS_XMLAGG(SYS_XMLGEN(LAST_NAME)).GETCLOBVAL()
---------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<LAST_NAME>Rajs</LAST_NAME>
<LAST_NAME>Raphaely</LAST_NAME>
<LAST_NAME>Rogers</LAST_NAME>
<LAST_NAME>Russell</LAST_NAME>
</ROWSET>