XMLTABLE example: Specifying an ordinality column in the result table

You can specify that the result table of an XMLTABLE invocation includes an ordinality column.

An ordinality column has the following properties:

Has the BIGINT data type
Starts at one for each document that generates a result table row
Is incremented by one for each result table row that is generated by a single document

For example, the following XML document is stored in the sample CUSTOMER table.

<customerinfo xmlns="http:⁄⁄posample.org" Cid="1003">
  <name>Robert Shoemaker<⁄name>
    <addr country="Canada">
    <street>1596 Baseline<⁄street>
    <city>Aurora<⁄city>
    <prov-state>Ontario<⁄prov-state>
    <pcode-zip>N8X 7F8<⁄pcode-zip>
  <⁄addr>
  <phone type="work">905-555-7258<⁄phone>
  <phone type="home">416-555-2937<⁄phone>
  <phone type="cell">905-555-8743<⁄phone>
  <phone type="cottage">613-555-3278<⁄phone>
<⁄customerinfo>

You can use a query like this to create an ordinality column in the XMLTABLE result table:

SELECT X.*
  FROM CUSTOMER C, XMLTABLE (XMLNAMESPACES(DEFAULT 'http://posample.org'),
  '$cust/customerinfo/phone' PASSING C.INFO as "cust"
  COLUMNS "SEQNO" FOR ORDINALITY,
  "PHONE TYPE" VARCHAR(15) PATH './@type',
  "PHONE NUMBER" VARCHAR(15) PATH '.'
  ) AS X
  WHERE CID=1003

The result table of the SELECT statement is:

Table 1. Result table from a query in which XMLTABLE has an ordinality column
SEQNO	PHONE TYPE	PHONE NUMBER
1	work	905-555-7258
2	home	416-555-2937
3	cell	905-555-8743
4	cottage	613-555-3278