This discussion is archived
10 Replies Latest reply: Sep 13, 2012 7:56 AM by odie_63 RSS

Generating large amounts of XML without running out of memory

960054 Newbie
Currently Being Moderated
Hi there,
I need some advice from the experienced xdb users around here. I´m trying to map large amounts of data inside the DB (Oracle 11.2.0.1.0) and by large I mean files up to several GB. I compared the "low level" mapping via PL/SQL in combination with ExtractValue/XMLQuery with the elegant XML View Mapping and the best performance gave me the View Mapping by using the XMLTABLE XQuery PATH constructs. So now I have a View that lies on several BINARY XMLTYPE Columns (where the XML files are stored) for the mapping and another view which lies above this Mapping View and constructs the nested XML result document via XMLELEMENT(),XMLAGG() etc. Example Code for better understanding:
CREATE OR REPLACE VIEW MAPPING AS
SELECT  type, (...)  FROM XMLTYPE_BINARY,  XMLTABLE ('/ROOT/ITEM' passing xml
     
     COLUMNS

      type       VARCHAR2(50)          PATH 'for $x in .
                                                            let $one := substring($x/b012,1,1)
                                                            let $two := substring($x/b012,1,2)
                                                            return
                                                                if ($one eq "A")
                                                                  then "A"
                                                                else if ($one eq "B" and not($two eq "BJ"))
                                                                  then "AA"
                                                                else if (...)
      (...)

-----------------------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE VIEW RESULT AS
select XMLELEMENT("RESULTDOC",
                        
                 (SELECT XMLAGG(
                         XMLELEMENT("ITEM",
                                      XMLFOREST(
                                           type "ITEMTYPE",
                                           (...)

) as RESULTDOC FROM MAPPING;

----------------------------------------------------------------------------------------------------------------------------
Now all I want to do is materialize this document by inserting it into a XMLTYPE table/column.

insert into bla select * from RESULT;

Sounds pretty easy but can´t get it to work, the DB seems to load a full DOM representation into the RAM every time I perform a select, insert into or use the xmlgen tool. This Representation takes more than 1 GB for a 200 MB XML file and eventually I´m running out of memory with an

ORA-19202: Error occurred in XML PROCESSING
ORA-04030: out of process memory

My question is how can I get the result document into the table without memory exhaustion. I thought the db would be smart enough to generate some kind of serialization/datastream to perform this task without loading everything into the RAM.

Best regards

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points