Re: Export/Import Using Oracle XML

Ha! That's a very interesting article koert54, thanks!

But fortunately I'm working with a very small database and there are no requirements to support a large one. I'll also only be dealing with one row at a time.

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

"koert54" <nospam_at_nospam.com> wrote in message news:YBmKa.687$P26.1691_at_afrodite.telenet-ops.be...
> http://www.interealm.com/tao/parchxml.html
>
>
> "Leo J. Hart IV" <leo.hart_at_fmr.com> wrote in message
> news:b6bda431.0306251057.5a3ba84e_at_posting.google.com...
> > Hello,
> >
> > I've been trying to use the various XML utilities (dbms_mlquery,
> > dbms_xmlgen, and SQLX) to retrieve data from an Oracle 9i database in
> > a format I can use to import the data into another, structurally
> > identical database. Here's my GREATLY simplified scenario:
> >
> > I have two tables:
> >
> > CREATE TABLE tbl_one
> > (id NUMBER NOT NULL,
> > name VARCHAR2(30) NOT NULL)
> > /
> >
> > CREATE TABLE tbl_two
> > (id NUMBER NOT NULL,
> > name VARCHAR2(30) NOT NULL,
> > tbl_one_id NUMBER NOT NULL)
> > /
> >
> > There's one foreign key:
> > tbl_two.tbl_one_id -> tbl_one.id
> >
> > There is a sequence being used to generate values for each primary key
> > in each table.
> >
> >
> > I want to be able to get an XML representation of a row in tbl_two,
> > including any tbl_one rows it is dependant on. The XML should not
> > specify any primary key or foreign key values explicitly as there is
> > no guarantee that the destination database will have the same primary
> > key values to loop up.
> >
> > Here's the XML I imagine I would need:
> > <tbl_two primaryKeyCol="id" primaryKeySeq="one_seq">
> > <name dataType="VARCHAR2(30)">This</name>
> > <tbl_one_id dataType="NUMBER">
> > <tbl_one primaryKeyCol="id" primaryKeySeq="two_seq">
> > <name dataType="VARCHAR2(30)">That</name>
> > </tbl_one>
> > </tbl_one_id>
> > </tbl_two>
> > This way, my import process could build a set of insert statements
> > doing the following:
> > If, according to the XML, the tbl_two row already exists in the
> > destination database:
> > Return the primary key value
> > Otherwise:
> > For each column in the current table (tbl_two):
> > If the current column is not a FK column:
> > Add the column and the column value to the current table's
> > SQL statement
> > If the current column is a FK column:
> > Drill down one node to get the table node
> > Recursively call this process usnig tbl_one starting at
> > the top to determine the FK column's value.
> > Execute the INSERT SQL and return the resulting primary key
> > value
> > My problem is that I've tried using all of Oracle's provided XML tools
> > to generate a file like the one above, but none of them seem to cut
> > it. The closest I've gotten is the following:
> > <tbl_two primaryKeyCol="id" primaryKeySeq="one_seq">
> > <name>This</name>
> > <tbl_one_id>
> > <tbl_one primaryKeyCol="id" primaryKeySeq="two_seq">
> > <name>That</name>
> > </tbl_one>
> > </tbl_one_id>
> > </tbl_two>
> > This is missing the datatype attribute for each column which I'll
> > definitely need if I come across any DATE columns.
> >
> >
> > Has anyone else tried to tackle this problem or does anyone have an
> > idea for a solution. I would GREATLY appreciate your help!
> >
> >
> >
> > Thanks,
> >
> > Leo J. Hart IV
> > Sr. SWE/Developer
> > Fidelity Investments - CFIT
>
>
Received on Wed Jun 25 2003 - 15:17:07 CDT