I am trying to write a package of procedures to shred some XML documents passed to me from a web portal for insertion into a relational Oracle 10.2.0.1 DB. I'm using XMLType views for insertion. It just seemed like it might be easier. All is well when it deals with elements which don't have collections in them, ie. maxOccur="unbounded" elements. Those I need to treat special. I need to extract the repeating elements, and feed them to a table fx as individual rows for insertion into their appropriate table. Strangely enough, where I'm stuck currently is the extraction of the repeating elements from the XML file. I'm getting the following error:
Error processing IDB_FILE_TEMPLATE.insertFileTemplateFromFile
XPATH = "/idb:fileTemplate/idb:column"
Oracle Error Stack: ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00284: namespace prefix to NULL URI is not
allowed
Error at line 1
The offending code when trying to extract all column elements from the file is:
xpth := '/idb:fileTemplate/idb:column';
elem := tplXML.extract(xpth, 'xmlns:idb="http://www.itt.com/IDB"');
elem is XMLType.
Attached you will find my .SQL script to create the package, so you can see the source. I know there are other bugs in it. I'm just trying to fix this current one, but if you have tips on others, I wouldn't complain. I've also attached the XSD and sample XML file. The main function in the package to look at is insertFileTemplateFromFile. Other functions which are important are extractColumns, and insertColsIntoView.
Any help you can offer would be greatly appreciated. I'm good and truly stuck here and am running out of time to get this to work. I've never done this XML stuff in Oracle before and I find the available docs a smidge lacking.
2 Types defined elsewhere which are used are:
CREATE OR REPLACE TYPE xmlRecType AS OBJECT (elem CLOB);
CREATE OR REPLACE TYPE tbl_xmlElements AS TABLE OF xmlRecType;
Thank you ever so much,
Julie