How to generate XML report through PL/SQL Code

First Create procedure or package in Database.

See below sample code

Create or replace procedure xx_test_pro_rep(errfbuff out varchar2,retcode out varchar2)
IS
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 xx_test_pro_rep;

After that create executable and Concurrent program.

In concurrent program output format is “XML”.

Add CP to responsibility and run program from SRS window.

See output file of that program.

Thanks

Sajal Agarwal