Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-01 |
|
Functions, 174 of 177
XMLSequence::=
XMLSequence
has two forms:
XMLType
instance and returns a varray of the top-level nodes in the XMLType
.REFCURSOR
instance, with an optional instance of the XMLFormat
object, and returns as an XMLSequence
type an XML document for each row of the cursor.Because XMLSequence
returns a collection of XMLType
, you can use this function in a TABLE
clause to unnest the collection values into multiple rows, which can in turn be further processed in the SQL query.
See Also:
Oracle9i XML API Reference - XDK and Oracle XML DB for more information on this function |
The following example shows how XMLSequence
divides up an XML document with multiple elements into VARRAY
single-element documents. In this example, the TABLE
keyword instructs Oracle to consider the collection a table value that can be used in the FROM
clause of the subquery:
SELECT EXTRACT(warehouse_spec, '/Warehouse') as "Warehouse" FROM warehouses WHERE warehouse_name = 'San Francisco'; Warehouse ------------------------------------------------------------ <Warehouse> <Building>Rented</Building> <Area>50000</Area> <Docks>1</Docks> <DockType>Side load</DockType> <WaterAccess>Y</WaterAccess> <RailAccess>N</RailAccess> <Parking>Lot</Parking> <VClearance>12 ft</VClearance> </Warehouse> 1 row selected. SELECT VALUE(p) FROM warehouses w, TABLE(XMLSEQUENCE(EXTRACT(warehouse_spec, '/Warehouse/*'))) p WHERE w.warehouse_name = 'San Francisco'; VALUE(P) ---------------------------------------------------------------- <Building>Rented</Building> <Area>50000</Area> <Docks>1</Docks> <DockType>Side load</DockType> <WaterAccess>Y</WaterAccess> <RailAccess>N</RailAccess> <Parking>Lot</Parking> <VClearance>12 ft</VClearance> 8 rows selected.
|
![]() Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|