I have Oracle xmldb with a table containing an xmltype column that has multiple records of large xmls. The size of each xml would be around 100MB with a portion of the xml having repeating elements. The repeating elements can be in few thousands in count in each xml. I need to read only the repeating elements. What is the best possible way to read the repeating elements in batches of element collection? Assuming the batch size could be 50 repeating elements at once. I have written the code using DBMS_XMLGEN for writing the xml efficiently in batches, but could not figure out a way to do a similar batch read. Kindly suggest an efficient approach.
I have figured out a couple of ways of doing this with one sql query.
With in the sql query:
1. create an xmltable with a column for repeating xml elements.
2. Select the column that has each repeating element
select elements.*
from
xmltable('//*/parent[@name="E1"].*' passing xml
columns
repeating_element xmltype path '/repeatingElement') elements;
using table() operator:
The query looks something like this:
select elements.*
from
table(xmlsequence(extract(xml, '//*/parent[@name="E1"]/repeatingElement'))) elements;
Using any of the above queries, open a cursor with limit clause on this sql query to query records in batches.