XML SEQUENCE SQL Function

What is XMLSEQUENCE SQL Function and How to use?

SQL function XML Sequence returns an XMLSequenceType value (a varray of XML Type 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;

Then insert data into table
INSERT INTO emp_xml_tab

        VALUES (

                  XMLType ('112Joe50000217

                             Jane

                            60000412Jack40000'));

COMMIT;

/

To create a new XML document containing only employees who earn $42,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') >= 42000;

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.

·         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: Unseating Collections in XML Documents into SQL Rows

CREATE TABLE dept_xml_tab OF XMLTYPE;

INSERT INTO dept_xml_tab

     VALUES (XMLType ('Sports

               John33333

               Jack333444'));

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;