Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Approach to parse large number of XML files into the relational table.

225452Jan 24 2013 — edited Jan 31 2013
We are exploring the option of XML DB for processing a large number of files coming same day.
The objective is to parse the XML file and store in multiple relational tables. Once in relational table we do not care about the XML file.
The file can not be stored on the file server and need to be stored in a table before parsing due to security issues. A third party system will send the file and will store it in the XML DB.
File size can be between 1MB to 50MB and high performance is very much expected other wise the solution will be tossed.
Although we do not have XSD, the XML file is well structured. We are on 11g Release 2.

Based on the reading this is what my approach.
1. CREATE TABLE XML_DATA
(xml_col XMLTYPE)
XMLTYPE xml_col STORE AS SECUREFILE BINARY XML;

2. Third party will store the data in XML_DATA table.
3. Create XMLINDEX on the unique XML element
4. Create views on XMLTYPE
CREATE OR REPLACE FORCE VIEW V_XML_DATA(
   Stype,
   Mtype,
   MNAME,
   OIDT
)
AS
   SELECT x."Stype",
          x."Mtype",
          x."Mname",
          x."OIDT"
   FROM   data_table t,
          XMLTABLE (
             '/SectionMain'
             PASSING t.data
             COLUMNS Stype VARCHAR2 (30) PATH 'Stype',
                     Mtype VARCHAR2 (3) PATH 'Mtype',
                     MNAME VARCHAR2 (30) PATH 'MNAME',
                     OIDT VARCHAR2 (30) PATH 'OID') x;

5. Bulk load the parse data in the staging table based on the index column.

Please comment on the above approach any suggestion that can improve the performance.

Thanks
AnuragT
This post has been answered by Marco Gralike on Jan 25 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 28 2013
Added on Jan 24 2013
12 comments
572 views