RE: xml->dml ?

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@trantor:/tmp/ [A1020] cat dept.xml
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <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@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

Other related posts: