PL/SQL Script to Generate XML Tags for XMLP Report

By | July 4, 2013

There are many ways to generate output in XML tags format, dbms_xmlgen is one of the way to generate.

Lets consider an example to display EMP table output in XMLP report in excel format, below is the scrip to generate xml tags

DECLARE
  --
  --Cursor to fetch the data
  --
  CURSOR data_cur
  IS
    --
    SELECT empno,ename,job,hiredate,sal FROM emp;
  --
  output_row data_cur%rowtype;
BEGIN
  --
  --
  dbms_output.put_line('<?xml version="1.0" encoding="US-ASCII" standalone="no"?>');
  fnd_file.put_line(fnd_file.output,'<?xml version="1.0" encoding="US-ASCII" standalone="no"?>');
  dbms_output.put_line('<OUTPUT>');
  fnd_file.put_line(fnd_file.output,'<OUTPUT>');
  --
  OPEN data_cur;
  LOOP
    --
    FETCH data_cur INTO output_row;
    EXIT
  WHEN data_cur%notfound;
    --
    dbms_output.put_line('<ROW>');
    fnd_file.put_line(fnd_file.output,'<ROW>');
    --
    dbms_output.put_line('<ENUM>'||dbms_xmlgen.CONVERT(output_row.empno)||'</ENUM>');
    fnd_file.put_line(fnd_file.output,'<ENUM>'||dbms_xmlgen.CONVERT(output_row.empno)||'</ENUM>');
    --
    dbms_output.put_line('<ENAME>'||dbms_xmlgen.CONVERT(output_row.ename )||'</ENAME>');
    fnd_file.put_line(fnd_file.output,'<ENAME>'||dbms_xmlgen.CONVERT(output_row.ename )||'</ENAME>');
    --
    dbms_output.put_line('<JOB>'||dbms_xmlgen.CONVERT(output_row.job )||'</JOB>');
    fnd_file.put_line(fnd_file.output,'<JOB>'||dbms_xmlgen.CONVERT(output_row.job )||'</JOB>');
    --
    dbms_output.put_line('<HIRE_DATE>'||dbms_xmlgen.CONVERT(output_row.hiredate )||'</HIRE_DATE>');
    fnd_file.put_line(fnd_file.output,'<HIRE_DATE>'||dbms_xmlgen.CONVERT(output_row.hiredate )||'</HIRE_DATE>');
    --
    dbms_output.put_line('<SAL>'||dbms_xmlgen.CONVERT(output_row.sal )||'</SAL>');
    fnd_file.put_line(fnd_file.output,'<SAL>'||dbms_xmlgen.CONVERT(output_row.sal )||'</SAL>');
    --
    dbms_output.put_line('</ROW>');
    fnd_file.put_line(fnd_file.output,'</ROW>');
    --
  END LOOP;
  CLOSE data_cur;
  --
  dbms_output.put_line('</OUTPUT>');
  fnd_file.put_line(fnd_file.output,'</OUTPUT>');
  --
END;
/

below is the generated output in XML tags

<?xml version="1.0" encoding="US-ASCII" standalone="no"?>
<OUTPUT>
 <ROW>
  <ENUM>007369</ENUM>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <HIRE_DATE>17-12-80</HIRE_DATE>
  <SAL>800</SAL>
 </ROW>
 <ROW>
  <ENUM>007499</ENUM>
  <ENAME>ALLEN</ENAME>
  <JOB>SALESMAN</JOB>
  <HIRE_DATE>20-02-81</HIRE_DATE>
  <SAL>1600</SAL>
 </ROW>
 <ROW>
  <ENUM>007521</ENUM>
  <ENAME>WARD</ENAME>
  <JOB>SALESMAN</JOB>
  <HIRE_DATE>22-02-81</HIRE_DATE>
  <SAL>1250</SAL>
 </ROW>
 <ROW>
  <ENUM>7566</ENUM>
  <ENAME>JONES</ENAME>
  <JOB>MANAGER</JOB>
  <HIRE_DATE>02-04-81</HIRE_DATE>
  <SAL>2975</SAL>
 </ROW>
</OUTPUT>

Below is the screenshot of the output layout (XMLP Template)
Emp_RPT_Layout
Below is the screen shot of output generated
Emp Report Output

You might have observed that the xml content has employee number with 00 as prefix but the excel output is not showing the 00 prefix, to know why and how to resolve this, see this post.

WOW! Did you like this post? We'll send more interesting posts like PL/SQL Script to Generate XML Tags for XMLP Report to you!
Enter your Email Address:
  • Sukesh Kotian

    Never thought this was possible. Keep up the good work 🙂

  • Narendra Chowdary

    Thanks for shared details.