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
novalex
Messages: 19
Registered: November 2007
Junior Member
Hi!

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 || ' ' ||
                    v_my_where_str;
      
      /* "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. */
      dbms_sql.close_cursor(v_sql_stat_cur);
      
      /* Open the file for creating the xml. */
      v_xml_file := utl_file.fopen(par_path, par_file_name, 'A');
...


Thanks for helping

Alex
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: 4502
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
hobbes
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.

SQL> SELECT SYS_XMLGen
  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;

DATA
-----------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
  <DEPTNO/>
  <COMM/>
  <SAL/>
  <HIREDATE/>
  <MGR/>
  <JOB/>
  <ENAME/>
  <EMPNO/>
</ROW>
</ROWSET>
Previous Topic: DISTINCT in XMLQuery ??
Next Topic: Load Java
Goto Forum:
  


Current Time: Sat Dec 10 16:50:07 CST 2016

Total time taken to generate the page: 0.07439 seconds