CODEWITHSUNDEEP
xmloracle-databaseplsql
TKlim
TKlim

Reputation: 11

method to generate large xml for about 3GB in file size

My data is in Oracle 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.

I have been searching online and trying the suggestions, to try to generate xml file for about 3GB in file size.

So, I have used SSRS, oracle xmldom package in PLSQL, SQL (xmlelement, xmlagg...etc), manual SQL and insert line by line into table (select '<starttag>'|| table.value ||'</endtag>' from table).

What else i shall try next ?

(by the way, i am almost about to go Java StaX way but since i am not a program developer, i am not given the resource. So, i am stuck with oracle, SQL, PLSQL is the language i will use.)

my sample code:

https://community.oracle.com/thread/4197304

https://community.oracle.com/thread/4199003

I found an article here about the best practice to generate large xml file. I am now able to generate the xml file using the manual way but it took me almost 2 full days to generate xml file with file size about 80MB. My current approach will take me ages to generate the actual 3GB xml file.

How does everyone do it? How much time is the norm for the approach you all using to generate large xml file?

Upvotes: 1

Views: 404

Answers (1)

Dmitry Demin
Dmitry Demin

Reputation: 2113

I use the simplest method of forming the main part of xml. This is sqlplus and spool to file. The running time of the script is 6 minutes.

[oracle@krw-sql-ora12-01 test_xml]$ ls -l
total 164252
-rwxr-xr-x. 1 oracle oinstall       405 Feb 22 10:43 test_unload_xml.sh
-rw-r--r--. 1 oracle oinstall 168189616 Feb 22 10:49 test_xml.xml

[oracle@krw-sql-ora12-01 test_xml]$ more test_unload_xml.sh
 #!/bin/sh

    sqlplus -s /  as sysdba <<EOF
    set heading off
    set termout OFF
    SET FEEDBACK OFF
    SET TAB OFF
    set pause off
    set verify off
    SET UNDERLINE OFF
    set trimspool on
    set timing off
    set echo off
    set linesize 1000
    set pagesize 0
    spool test_xml.xml
    select '<starttag>'|| ID_SKL||'</endtag>'  from xxx.yyyy;
    spool off
    exit;
    EOF

For Example output.

<starttag>752</endtag>
<starttag>753</endtag>
<starttag>755</endtag>
<starttag>756</endtag>
<starttag>758</endtag>
<starttag>759</endtag>
<starttag>767</endtag>
<starttag>772</endtag>
<starttag>778</endtag>
<starttag>784</endtag>
<starttag>789</endtag>
<starttag>798</endtag>
<starttag>800</endtag>
<starttag>804</endtag>
<starttag>805</endtag>

Upvotes: 1

Related Questions