Create XML file using PL/SQL

To create a xml file, we need to create a directory and grant the read write permission to the specified USER as

C:\>sqlplus sys/sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 29 15:29:12 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> create or replace directory myxml as 'E:\myxml\';
Directory created.

SQL> grant read, write on directory myxml to scott;
Grant succeeded.

NOTE: "E:\myxml\" has to be physical location on disk.

After creating the directory and granting the permissions to the SCOTT user, following PL/SQL code needs to be executed by the SCOTT user to create XML file.

C:\>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 29 15:32:06 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> DECLARE
 2      F UTL_FILE.FILE_TYPE;
 3      MYCLOB CLOB;
 4  BEGIN
 5      SELECT
 6        DBMS_XMLGEN.GETXML('
 7          SELECT
 8            EMPNO, ENAME, SAL, DNAME
 9          FROM
10            SCOTT.EMP E,
11            SCOTT.DEPT D
12          WHERE
13            D.DEPTNO=E.DEPTNO
14        ')
15      INTO MYCLOB
16      FROM DUAL;
17
18      F := UTL_FILE.FOPEN('MYXML','EMP_DEPT.XML','w',32767);
19      UTL_FILE.PUT(F,MYCLOB);
20      UTL_FILE.FCLOSE(F);
21  END;
22  /

PL/SQL procedure successfully completed.

After the execution of above procedure, a file (EMP_DEPT.XML) would have been created at "E:\myxml\" location.

Related Links:
- Generate XML data using SQL in Oracle Database
- Convert XML to Rows and Columns in Oracle
- Create CSV file using PL/SQL
- Load CSV file in Oracle using PL/SQL
- Create External Table from CSV File
- Load XML File in Oracle Table
- Generate XML Output from SQL