Re: Export/Import Using Oracle XML

"Leo J. Hart IV" <leo.hart_at_nospam.fmr.com> wrote in message news:<fqnKa.160$Mn.63_at_news-srv1.fmr.com>...

> So, any suggestions on HOW TO do something like this?
>

Here is one:

Use an O-R view to define the DOM-tree you want. This is done by creating object types for every row in the tables involved, then creating collection (nested table) data types for each row object involved, then creating an Object Relational view (see the OR Manual) that creates the "root" row and its attributes, including the collection data types of dependent rows.

THEN, you use the XML packages provided by Oracle in the XMLSDK to extract the data from the view as a DOM-tree. Possibly, in a format similar to this:

<tbl_one primaryKeyCol="id" >

    <name dataType="VARCHAR2(30)">This</name>     <tbl_two row_number=1 primaryKeyCol="id">

       <name dataType="VARCHAR2(30)">That</name>     </tbl_two>
    <tbl_two row_number=2 primaryKeyCol="id">

       <name dataType="VARCHAR2(30)">That</name>     </tbl_two>
</tbl_one>

To import, you can use the packages to insert into the O-R view, but you'll need INSTEAD OF triggers to then expand the DOM tree into its table elements.

HTH PS: Hope you're using 9i. If you're using 8i, then the DOM-tree via O-R mapping is limited to one level... DAMHIKT!
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Fri Jun 27 2003 - 00:45:43 CDT