Recursive xml data with xmltable() ?
Hi all
I've got the following XML, and would like to store it relationally, preserving the hierarchy..
The problem is, that each row item can have any number of "rowset name='contents'" children. And the row items in there can again have any number of "contents" children. And so on. Depth is unknown, unlimited.
Is this doable ?
What I'm looking for is to get hierarchical data, sort of itemid locationid parent_itemid -- so that I can process the data normally in a relational fashion using Oracle's hierarchical queries.
Any ideas would be greatly appreciated!!
Cheers
Stefan
<?xml version=''1.0'' encoding=''UTF-8''?>
<eveapi version="1">
<currentTime>2007-12-01 17:55:07</currentTime>
<result>
<rowset name="assets" key="itemID" columns="itemID,locationID,typeID,quantity,flag,singleton">
<row itemID="150354641" locationID="30000380" typeID="11019" quantity="1" flag="0" singleton="1">
<rowset name="contents" key="itemID" columns="itemID,typeID,quantity,flag,singleton">
<row itemID="150354709" typeID="16275" quantity="200000" flag="5" singleton="0" />
<row itemID="150354710" typeID="16272" quantity="150000" flag="5" singleton="0" />
<row itemID="150354711" typeID="16273" quantity="150000" flag="5" singleton="0" />
<row itemID="150354712" typeID="24597" quantity="1000" flag="5" singleton="0" />
<row itemID="150354713" typeID="24596" quantity="1000" flag="5" singleton="0" />
<row itemID="150354714" typeID="24595" quantity="1000" flag="5" singleton="0" />
<row itemID="150354715" typeID="24594" quantity="1000" flag="5" singleton="0" />
<row itemID="150354716" typeID="24593" quantity="1000" flag="5" singleton="0" />
<row itemID="150354717" typeID="24592" quantity="1000" flag="5" singleton="0" />
<row itemID="150354718" typeID="16274" quantity="450000" flag="5" singleton="0" />
<row itemID="150354719" typeID="9848" quantity="1000" flag="5" singleton="0" />
<row itemID="150354720" typeID="9832" quantity="8000" flag="5" singleton="0" />
<row itemID="150354721" typeID="3689" quantity="5000" flag="5" singleton="0" />
<row itemID="150354722" typeID="3683" quantity="25000" flag="5" singleton="0" />
<row itemID="150354723" typeID="44" quantity="4000" flag="5" singleton="0" />
</rowset>
</row>
<row itemID="150354706" locationID="30001984" typeID="11019" quantity="1" flag="0" singleton="1">
<rowset name="contents" key="itemID" columns="itemID,typeID,quantity,flag,singleton">
<row itemID="150354741" typeID="24593" quantity="400" flag="5" singleton="0" />
<row itemID="150354742" typeID="24592" quantity="400" flag="5" singleton="0" />
<row itemID="150354755" typeID="16275" quantity="199000" flag="5" singleton="0" />
<row itemID="150354837" typeID="24597" quantity="400" flag="5" singleton="0" />
<row itemID="150354838" typeID="24596" quantity="400" flag="5" singleton="0" />
<row itemID="150354839" typeID="24595" quantity="400" flag="5" singleton="0" />
<row itemID="150354840" typeID="24594" quantity="400" flag="5" singleton="0" />
<row itemID="150356329" typeID="14343" quantity="1" flag="5" singleton="0" />
</rowset>
</row>
<row itemID="150212056" locationID="60001078" typeID="25851" quantity="10" flag="4" singleton="0" />
<row itemID="150212057" locationID="60001078" typeID="20424" quantity="20" flag="4" singleton="0" />
<row itemID="150212058" locationID="60001078" typeID="20421" quantity="20" flag="4" singleton="0" />
<row itemID="150357641" locationID="30001984" typeID="23" quantity="1" flag="0" singleton="1">
<rowset name="contents" key="itemID" columns="itemID,typeID,quantity,flag,singleton">
<row itemID="150357740" typeID="16275" quantity="9166" flag="0" singleton="0" />
</rowset>
</row>
<row itemID="150212062" locationID="60001078" typeID="944" quantity="1" flag="4" singleton="1" />
<row itemID="150212063" locationID="60001078" typeID="597" quantity="1" flag="4" singleton="0" />
</rowset>
</result>
<cachedUntil>2007-12-02 16:55:07</cachedUntil>
</eveapi>