PL/SQL Script to Generate XML Tags for XMLP Report

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)

Below is the screen shot of output generated

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.

PL/SQL Script to Generate XML Tags for XMLP Report

For Further Reading