[jtittler]

I am writing a stored proc package to read data from and XML shred it and insert it into a relational db. I'm using XMLType views as my main entry into the db since the web portal is speaking XML and the db is only relational. No XML storage. In particular cases, I need to extract a subset of the XML and process it with a PIPELINED Table function so each element can be inserted as its own row. This happens when the xml element in question has a member of its sequence with maxOccurences="unbounded". The first step is to extract that chunk of XML which has the collection of similar elements and pass that XMLType object onto the PIPELINED function.

In my example code I have an element <idb:fileTemplate> which has a collection of 1...unbounded <idb:column> elements. Extracting these column elements gives me the following error:

-------------------------------------
PROCESSING COLUMNS
-------------------------------------
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 lines of code are:
---------------------------------
xpth VARCHAR2(200);
elem XMLType;
tplXML XMLType;

xpth := '/idb:fileTemplate/idb:column';
elem := tplXML.extract(xpth, 'xmlns:idb="http://www.itt.com/IDB"');
---------------------------------

Any ideas? Attached you will find the sample schema and test xml file. If you need more information to figure this out, please IM or email me. This matter is very critical for me. If I don't fix it, I miss my deadline Tues. Yes, I do work nights and weekends. Ah, the plight of a work-from-home Mom.

Thank you ever so much,
Julie

Oracle 10.2.0.1, Windows XP sp2

[jtittler]

If I try this XPath expression in Stylus Studio XPath Query window on the test xml file, it works, but it gives me the same URI error. This query gives me all the column elements from the xml file, but errors in Oracle. I'm confused.

//idb:column