XMLSEQUENCE

The XMLSEQUENCE operator is used to split multi-value results from XMLTYPE queries into multiple rows. This article presents a simple example of its use.

First we create a table to hold our XML document and populate it with a document containing multiple rows of data:

CREATE TABLE xml_tab OF XMLTYPE;

DECLARE
  l_xmltype XMLTYPE;
BEGIN
  SELECT sys_xmlagg(
           xmlelement(
             "EMP",
             xmlforest(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm)
           )
         )
  INTO   l_xmltype
  FROM   emp e;

  INSERT INTO xml_tab VALUES (l_xmltype);
  COMMIT;
END;
/

We can see the resulting row using the following query:

SET LONG 5000
SELECT x.getClobVal()
FROM   xml_tab x;

If we query this without the XMLSEQUENCE operator the results are returned as a single row with each column containing multiple values:

COLUMN empno DEFAULT
COLUMN ename DEFAULT
COLUMN job DEFAULT
COLUMN mgr DEFAULT
COLUMN hiredate DEFAULT
COLUMN sal DEFAULT
COLUMN deptno DEFAULT

SELECT extract(value(x), '//EMPNO/text()').getStringVal() AS empno,
       extract(value(x), '//ENAME/text()').getStringVal() AS ename,
       extract(value(x), '//JOB/text()').getStringVal() AS job,
       extract(value(x), '//MGR/text()').getStringVal() AS mgr,
       extract(value(x), '//HIREDATE/text()').getStringVal() AS hiredate,
       extract(value(x), '//SAL/text()').getStringVal() AS sal
FROM   xml_tab x;

The XMLSEQUENCE allows us to split the results into separate rows:

COLUMN empno FORMAT 9999
COLUMN ename FORMAT A10
COLUMN job FORMAT A9
COLUMN mgr FORMAT 9999
COLUMN hiredate FORMAT A20
COLUMN sal FORMAT 99999.00
COLUMN deptno FORMAT 99

SELECT extract(value(d), '//EMPNO/text()').getNumberVal() AS empno,
       extract(value(d), '//ENAME/text()').getStringVal() AS ename,
       extract(value(d), '//JOB/text()').getStringVal() AS job,
       extract(value(d), '//MGR/text()').getNumberVal() AS mgr,
       extract(value(d), '//HIREDATE/text()').getStringVal() AS hiredate,
       extract(value(d), '//SAL/text()').getNumberVal() AS sal
FROM   xml_tab x,
       table(xmlsequence(extract(value(x), '/ROWSET/EMP'))) d;

With the employees split into rows we can now include predicates to restrict the rows returned:

COLUMN empno FORMAT 9999
COLUMN ename FORMAT A10
COLUMN job FORMAT A9
COLUMN mgr FORMAT 9999
COLUMN hiredate FORMAT A20
COLUMN sal FORMAT 99999.00
COLUMN deptno FORMAT 99

SELECT extract(value(d), '//EMPNO/text()').getNumberVal() AS empno,
       extract(value(d), '//ENAME/text()').getStringVal() AS ename,
       extract(value(d), '//JOB/text()').getStringVal() AS job,
       extract(value(d), '//MGR/text()').getNumberVal() AS mgr,
       extract(value(d), '//HIREDATE/text()').getStringVal() AS hiredate,
       extract(value(d), '//SAL/text()').getNumberVal() AS sal
FROM   xml_tab x,
       table(xmlsequence(extract(value(x), '/ROWSET/EMP'))) d
WHERE  extract(value(d), '//SAL/text()').getNumberVal() > 2000;

If you wish to use a regular table containing an XMLTYPE rather than an object table the query is similar. First we recreate and populate the new table structure:

DROP TABLE xml_tab;
CREATE TABLE xml_tab (
  id      NUMBER(10),
  xmlval  XMLTYPE
)
/

DECLARE
  l_xmltype XMLTYPE;
BEGIN
  SELECT sys_xmlagg(
           xmlelement(
             "EMP",
             xmlforest(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm)
           )
         )
  INTO   l_xmltype
  FROM   emp e;

  INSERT INTO xml_tab (id, xmlval) VALUES (1, l_xmltype);
  COMMIT;
END;
/

Then we query the table by replacing the value(x) in the table function with a direct reference to the column x.xmlval:

COLUMN empno FORMAT 9999
COLUMN ename FORMAT A10
COLUMN job FORMAT A9
COLUMN mgr FORMAT 9999
COLUMN hiredate FORMAT A20
COLUMN sal FORMAT 99999.00
COLUMN deptno FORMAT 99

SELECT extract(value(d), '//EMPNO/text()').getNumberVal() AS empno,
       extract(value(d), '//ENAME/text()').getStringVal() AS ename,
       extract(value(d), '//JOB/text()').getStringVal() AS job,
       extract(value(d), '//MGR/text()').getNumberVal() AS mgr,
       extract(value(d), '//HIREDATE/text()').getStringVal() AS hiredate,
       extract(value(d), '//SAL/text()').getNumberVal() AS sal
FROM   xml_tab x,
       table(xmlsequence(extract(x.xmlval, '/ROWSET/EMP'))) d;

Hope this helps. Regards Tim...

Back to the Top.