This discussion is archived
5 Replies Latest reply: Jan 30, 2013 8:33 PM by user302631 RSS

Need help generating large xml file using a stored procedure.

user302631 Newbie
Currently Being Moderated
Hello Guys,

I am new to using XML in pl/sql. I have below procedure generating the xml file in the format that I want.
But performance is a hugh issue. I usually have to generate a file with 300-500 thousand records.
I have pasted my procedure and our database version. Also the output format.

I know one of the reason for performance problem is because I am using the same table three times.
Dont know if I can write the select statement in a diff way and still get the same format.

I need help in getting the xml file generated quickly and the output should be in the same format.

Your help is much appreciated.

select * from v$version

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Solaris: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production


CREATE OR REPLACE PROCEDURE generate_file
(
start_date VARCHAR2,
end_date VARCHAR2 )
IS
doc CLOB;
hdoc CLOB;
h1doc CLOB;
tdoc CLOB;

CURSOR getdata IS
SELECT XMLAGG (
XMLELEMENT (
"casereport",
XMLFOREST (
caseversion AS "caseversion",
caseid AS "caseid",
transmissiondateformat AS "transmissiondateformat",
transmissiondate AS "transmissiondate",
),
XMLFOREST (
XMLFOREST ( dsource AS "dsource",
numb AS " numb") AS " duplicate"),
XMLELEMENT (
"patient",
XMLFOREST (
onsetage AS "onsetage",
onsetageunit AS "onsetageunit",
weight AS "weight",
sex AS "sex"),
(SELECT XMLAGG (
XMLELEMENT (
"reaction",
XMLFOREST (
meddra AS "meddra",
startdateformat AS "startdateformat",
startdate AS "startdate",
enddateformat AS "enddateformat",
enddate AS "enddate",
outcome AS "outcome")))
FROM quaterly_data_extract
WHERE qdeid = 'U'),
(SELECT XMLAGG (
XMLELEMENT (
"drug",
XMLFOREST (
characterization AS "characterization",
product AS "product",
batchnumb AS "batchnumb",
authorizationnumb AS "authorizationnumb",
structurenumb AS "structurenumb",
administration AS " administration")))
FROM quaterly_data_extract
WHERE qdeid = 'N' )))) .getclobval() AS qdexml
FROM data_extract
WHERE id = 'Y' AND (receivedate BETWEEN TO_DATE(start_date, 'DD-MON-YYYY') AND TO_DATE(end_date, 'DD-MON-YYYY') );
BEGIN

dbms_lob.createtemporary(doc, true);


SELECT ( 'imbprod lang="en"' ) INTO hdoc FROM DUAL;

SELECT XMLELEMENT (
"imbprodmessageheader",
XMLFOREST (
'IMBP' AS "messagetype",
'9.1' AS "messageversion",
) ).getclobval()

INTO h1doc
FROM DUAL;

-- append header
dbms_lob.append(doc, hdoc);
dbms_lob.append(doc, h1doc);

FOR y IN getqde
LOOP
dbms_lob.append(doc, y.qdexml);
IF getqde%NOTFOUND THEN
EXIT;
END IF;
END LOOP;
SELECT( 'imbprod') INTO tdoc FROM DUAL;
-- append trailer
dbms_lob.append(doc, tdoc);

dbms_xslprocessor.clob2file(doc, 'QDE', 'test.xml', nls_charset_id('AL32UTF8'));
dbms_lob.freetemporary(doc);

END;
/


**output format**



<imbprod lang = "en">
<imbprodmessageheader>
<messagetype>IMBP</messagetype>
<messageversion>9.1</messageversion>
</imbprodmessageheader>

