5 Replies Latest reply: Jan 30, 2013 10:33 PM by user302631 RSS

    Need help generating large xml file using a stored procedure.

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