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 - 14:20:24 CDT
