This discussion is archived
3 Replies Latest reply: Jul 9, 2013 1:44 PM by odie_63 RSS

Create Big XML files ( extract ) from Relational Tables

Kevin_K Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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...

Legend

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