XMLSEQUENCE SQL Function in BI Publisher

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.
Example 1:
SELECT value(T).getstringval() Attribute_Value
  FROM table(XMLSequence(extract(XMLType('V1V2V3'),
                                 '/A/B'))) T;

Example 2:
CREATE TABLE emp_xml_tab OF XMLType;

Table created.
INSERT INTO emp_xml_tab VALUES( XMLType('
                                           112Joe50000217
                                             Jane
                                             60000412Jack40000'));
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.


Example 3:
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;


Example 4:
XMLSEQUENCE: Unnesting Collections in XML Documents into SQL Rows

CREATE TABLE dept_xml_tab OF XMLType;
Table created.
INSERT INTO dept_xml_tab
  VALUES(
    XMLType('Sports
               John33333
               Jack333444')

);

1 row created.

INSERT INTO dept_xml_tab
  VALUES (
    XMLType('Sports
               Marlin20000')

);
1 row created.
COMMIT;

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;