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