Example: Returning one row for each occurrence of an item using XMLTABLE

For example, if the following two XML documents were stored in a table named CUSTOMER:

<customerinfo Cid="1001">
  <name>Kathy Smith</name>
  <addr country="Canada">
    <street>25 EastCreek</street>
    <city>Markham</city>
    <prov-state>Ontario</prov-state>
    <pcode-zip>N9C 3T6</pcode-zip>
  </addr>
  <phone type="work">905-555-7258</phone>
</customerinfo>

<customerinfo 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>

to create a table where every <phone> value is stored in a separate row, use XMLTABLE as follows:

SELECT X.*
FROM CUSTOMER C, XMLTABLE ('$cust/customerinfo/phone' PASSING C.INFO as "cust"
                           COLUMNS "CUSTNAME" CHAR(30) PATH '../name',
                           "PHONETYPE" CHAR(30) PATH '@type',
                           "PHONENUM" CHAR(15) PATH '.'
                          ) as X

This query yields the following result for the two XML documents: Notice how each <phone> element for the XML document with the name "Robert Shoemaker" are returned in a separate row.

For the same documents, you can also extract the <phone> elements as XML, as follows:

SELECT X.*
FROM CUSTOMER C, XMLTABLE ('$cust/customerinfo/phone' PASSING C.INFO as "cust"
                           COLUMNS "CUSTNAME" CHAR(30) PATH '../name',
                           "PHONETYPE" CHAR(30) PATH '@type',
                           "PHONENUM" XML PATH '.'
                          ) as X

This query yields the following result for the two XML documents (the output has been formatted for clarity):