XMLSEQUENCE

Related articles.

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;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.