XMLSEQUENCE SQL Function

XMLSEQUENCE SQL Function

XML SEQUENCE SQL Function

How to use and what is XMLSEQUENCE SQL Function?
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;