<casereport>
<caseversion>2<\caseversion>
<caseid>897564<\caseid>
<duplicate>
<dsource>online<\dsource>
<numb>9875<\numb>
<\duplicate>
<patient>
<onsetage>75<\onsetage>
<onsetageunit>lb<\onsetageunit>
<weight>134<\weight>
<sex>M<\sex>
<reaction>
<meddra>eee<\meddra>
<outcome>cough<\outcome>
<\reaction>
<reaction>
<meddra>www<\meddra>
<outcome>fever<\outcome>
<\reaction>
<reaction>
<meddra>fff<\meddra>
<outcome>dehydration<\outcome>
<\reaction>
<drug>
<characterization>fff<\characterization>
<product>fff<\product>
<batchnumb>fff<\batchnumb>
<\drug>
<drug>
<characterization>fff<\characterization>
<authorizationnumb>fff<\authorizationnumb>
<structurenumb>fff<\structurenumb>
<\drug>
<drug>
<characterization>fff<\characterization>
<batchnumb>fff<\batchnumb>
<administration>fff<\administration>
<\drug>
<\patient>
<\casereport>
<\imbprod>
  • 1. Re: Need help generating large xml file using a stored procedure.
    odie_63 Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!
    I have pasted my procedure and our database version. Also the output format.
    Is it really your procedure? I highly doubt so.
    For starters, the cursor names don't match.

    Same remark for the output format, which is not wellformed.

    Why use a FOR LOOP? The cursor always returns a single row.
    I know one of the reason for performance problem is because I am using the same table three times.
    I only see two occurrences of QUATERLY_DATA_EXTRACT and one of DATA_EXTRACT.
    What's the relationship between those two tables?
  • 2. Re: Need help generating large xml file using a stored procedure.
    user302631 Newbie
    Currently Being Moderated
    Hello Odie,

    Thanks for the response.

    Yes it is my procedure but I didn't want to paste the actual procedure so I massaged it ltl bit.
    FOR LOOP because it loops through all the records, I expect around 300 - 500 thousand records get into the xml file.
    I ran for 9000 records this morning and it is still running. Table data_extract got repeated 3 times.

    CREATE OR REPLACE PROCEDURE generate_file
    (
    start_date VARCHAR2,
    end_date VARCHAR2 )
    IS
    doc CLOB;
    hdoc CLOB;
    h1doc CLOB;
    tdoc CLOB;

    CURSOR getdata IS
    SELECT XMLAGG (
    XMLELEMENT (
    "casereport",
    XMLFOREST (
    caseversion AS "caseversion",
    caseid AS "caseid",
    transmissiondateformat AS "transmissiondateformat",
    transmissiondate AS "transmissiondate",
    ),
    XMLFOREST (
    XMLFOREST ( dsource AS "dsource",
    numb AS " numb") AS " duplicate"),
    XMLELEMENT (
    "patient",
    XMLFOREST (
    onsetage AS "onsetage",
    onsetageunit AS "onsetageunit",
    weight AS "weight",
    sex AS "sex"),
    (SELECT XMLAGG (
    XMLELEMENT (
    "reaction",
    XMLFOREST (
    meddra AS "meddra",
    startdateformat AS "startdateformat",
    startdate AS "startdate",
    enddateformat AS "enddateformat",
    enddate AS "enddate",
    outcome AS "outcome")))
    FROM data_extract
    WHERE qdeid = 'U'),
    (SELECT XMLAGG (
    XMLELEMENT (
    "drug",
    XMLFOREST (
    characterization AS "characterization",
    product AS "product",
    batchnumb AS "batchnumb",
    authorizationnumb AS "authorizationnumb",
    structurenumb AS "structurenumb",
    administration AS " administration")))
    FROM data_extract
    WHERE qdeid = 'N' )))) .getclobval() AS qdexml
    FROM data_extract
    WHERE id = 'Y' AND (receivedate BETWEEN TO_DATE(start_date, 'DD-MON-YYYY') AND TO_DATE(end_date, 'DD-MON-YYYY') );
    BEGIN

    dbms_lob.createtemporary(doc, true);


    SELECT ( 'imbprod lang="en"' ) INTO hdoc FROM DUAL;

    SELECT XMLELEMENT (
    "imbprodmessageheader",
    XMLFOREST (
    'IMBP' AS "messagetype",
    '9.1' AS "messageversion",
    ) ).getclobval()

    INTO h1doc
    FROM DUAL;

    -- append header
    dbms_lob.append(doc, hdoc);
    dbms_lob.append(doc, h1doc);

    FOR y IN getdata
    LOOP
    dbms_lob.append(doc, y.qdexml);
    IF getqde%NOTFOUND THEN
    EXIT;
    END IF;
    END LOOP;
    SELECT( 'imbprod') INTO tdoc FROM DUAL;
    -- append trailer
    dbms_lob.append(doc, tdoc);

    dbms_xslprocessor.clob2file(doc, 'QDE', 'test.xml', nls_charset_id('AL32UTF8'));
    dbms_lob.freetemporary(doc);

    END;
    /


    I am really sorry if the format is off
    Thanks,
    S
  • 3. Re: Need help generating large xml file using a stored procedure.
    user302631 Newbie
    Currently Being Moderated
    Hello Odie,

    Its the same table but based the qdeid I group the values together.
    For qdeid = "U" and "N" there would be multiple records

    Thanks,
    S.
  • 4. Re: Need help generating large xml file using a stored procedure.
    odie_63 Guru
    Currently Being Moderated
    I would need some relevant sample data to understand your data model and the XML output you're expecting. Right now I don't get it.

    Your current query selects all rows with ID = 'Y', then for each of those rows it selects the same set of rows where QDEID = 'U' or 'N' over and over again, how does that make sense?
    Are you sure the nested subqueries don't need to be correlated in some way to the outer query?
  • 5. Re: Need help generating large xml file using a stored procedure.
    user302631 Newbie
    Currently Being Moderated
    Hi Odie,

    Thank you very much for your early and prompt response.
    I was able to figure out how to do it.

    PS- Sorry I couldn't respond soon enough.

    Thanks again!
    S

    Edited by: 983178 on Jan 30, 2013 8:33 PM

Legend

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