Oracle PL/SQL to Load XML

I don't know the best way to ask this, but let me explain the issue and it may help.

We currently have a feed of data that is distributed to us using an Oracle OAI hub. The data is fed to us using DBLinks.

The information provider is going to be upgrading to Oracle ODI, and for numerous reasons are mandating that all data transfer is conducted using encr开发者_如何转开发ypted XML files over SFTP.

Now this introduces a new issue for ourselves as data recipient, as we now need to amend our systems to load XML data into the tables that once were populated by DBLinks.

The set-up we currently have is:

• Oracle 10g (10.2.0.4)
  • Oracle is running on Unix (HP-UX)
• Numerous Win2k3 servers controlling interface / ETL flow.

So currently, the OAI hub will place data into a number of tables in our ETL layer. Say for example "PERSON". The structure of this table i dont believe is relevant...

When the data has been loaded into the PERSON table, the OAI delivery will transmit an End-of-file marker to the database, this is stored in table "EOF" - it holds a count of recieved records expected in "PERSON".

The windows server has a batch process that polls every 30 seconds, this checks to see if an EOF record exists, if it does then it kicks off our ETL processing.

I want to avoid changing this part of the system if possible, so what i am proposing as a solution is to parse the new XML files and load them into the Oracle database, the question is what is the best way to do this:

• The XML files will be delivered to the Windows Servers
• Preference would be to use PL/SQL to load the data

What is the best way to load the data into the Oracle (Unix) database, with the source data being on the Windows side.

I want to avoid having to use any Unix scripting if possible - as my development team don't have enough Unix experience to let them loose on this.

Any suggestions will be grateful.

The XML format is as below - and element names map to column names on DB:

<PERSON>
  <HEADER>
    <Creator>~</Creator>
    <DigitalSigniture>~</DigitalSigniture>
    <Owner>~</Owner>
    <Title>~</Title>
    <Marking>~</Marking>
   </HEADER>

   <PERSONS>
     <EMPLOYEE_NUMBER>~</EMPLOYEE_NUMBER>
     <FIRST_NAME>~</FIRST_NAME>
     <LAST_NAME>~</LAST_NAME>
     ......
   </PERSONS>
<PERSON>

EDIT: I am also conscious of volumes, on a normal (average) day i will be processing about 80,000 XML records, and on an exceptional day i will processing up to 300,000 (typically once per year).

To shred your XML and load them into Oracle-tables, you can use the technique I described in this blogpost: http://rwijk.blogspot.com/2010/03/shredding-xml-into-multiple-tables-in.html

Regards,
Rob.

Some links for loading the files: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb25loa.htm#ADXDB2900
http://www.oracle-developer.net/display.php?id=416

I would offer to forget about build-in Oracle XML features and use any normal language to process XML files on the "client side" and submit ready to use data to the DB. (Look for Python or .NET)

It could be a security risk, if you allows to access DB server file system from network.

80 000 records a day should not be a big problem for Oracle. We do much more close to real time.