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('');
  fnd_file.put_line(fnd_file.output,'');
  dbms_output.put_line('
');
  fnd_file.put_line(fnd_file.output,'
');
  --
  OPEN data_cur;
  LOOP
    --
    FETCH data_cur INTO output_row;
    EXIT
  WHEN data_cur%notfound;
    --
    dbms_output.put_line('');
    fnd_file.put_line(fnd_file.output,'');
    --
    dbms_output.put_line(''||dbms_xmlgen.convert(output_row.empno)||'');
    fnd_file.put_line(fnd_file.output,''||dbms_xmlgen.convert(output_row.empno)||'');
    --
    dbms_output.put_line(''||dbms_xmlgen.convert(output_row.ename )||'');
    fnd_file.put_line(fnd_file.output,''||dbms_xmlgen.convert(output_row.ename )||'');
    --
    dbms_output.put_line(''||dbms_xmlgen.convert(output_row.job )||'');
    fnd_file.put_line(fnd_file.output,''||dbms_xmlgen.convert(output_row.job )||'');
    --
    dbms_output.put_line(''||dbms_xmlgen.convert(output_row.hiredate )||'');
    fnd_file.put_line(fnd_file.output,''||dbms_xmlgen.convert(output_row.hiredate )||'');
    --
    dbms_output.put_line(''||dbms_xmlgen.convert(output_row.sal )||'');
    fnd_file.put_line(fnd_file.output,''||dbms_xmlgen.convert(output_row.sal )||'');
    --
    dbms_output.put_line('');
    fnd_file.put_line(fnd_file.output,'');
    --
  END LOOP;
  CLOSE data_cur;
  --
  dbms_output.put_line('
');
  fnd_file.put_line(fnd_file.output,'
');
  --
END;
/

below is the generated output in XML tags

 
  007369
  SMITH
  CLERK
  17-12-80
  800
 
 
  007499
  ALLEN
  SALESMAN
  20-02-81
  1600
 
 
  007521
  WARD
  SALESMAN
  22-02-81
  1250
 
 
  7566
  JONES
  MANAGER
  02-04-81
  2975
 

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.