Inserting large XML DataWE

System Specs: Oracle 9i (9104) Windows XP PRO (Fully patched), 512 MD RAM, 2.4 GHz P4 (Dev laptop)

We have several areas in our application that deal with a parent child relationship between tables, some with potentially hundreds of details... Up until this point we have been dealing with these large documents in the following manner...

First we define a collection variable like this...

<Code>
-- Create a Table Type to hold restore item information
SUBTYPE tmpRestoreItems_T IS tmpRestoreItems%ROWTYPE; TYPE listRestoreItems_T IS TABLE OF tmpRestoreItems_T;
-- Declare and Construct the TableVariable
lstRestoreItems listRestoreItems_T := listRestoreItems_T(); </Code>

After which we prepare the DOM and get it ready to start pulling data from by doing this...

<Code>
-- Prepare a DOM Document so we can insert the information into the table

• Create DOM Handle domDoc := DBMS_XMLDOM.NewDomdocument(XMLDoc);
• Create Document element Handle (root) domRoot := DBMS_XMLDom.getDocumentElement(domDoc);
• Make the root element a node nodeRoot := DBMS_XMLDom.makenode(domRoot);
• Use XPath to retrieve list of Items

nodesItem := DBMS_XSLPROCESSOR.SelectNodes(NodeRoot, vcNodeXPath);

nListLength := DBMS_XMLDOM.getLength(nodesItem); lstRestoreItems.EXTEND(nListLength);
</Code>

Once the document is ready to go we loop through the Document and pull all of the data out into the collection

<Code>
FOR i IN 0 .. nListLength - 1
LOOP

• Access Node by index and pull back values for insert into the temp table

nodeItem := DBMS_XMLDom.Item(nodesItem, i);

lstRestoreItems(i + 1).Counter :=
BMQR_Common.SelectSingleNodeValue(nodeItem, 'Counter');

lstRestoreItems(i + 1).ItemID := BMQR_Common.SelectSingleNodeValue(nodeItem, vcNodeItem);

END LOOP;
</Code>

After we have the data from the DOM into the collection variable we insert the entire collection at once like this...

<Code>
-- Insert List contents into the temporary table

FORALL INDX IN lstRestoreItems.FIRST .. lstRestoreItems.LAST

INSERT INTO tmpRestoreItems
VALUES lstRestoreItems(INDX);
</Code>

This approach has worked well for most areas of the application with relatively small natural limits on children in the relationship, however, we are now working on doing something where the children could be several hundreds of items and this approach does not seem to scale well to that number of records. Is there a better way to get the data from the XML Document into the table that is quicker... It shouldn't take almost a minute to loop through 300 nodes and insert the data into a table. Tracing shows that the lions share of the time is eaten up in the loop that pulls the XML data into the collection and almost no time is taken inserting that data into the table. Is there a method of pulling the data right from the XML Document and inserting it directly into the table, avoiding the painful step of populating the collection? An INSERT INTO FROM XML Would be nice here (oops did I say that?)... I have looked around several sites and spent some time on google groups but haven't found a better solution yet. Anyone out there doing this with better results. Looking for a lead to follow, I can do the leg work just need a pointer to a technique.

Thanks,

More info available on request. WC/Lucas AT coolblue DOT com (remove "/")

Bill Received on Tue Jun 22 2004 - 15:55:54 CDT