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

Code:

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>