3 Replies Latest reply: Jul 9, 2013 3:44 PM by odie_63 RSS

    Create Big XML files ( extract ) from Relational Tables

    Kevin_K

      Experts: I need to create a big XML extract more than 5Gb , from relations tables using SQLX. I read the excellent FAQ given by MDrake in the following thread.

       

      https://forums.oracle.com/thread/418001

       

      Question

      1) Is it better to use XML schema, My XML output format is pretty much going to be static, so I can register an XML schema .

      2) Does Registering the XMLschema help with better memory management. I recall I used to get out of memory exception when I generated xml documents on oracle 10g using DBMS_XMLGEN.

      3) Can I generate this 5 Gb of XML file using oracle's default DOM parser?

       

      Thanks

      Kevin

        • 1. Re: Create Big XML files ( extract ) from Relational Tables
          odie_63

          Hi Kevin,

          1) Is it better to use XML schema, My XML output format is pretty much going to be static, so I can register an XML schema .

          2) Does Registering the XMLschema help with better memory management. I recall I used to get out of memory exception when I generated xml documents on oracle 10g using DBMS_XMLGEN.

          No, an XML schema won't help for the generation.

          It is useful though if you're looking for the opposite task, i.e. loading an XML file into database tables.

           

          3) Can I generate this 5 Gb of XML file using oracle's default DOM parser?

          What is the default DOM parser ? Do you mean DBMS_XMLDOM APIs?

           

          Since you want to generate XML, there's not much to parse.

           

          Generally, using SQL/XML functions is the way to go.

          You may still hit some performance/memory issues while reaching such a size, especially with large XMLAgg aggregation context.

          If you do, you may switch to chunk generation instead. I've got some pretty good result with this approach and the parallel query feature.

          • 2. Re: Create Big XML files ( extract ) from Relational Tables
            Kevin_K

            Thanks for your response Odie.

             

            By parser I meant the following issue:

             

            Ask Tom "XML Generation/Manipulation using SQL"

             

            You are right, I have seen the memory issues while aggregating the data.

             

            If it possible to combine the chunks into 1 big valid xml file? Can you please explain the parallel query feature you had mentioned . Do you have any examples of chunk generation and parallel query that you can share?

             

            Thanks

            Kevin

            • 3. Re: Create Big XML files ( extract ) from Relational Tables
              odie_63

              By parser I meant the following issue:

               

              Ask Tom "XML Generation/Manipulation using SQL"

              I still don't know what you're referring to specifically.

              There's a lot of methods described there, most of them being old stuff inherited from the early days of the XDK (DBMS_XMLQuery for ex.).

               

              One thing for sure, I would never use DBMS_XMLQuery (Java-based) or DBMS_XMLGEN to generate large amount of data, especially if the structure is rather complex.

               

              If it possible to combine the chunks into 1 big valid xml file? Can you please explain the parallel query feature you had mentioned . Do you have any examples of chunk generation and parallel query that you can share?

              Sure, let me dig out some examples...