Oracle Database 8: Export Table to XML

Question

Yes, I know ... we have to upgrade to a recent Oracle DB Version. :-)

But until then: Did release 8.0.5.0.0 support any kind of XML-Export at all - beside manually spooling something like ...

select '<?xml ...' from dual;

As far as I know, the first relevant XML features (XMLType) were introduced in Oracle 9i (source for example: http://web.stanford.edu/dept/itss/docs/oracle/9i/appdev.920/a96620/whatsnew.htm)?

No, "proper" XML support started with Oracle 10. – Wernfried Domscheit Mar 24 '15 at 14:09 — Wernfried Domscheit, Mar 24 '15 at 14:09

score 0 · Answer 1 · answered Mar 25 '15 at 20:31

I don't have a copy of 8.0.5.0.0 so haven't been able to test these but thought they may be of some help.

Oracle's SQL Developer IDE gives you a couple of different ways to export queries as XML.

The first is to add the following comment to your SQL query.

select /*xml*/ 
       *
  from hr.employees

Pressing F5 to run as a script gives the following output:

<?xml version='1.0'  encoding='UTF8' ?>
<RESULTS>
    <ROW>
        <COLUMN NAME="EMPLOYEE_ID"><![CDATA[100]]></COLUMN>
        <COLUMN NAME="FIRST_NAME"><![CDATA[Steven]]></COLUMN>
        <COLUMN NAME="LAST_NAME"><![CDATA[King]]></COLUMN>
 ....

It is worth pointing out that the /*xml*/ is a feature of the SQL Developer IDE.

The second method is to execute your query within SQL Developer as normal and then right click on the results grid and select export and choose XML.

enter image description here

I actually use SQL Developer for working with the 12c development DB. That combination definitely does not give me any headache ;-) But unfortunately there does not seem to be a way of connecting with SQL Developer to a 8.0.5.0.0 DB :-/ — tschlein, Mar 26 '15 at 09:24

score 0 · Answer 2 · edited May 23 '17 at 10:24

So, to answer myself after doing some experiments:

Option 1: Spooling via SQL*Plus (yes, quite ugly)
Option 2: Using PL/SQL to print out the XML (copy/paste to final file, not that much better)
Option 3: Connecting via Java/JDBC to the DB, getting the data, and create the XML on the Java-side. Problem boils down on how to connect with a recent Java/JDBC to an old 8.0.5.0.0. DB. But that can be handled: Using Multiple Oracle JDBC drivers in one Java application?

Maybe Option 2.5 could be using UTL_FILE on the PL/SQL side, but with Option 3 I might be more happier.

Oracle Database 8: Export Table to XML

2 Answers2