xml data to columns table

Hi all,

Assuming i have this xml fragment, stored in a column named xmldata of CLOB type, in a table
'<Warehouse>

<WarehouseId>1</WarehouseId>
<WarehouseName>Montreal, Quebec</WarehouseName>
</Warehouse>
<Warehouse>

<WarehouseId>2</WarehouseId>
<WarehouseName>Southlake, Texas</WarehouseName>
</Warehouse>
'

How can i generate in one query, the xml elements to have them in columns table format ?

I know, i could do something like

select
extractvalue(a.xmldata,'//Warehouse/WarehouseId') id, extractvalue(a.xmldata,'//Warehouse/WarehouseName') name from mytable a

Result

id       name
1        Montreal, Quebec
2        Southlake, Texas

and that would satisfy my needs. But, what if i have many more xmlelements inside my xml ? I wouldn't like to select each column by each column as above. In fact, i'm looking something equal to the "select *" but from an xmldata so that it is directly splitted into the right number of columns, based on the xml elements. Is that clear for anybody ..?
Thanks for helping,
Regards,
Mike. Received on Tue May 10 2005 - 05:00:02 CDT