Loop through XML Nodes

[MikeAJ]

I have an xml document that I need to read and insert records from. The nodes can be buried as many levels deep as they need to be, and it's giving me some problems. Here's my xml doc:

<PACKAGE segment1 = "CFP" inventory_item_id = "213301">
    <OPTION_CLASS segment1 = "CFP PAC" inventory_item_id = "280525">
        <OPTION_CLASS segment1 = "CFP MODULES" inventory_item_id = "213298">
            <PART segment1 = "888318-303" quantity = "1" inventory_item_id = "231378"/>
        </OPTION_CLASS>
        <PART segment1 = "970800-03" quantity = "1" inventory_item_id = "279533"/>
        <PART segment1 = "970800-08" quantity = "1" inventory_item_id = "279535"/>
        <OPTION_CLASS segment1 = "CFP BACKPLANE OPT" inventory_item_id = "280519">
            <PART segment1 = "888718-03" quantity = "1" inventory_item_id = "158038"/>
            <PART segment1 = "888718-08" quantity = "1" inventory_item_id = "158039"/>
        </OPTION_CLASS>
        <OPTION_CLASS segment1 = "CFP CTRLR OPT" inventory_item_id = "280521">
            <PART segment1 = "888318-2120" quantity = "1" inventory_item_id = "213881"/>
            <PART segment1 = "888318-2100" quantity = "1" inventory_item_id = "231950"/>
            <PART segment1 = "888318-2110" quantity = "1" inventory_item_id = "231951"/>
        </OPTION_CLASS>
        <OPTION_CLASS segment1 = "CFP PWR OPTION" inventory_item_id = "280523">
            <PART segment1 = "888578-01" quantity = "1" inventory_item_id = "32081"/>
            <PART segment1 = "888583-01" quantity = "1" inventory_item_id = "32818"/>
            <PART segment1 = "888585-01" quantity = "1" inventory_item_id = "33789"/>
        </OPTION_CLASS>
        <OPTION_CLASS segment1 = "CFP OS STACK" inventory_item_id = "283258">
            <PART segment1 = "970592-01" quantity = "1" inventory_item_id = "283259"/>
        </OPTION_CLASS>
        <PART segment1 = "888725-01" quantity = "1" inventory_item_id = "172318"/>
    </OPTION_CLASS>
</PACKAGE>

For each option class, I need to insert a record in two different tables, and for each part, I need to insert one record.

I've used this function in the past to loop through node, but when I call it recursively, it doesn't work:

   CURSOR getNodeByXPath(cv_doc  XMLType, cv_path VARCHAR2) IS
      SELECT value(p) XML
        FROM TABLE(XMLSequence(extract(cv_doc,cv_path))) p;

What I'm basically trying to do is this:

PROCEDURE insert_package(node in XMLTYPE)

   FOR EACH PART LOOP
     INSERT PART RECORD
   END LOOP

   FOR EACH OPTION_CLASS LOOP
      INSERT HEADER
      INSERT LINE
      insert_package(node.extract('OPTION_CLASS'))
   END LOOP

END insert_package;

Anyone know how I can loop through every node to achieve this? Thanks!

 

[taupirho]

I wonder if the file you show is just a sample or is it the actual doc. Reason I ask is that if the doc is < 64K chars I would load it into a database table using code similar to that shown in this link:

http://www.oracle.com/technology/sa...nippet/xmldb/HowToLoadLargeXML.html#PLSQLCode

Once its in a table you should be able to process it more easily using the various SQL/XMLDB type operators.


In order to understand recursion, you must first understand recursion.

 

[MikeAJ]

So what are these various SQL/XMLDB type operators you speak of? That's really what I'm looking for.

Thanks,
Mike

 

[taupirho]

This link might be of help even though its for 9.2

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96620/xdb04cre.htm#1024805

In order to understand recursion, you must first understand recursion.