Home » Developer & Programmer » JDeveloper, Java & XML » How to export an empty table to XML file
How to export an empty table to XML file [message #292905] Thu, 10 January 2008 02:45 Go to next message
Messages: 19
Registered: November 2007
Junior Member

I wrote a procedure that exports a table to a XML file. But When the table is empty the XML file is empty as well.
But how can I export an empty table to XML. The XML file would just have the column names without any content.

Here is an extract of my procedure:
v_sql_stat := 'SELECT * FROM '|| 
                    v_tab_own_upp || '.' || 
                    v_tab_name_upp || ' ' ||
      /* "Activate" the generator. */
      v_ctx := dbms_xmlgen.newContext(v_sql_stat);
      /* Create the XML code. */
      v_xml := dbms_xmlgen.getXML(v_ctx);
      /* Closing the cursor. */
      /* Open the file for creating the xml. */
      v_xml_file := utl_file.fopen(par_path, par_file_name, 'A');

Thanks for helping

Re: How to export an empty table to XML file [message #292976 is a reply to message #292905] Thu, 10 January 2008 06:22 Go to previous messageGo to next message
Frank Naude
Messages: 4516
Registered: April 1998
Senior Member
You can always test if v_xml is empty. If so, write out the headers yourself (utl_file.put_line('<MY-XML-HEAD....>').
Re: How to export an empty table to XML file [message #293173 is a reply to message #292905] Fri, 11 January 2008 01:38 Go to previous message
Messages: 173
Registered: January 2006
Senior Member
Check if dbms_xmlgen.getNumRowsProcessed() is 0. If yes, you could use a query like the one below - this generates XML for the empty table in Oracle's canonical format.

  2            (SYS_XMLGen(XMLAgg(XMLType('<'||column_name||'/>')))
  3              , XMLFormat.createformat('ROWSET')).getClobVal() data
  4  FROM   all_tab_cols
  5  WHERE  table_name = 'EMP' 
  6  ORDER BY column_id;

<?xml version="1.0"?>
Previous Topic: XMLSequence and attribute condition
Next Topic: PL/SQL Cursor output as XML
Goto Forum:

Current Time: Sun Aug 20 16:11:38 CDT 2017

Total time taken to generate the page: 0.33547 seconds