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