RE: xml->dml ?

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Wed, 31 Aug 2005 09:39:34 +0200
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF134091@MSXVS04.trivadis.com>

Hi Chris

>i just recieved an xsd from a .net developer that tells me how to
>create a series of dml statements to extract data out of an xml
>document through extractvalue()
>
>i'd hate to manually do this. ...automating that myself is above my
>abilities at this point (it seems like a regular expression problem
>but i won't pretend to have a clue). ...i'v been asking lots of
>questions here lately so i guess another one (or two) won't hurt:
>
>is this something that occurs frequently when xml is to be loaded into
>relational tables? are there tools/scripts that automate this?

If the mapping is simple, XSU (a command line utility provided with XDK) could be a solution. Here an example on how you can load a XML file...

my input file

oracle_at_trantor:/tmp/ [A1020] cat dept.xml <?xml version = '1.0'?>
<ROWSET>

      <DEPTNO>10</DEPTNO>
      <DNAME>ACCOUNTING</DNAME>
      <LOC>NEW YORK</LOC>

</ROW>
<ROW num="2">

      <DEPTNO>20</DEPTNO>
      <DNAME>RESEARCH</DNAME>
      <LOC>DALLAS</LOC>

</ROW>
<ROW num="3">

      <DEPTNO>30</DEPTNO>
      <DNAME>SALES</DNAME>
      <LOC>CHICAGO</LOC>

</ROW>
<ROW num="4">

      <DEPTNO>40</DEPTNO>
      <DNAME>OPERATIONS</DNAME>
      <LOC>BOSTON</LOC>

</ROW>
</ROWSET>

set environment

export CLASSPATH=${ORACLE_HOME}/lib/xsu12.jar:${ORACLE_HOME}/lib/xmlparserv2.jar:${ORACLE_HOME}/jdbc/lib/ojdbc14.jar:${ORACLE_HOME}/rdbms/jlib/xdb.jar

run XSU

oracle_at_trantor:/tmp/ [A1020] java OracleXML putXML -user scott/tiger -fileName dept.xml dept                         successfully inserted 4 rows into dept

For more information refer to "Oracle XML Developer's Kit Programmer's Guide".

HTH
Chris

New Features Oracle Database 10g Release 2 seminars @ www.trivadis.com Italiano: Lugano (24-Nov)
Français: Genève (17-Nov)
Deutsch: Zürich (11-Oct), Hamburg (13-Oct), München (20-Oct), Basel (25-Oct),

Frankfurt (27-Oct), Bern (8-Nov), Düsseldorf (23-Nov), Stuttgart (13-Dec)

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 31 2005 - 02:41:36 CDT