CREATE OR REPLACE PROCEDURE table_to_xml_out
( sdate varchar2,file_dir varchar2,file_name varchar2)

IS
begin
declare
g_xml clob;
l_ctx dbms_xmlquery.ctxtype;
BEGIN

l_ctx := dbms_xmlquery.newcontext('SELECT empno from emp emp_date = :m_date ');
--bind variable
DBMS_XMLQuery.setBindValue(l_ctx,'m_date',to_date(sdate,'mm/dd/yyyy'));
--gets all the data into clob
g_xml := DBMS_XMLQuery.getXML(l_ctx);
dbms_xmlquery.closecontext(l_ctx);
--call another procedure to write the clob into a file in batches
clob_to_file(file_dir,file_name||'.xml',g_xml);
END;

END table_to_xml_out;

create or replace procedure clob_to_file( p_dir in varchar2,
p_file in varchar2,
p_clob in clob )
is
l_output utl_file.file_type;
l_amt number default 32000;
l_offset number default 1;
l_length number default
nvl(dbms_lob.getlength(p_clob),0); --gets the total length of the clob
BEGIN
l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
--this loop passes 32000 bytes from the clob till the end.
while ( l_offset < l_length )
loop
utl_file.put(l_output,
dbms_lob.substr(p_clob,l_amt,l_offset) );
--dbms_lob.read(file,amt, pos, buf);
utl_file.fflush(l_output);
l_offset := l_offset + l_amt;
end loop;
utl_file.new_line(l_output);
utl_file.fclose(l_output);
end;

SQL> exec table_to_xml_out('09/24/2008','c:\test\','testout')
BEGIN table_to_xml_out('09/24/2008','c:\test\','testout'); END;

*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "SCOTT.CLOB_TO_FILE", line 11
ORA-06512: at "SCOTT.TABLE_TO_XML_OUT", line 18
ORA-06512: at line 1

SQL> select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;
DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>
      <HIREDATE>12/17/1980 0:0:0</HIREDATE>
      <SAL>800</SAL>
      <DEPTNO>20</DEPTNO>
   </ROW>
   <ROW num="2">
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/20/1981 0:0:0</HIREDATE>
      <SAL>1600</SAL>
      <COMM>300</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
   <ROW num="3">
      <EMPNO>7521</EMPNO>
      <ENAME>WARD</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/22/1981 0:0:0</HIREDATE>
      <SAL>1250</SAL>
      <COMM>500</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
</ROWSET>

set termouf off

SQL>select * From emp ;

     EMPNO ENAME      JOB               MGR HIREDATE
---------- ---------- ---------- ---------- ---------
      7369 SMITH      CLERK            7902 17-DEC-80
      7499 ALLEN      SALESMAN         7698 20-FEB-81
      7521 WARD       SALESMAN         7698 22-FEB-81
      7566 JONES      MANAGER          7839 02-APR-81
      7654 MARTIN     SALESMAN         7698 28-SEP-81
      7698 BLAKE      MANAGER          7839 01-MAY-81
      7782 CLARK      MANAGER          7839 09-JUN-81
      7788 SCOTT      ANALYST          7566 19-APR-87
      7839 KING       PRESIDENT             17-NOV-81
      7844 TURNER     SALESMAN         7698 08-SEP-81
      7876 ADAMS      CLERK            7788 23-MAY-87
      7900 JAMES      CLERK            7698 03-DEC-81
      7902 FORD       ANALYST          7566 03-DEC-81
      7934 MILLER     CLERK            7782 23-JAN-82

14 rows selected.

SQL>select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;

DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMPNO>7369</EMPNO>



SQL>

set long 10000