I want to know how to convert table data into xml separate file. Whether I need to use UTL file concepts?. Below is the sample code. can any one help me to clear on this?
CREATE OR PROCEDURE PRC_INSDB_XML (Booking_date1 in date)
as
begin
SELECT XMLElement("Employee", XMLForest(empno, ename, job, mgr, hiredate, sal, comm, deptno, gender)) AS "RESULT" FROM emp where Booking_date=Booking_date1 ;
end PRC_INSDB_XML;
/
You can generate XML using dbms_xmlgen.getxml() function.Find below link for details:
https://docs.oracle.com/cd/B10501_01/appdev.920/a96620/xdb12gen.htm#1025729
select dbms_xmlgen.getxml( 'select * from emp') as emp_xml
from dual;
However to create an XML on your disk you need UTL package. Eg.
DECLARE
F UTL_FILE.FILE_TYPE;
MYCLOB CLOB;
BEGIN
SELECT
DBMS_XMLGEN.GETXML('
select deptno,dname,loc from emp
')
INTO MYCLOB
FROM DUAL;
F := UTL_FILE.FOPEN(dest_path,'EMP.XML','w',32767);
UTL_FILE.PUT(F,MYCLOB);
UTL_FILE.FCLOSE(F);
END;
If you need it on your client machine then try spooling in Sql*plus
SET HEADING OFF
SET PAGESIZE 0
SET LONG 90000
SET FEEDBACK OFF
SET ECHO OFF
SPOOL c:\downloads\file_name.xml
select dbms_xmlgen.getxml( 'select * from emp') as emp_xml
from dual;
SPOOL OFF
