PL/SQL error receieved on making of an XML file

oracle11g, plsql, plsqldeveloper, sql, xml

Solution

First off, you are trying to write to a file on the database server, right? Not a file on your client machine? `c:\xml` seems like a directory that might exist on your client machine, not on the server.

The `DBMS_XMLDOM` documentation states

Note: Before database startup, the read-from and write-to directories in the init.ORA file must be specified; for example: UTL_FILE_DIR=/mypath/insidemypath.

Read-from and write-to files must be on the server file system.

It seems that `DBMS_XMLDOM` doesn't support directory objects. It still seems to rely on the old `UTL_FILE_DIR` parameter.

Personally, I would probably convert the XMLType to a CLOB with the `getClobVal()` method and then use the `clob2file` procedure in the `DBMS_XSLPROCESSOR` package to write it out

DBMS_XSLProcessor.Clob2File( 
  xdata.getClobVal(),
  'TEMP_DIR',
  'myXML1.xml' );

which works on my system

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    xdata  XMLTYPE;
  3    CURSOR xmlcur IS
  4    SELECT xmlelement("Employee",
  5                      XMLAttributes('http://www.w3.org/2001/XMLSchema' AS "xmlns:xsi",
  6                                    'http://www.oracle.com/Employee.xsd' AS "xsi:nonamespaceSchemaLocation")
  7                     ,xmlelement("EmployeeNumber",e.employee_id)
  8                     ,xmlelement("EmployeeName",e.first_name)
  9                     ,xmlelement("Department",
 10                                 xmlelement("DepartmentName",d.department_name)
 11                                ,xmlelement("Location",d.location_id)
 12                                )
 13                      )
 14    FROM   employees e
 15          ,departments d
 16    WHERE  e.department_id=d.department_id;
 17  BEGIN
 18    OPEN xmlcur;
 19    FETCH xmlcur INTO xdata;
 20    CLOSE xmlcur;
 21    DBMS_XSLProcessor.Clob2File(
 22        xdata.getClobVal(),
 23        'TEMP_DIR',
 24        'myXML1.xml' );
 25*    END;
SQL> /

PL/SQL procedure successfully completed.

If you actually want to fetch all the data from the cursor rather than just the first row, you'll need a loop

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    xdata  XMLTYPE;
  3    CURSOR xmlcur IS
  4    SELECT xmlelement("Employee",
  5                      XMLAttributes('http://www.w3.org/2001/XMLSchema' AS "xmlns:xsi",
  6                                    'http://www.oracle.com/Employee.xsd' AS "xsi:nonamespaceSchemaLocation")
  7                     ,xmlelement("EmployeeNumber",e.employee_id)
  8                     ,xmlelement("EmployeeName",e.first_name)
  9                     ,xmlelement("Department",
 10                                 xmlelement("DepartmentName",d.department_name)
 11                                ,xmlelement("Location",d.location_id)
 12                                )
 13                      )
 14    FROM   employees e
 15          ,departments d
 16    WHERE  e.department_id=d.department_id;
 17    l_clob clob;
 18  BEGIN
 19    dbms_lob.createtemporary( l_clob, true );
 20    OPEN xmlcur;
 21    LOOP
 22      FETCH xmlcur INTO xdata;
 23      EXIT WHEN xmlcur%notfound;
 24      l_clob := l_clob || xdata.getClobVal();
 25    END LOOP;
 26    DBMS_XSLProcessor.Clob2File(
 27      l_clob,
 28      'TEMP_DIR',
 29      'myXML1.xml' );
 30    CLOSE xmlcur;
 31* END;
SQL> /

PL/SQL procedure successfully completed.

Problem

Original source