The following query uses XMLTABLE to retrieve items from a sequence:
SELECT x.item FROM xmltab, XMLTABLE('$custXML/customers/customer' passing xmltab.xmldoc as "custXML" COLUMNS "item" XML PATH "." ) AS x;
The result of this query is multiple rows, each containing a single customer element in the item column.
Row #1:
<customer> <Name>John Hancock</Name> <Address>100 1st Street One City, CA 12345</Address> <Phone1>(999)9999-999</Phone1> <Phone2>(999)9999-998</Phone2> <Fax>(999)9999-997</Fax> <Email>John@somecompany.com</Email> <order Number="NW-01-16366" Date="Feb/28/2001"> <Contact>Mary Shannon</Contact> <Phone>(987)6543-210</Phone> <ShipTo>Widgets Inc., 123 Regency Parkway, Portland, OR 43211</ShipTo> <SubTotal>2355.00</SubTotal> <Tax>141.50</Tax> <Total>2496.50</Total> <item ID="001"> <Quantity>100</Quantity> <PartNumber>F54709</PartNumber> <Description/> <UnitPrice>23.55</UnitPrice> <Price>2355.00</Price> </item> </order> </customer>
Row #2:
<customer> <Name>Jim Smith</Name> <Address>200 2nd Street, San Diego, CA 12345</Address> <Phone1>(858)555-1234</Phone1> <Phone2>(858)555-1234</Phone2> <Fax>(858)555-9876</Fax> <Email>Jim@somecompany.com</Email> <order Number="JS-01-16366" Date="Feb/29/2001"> <Contact>Jim Smith</Contact> <Phone>(858)555-1234</Phone> <ShipTo>Acme co., 2467 Pioneer Road, San Diego, CA 12345</ShipTo> <SubTotal>1242.00</SubTotal> <Tax>141.50</Tax> <Total>1383.50</Total> <item ID="001"> <Quantity>10</Quantity> <PartNumber>F54709</PartNumber> <Description/> <UnitPrice>124.20</UnitPrice> <Price>1242.00</Price> </item> </order> </customer>