Export/Import Using Oracle XML

From: Leo J. Hart IV <leo.hart_at_fmr.com>
Date: 25 Jun 2003 11:57:30 -0700
Message-ID: <b6bda431.0306251057.5a3ba84e@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 - 13:57:30 CDT