We recently added a requirement onto an existing system where some clients could provide their data to us in XML format. So, we need to parse an XML file and load that data into our existing Oracle tables.
I'm pretty much out of my element. Could someone point me in the right direction so I could get a good start?
There are about a million ways of skinning this one...
Assuming you using 10g, look up XMLType and dbms_xmlstore. The XMLType has methods to load XML documents from URL, clob and filesystem. You can then use xmlstore.insertxml, xmlstore.updatexml and xmlstore.deletexml to apply document to your tables.
Note that the xml must be in ROWSET/ROW format when applying the changes to your db.
And one last thing... take care using dbms_xmlstore. There is a bug in Oracle 10.1... If you have a ' charachter in any of your node values, the package crashes and gives an error about an invalid table or something. Looked this up on Metalink and it is logged as a bug. I didn't find a patch though.
e.g. The following crashes when using dbms_xmlstore, but works fine using dbms_xmlsave...
A work around this is to use the old dbms_save package. It basically provides the exact same functionality, except it is implemented correctly. However, it is has a Java implementation which will consume more resources. Personally, I have not had problems with this yet.
parammap := 'username="''' || username || '''"';
h := dbms_xmlsave.newcontext('CARGO');
row_count := dbms_xmlsave.updatexml(h, xmlinput.transform(xslt, parammap).getCLOBVal());
when OTHERS then