Example: Number result rows for XMLTABLE

In some cases, you may want to generate a column that numbers the rows that XMLTABLE produces for any given document. This can help your application to remember the order in which the values appeared in each document.

To number the result rows, use the FOR ORDINALITY clause. Note that the numbering starts with 1 for each document that is input to the XMLTABLE function.

SELECT X.*
  FROM emp,
       XMLTABLE ('$d/dept/employee' PASSING doc AS "d"
                 COLUMNS
                      seqno     FOR ORDINALITY,
                      empID     INTEGER     PATH '@id',
                      firstname VARCHAR(20) PATH 'name/first',
                      lastname  VARCHAR(25) PATH 'name/last') AS X

The result of the query is:

SEQNO    EMPID       FIRSTNAME            LASTNAME
-------- ----------- -------------------- -------------------------
       1         901 John                 Doe
       2         902 Peter                Pan
       1         903 Mary                 Jones