XMLSEQUENCE Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

 

the XMLSEQUENCE function is shown below,

 

 

The below listing passes an XML document containing a video game’s information to the XMLSEQUENCE function, which produces a VARRAY containing the XML document’s content as shown below,

 

 

Result:

SYS.XMLSEQUENCETYPE(<Videogame>

<Type>Racing</Type>

<Name>NFS Most Wanted</Name>

<Version>2.0</Version>

<Size>5.5 GB</Size>

</Videogame>)

 

In the below example, the XMLSEQUENCE function’s return type VARRAY is passed into a TABLE function which converts its nodes into multiple rows as shown below,

 

ROWNUM	COLUMN_VALUE
1	<Type>Racing</Type>
2	<Name>NFS Most Wanted</Name>
3	<Version>2.0</Version>
4	<Size>5.5 GB</Size>

 

XMLSEQUENCE

XMLSEQUENCEreturns a collection of XMLTYPEs where each row contains a top element node of the XML content.

SELECT
   EXTRACTVALUE(T.COLUMN_VALUE,'/CANTON/NAME') CANTON
FROM
   WORLD,
   TABLE
   (
      XMLSEQUENCE
      (
         EXTRACT
         (
            WORLD.OBJECT_VALUE,
            '/COUNTRY/CANTON_LIST/CANTON'
         )
      )
   ) T
WHERE
   EXISTSNODE
   (
      T.COLUMN_VALUE,
      '/CANTON/DETAILS//LANGUAGE="French"'
   )=1;

CANTON
---------
Bern
Fribourg
Vaud
Valais
Neuchatel
Geneva
Jura

The cantons are extracted out of the XMLTYPE document. The XMLSEQUENCE function transforms the XMLTYPE content in a collection; TABLE unnests the collection. The column of the table WORLD is referenced in the collection function XMLSEQUENCE.

The NAME values are returned by EXTRACTVALUEand EXISTSNODErestrict the rows to the French speaking cantons.

XMLSEQUENCE also accepts a REF CURSORas input to transform a defined cursor into a collection of XMLTYPE.

SELECT
   ROWNUM,
   COLUMN_VALUE
FROM
   TABLE(XMLSEQUENCE(CURSOR(SELECT * FROM DEPT)));

ROWNUM COLUMN_VALUE
------ ------------------------------
     1  <ROW>
           <DEPTNO>10</DEPTNO>
           <DNAME>ACCOUNTING</DNAME>
           <LOC>NEW YORK</LOC>
        </ROW>

     2  <ROW>
           <DEPTNO>20</DEPTNO>
           <DNAME>RESEARCH</DNAME>
           <LOC>DALLAS</LOC>
        </ROW>

     3  <ROW>
           <DEPTNO>30</DEPTNO>
           <DNAME>SALES</DNAME>
           <LOC>CHICAGO</LOC>
        </ROW>
 
     4  <ROW>
           <DEPTNO>40</DEPTNO>
           <DNAME>OPERATIONS</DNAME>
           <LOC>BOSTON</LOC>
        </ROW>

For the defined cursor, a collection of XMLTYPEs is returned by XMLSEQUENCE, unnested and displayed.

XQuery

In Oracle 10gR2 and later, XQuery extends the XML capabilities. XQuery is defined by the World Wide Web Consortium at http://www.w3.org/TR/xquery.

The function XMLQUERY processes an XQuery program and returns a well-formed content.

SELECT
   XMLQUERY
   (
      'let $i := "oracle"
      return <NAME>{$i}</NAME>'
      RETURNING CONTENT   ) NAME

FROM
   DUAL;
 
NAME
--------------------
<NAME>oracle</NAME>

The value oracle is assigned to the variable $i and returned as XML content.

XMLTABLE processes an XQuery program and returns rows.

SELECT
   XMLELEMENT(N,COLUMN_VALUE) N
FROM
   XMLTABLE
   (
      '1 to 10'
   );

N
----------
<N>1</N>
<N>2</N>
<N>3</N>
<N>4</N>
<N>5</N>
<N>6</N>
<N>7</N>
<N>8</N>
<N>9</N>
<N>10</N>

The very short XQuery program returns a sequence of numbers from 1 to 10. XMLTABLE returns a row for each value.

XQUERY programs can generate rows like XMLSEQUENCE.

SELECT
   NAME
FROM
   WORLD,
   XMLTABLE
   ('
      for $CANTON in $COUNTRY/COUNTRY/CANTON_LIST/CANTON
      where $CANTON//LANGUAGE="Italian"
      order by $CANTON/@ID
      return $CANTON
   '
   PASSING
      OBJECT_VALUE
   AS
      COUNTRY
   COLUMNS
      NAME XMLTYPE PATH '/CANTON/NAME'
   ); 

NAME
---------------------------
<NAME>Graubuenden</NAME>
<NAME>Ticino</NAME>

XMLTABLE processes the COUNTRY and returns a row for each canton where the language Italian exists. The rows are sorted by ID and the column NAME is returned for the name of the canton.

XMLEXISTS(11gR1) is a Boolean function similar to EXISTSNODEwith the XQuery syntax.

SELECT
   XMLFOREST(EMPNO, ENAME, JOB)
FROM
   EMP
WHERE
   XMLEXISTS
   (
      '/[JOB="ANALYST"]'
      PASSING
      XMLFOREST(EMPNO, ENAME, JOB)
   ); 

XMLFOREST(EMPNO,ENAME,JOB)
----------------------------------------
<EMPNO>7788</EMPNO>
<ENAME>SCOTT</ENAME>
<JOB>ANALYST</JOB> 

<EMPNO>7902</EMPNO>
<ENAME>FORD</ENAME>
<JOB>ANALYST</JOB>

The XPATHexpression searches for analysts in the generated content.

Besides selecting literals and column values, XQuery has multiple functions. The function ora:view is an Oracle-specific addition to the XQuery language that queries a relational table or view:

SELECT
   *
FROM
   XMLTABLE
   (
      '
         for $i in ora:view("EMP"), $j in ora:view("DEPT")
         where $i//JOB="ANALYST" and $i//DEPTNO=$j//DEPTNO
         return (<EMP>{$i//EMPNO}{$i//ENAME}{$j//DNAME}</EMP>)
      '
      COLUMNS
         EMP XMLTYPE PATH '/EMP'
   );

EMP
-------------------------------------------------------------------------
<EMP><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><DNAME>RESEARCH</DNAME></EMP>
<EMP><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><DNAME>RESEARCH</DNAME></EMP>

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    59 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    59 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    39 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

The XML Query selects directly from the EMP and DEPT tables. Note the execution plan: Oracle is doing a hash joinof EMP and DEPT, which means the XML Query is analyzed before execution and the optimizer chooses the best possible execution plan. The columns clause defines the name and datatype of the columns that are returned. Without the columns clause, only one column named COLUMN_NAME containing an XMLTYPE is returned. The path string identifies the location of the column within the XML hierarchy.

XML Query can now be an expression.

CREATE TABLE
   T
AS
SELECT
   TABLE_NAME
FROM
   USER_TABLES;
SELECT
   TABLE_NAME,
   C
FROM
   T,
   XMLTABLE
   (
      (
         SELECT
            '
               let $j := ora:view("'||T.TABLE_NAME||'")
               return <c>{count($j)}</c>
            '
         FROM
            DUAL
      )
      COLUMNS C NUMBER PATH '/C'
   );

TABLE_NAME                              C
------------------------------ ----------
SALGRADE                                5
BONUS                                   0
EMP                                    14
DEPT                                    4

The query is built dynamically. The table name here is selected from table T, so it could be any expression.