XML has a parent element Interaction and each Interaction has different elements like Consumer, Campaign, Response, Survey, MultiSuppressions. Now i need to insert Consumer element data into table1, Response and Campaign elements data into table2, Survey data into Table3 and Multisuppression data into table4 with interaction number (this can be rownumber) so that i can link all the tables based on interaction number.

I googled on parsing xml and found xmltable can be used to parse xml. I wrote below procedure, but it will not work if i include MultSuppressions (will get cartesians). Can someone please help me on parsing the above xml.

I started of with writing pl/sql block to parse the xml. If i process row by row then the code is working. If i use bulk collect it's not working (i mean working but no output). Am i doing something wrong while using bulk collect.

Yes i do agree with you, pl/sql is slower when compared with SQL. Your solution is working without any issue if i have survey element with one MultiSuppressioncode element in one interaction. If i have more than one multisuppressioncode element in one interaction along with Survey element then the query is cartesianing survey and multisuppression data which will insert duplicate records into survey table while using INSERT ALL.

If i have more than one multisuppressioncode element in one interaction along with Survey element then the query is cartesianing survey and multisuppression data which will insert duplicate records into survey table while using INSERT ALL.

The following may not be the most efficient method, but does appear to eliminate the duplicates. I have added rnk2 and rnk3 to the when clauses and outer select statement. Solomon may have a better method.

Thank you Solomon and Barbara for your time on helping with this query. Query is working great.

Tested the query with my production file which has 800+ interactions (near 10,000+ survey records) and the query is running forever. Converted query to PL/SQL block which finished in 31 secs. Please take a look at the pl/sql block and suggest me if i can further tune the code. Appreciate your help.