I asked Sean Dillon, our local XML guy to take a look at this. He says...
If you haven't already, you need to download and install the latest Oracle XML Developer's Kits (XDK's). What you are trying to do is relatively simple, and with the XDK, you can generate XML from SQL queries using PL/SQL, Java, C or C++. The following command on the command line generates an XML document containing the first 4 rows of the SCOTT.EMP table:
$ java OracleXML getXML -user "scott/tiger" "select * from emp where rownum < 5" > emp.xml
$ cat emp.xml
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>12/17/1980 0:0:0</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="2">
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>2/20/1981 0:0:0</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="3">
<EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>2/22/1981 0:0:0</HIREDATE>
<SAL>1250</SAL>
<COMM>500</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="4">
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>4/2/1981 0:0:0</HIREDATE>
<SAL>2975</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
</ROWSET>
There are a variety of options with this Java class (OracleXML) as well...
$ java OracleXML
OracleXML-Error:wrong argument list
Call with
OracleXML getXML
[-user "username/password"] -- the user name and password
[-conn "JDBC_connect_string"] -- JDBC connect string
[-withDTD | -withSchema] -- generate the DTD/Schema
[-rowsetTag <rowsetTag>] -- document tag name
[-rowTag <rowTag>] -- row element tag name
[-rowIdAttr <attrName>] -- row-id attribute name
[-rowIdColumn <column_name>] -- db-column to use for the row id
[-collectionIdAttr <attrName>] -- collection element-id attribute
[-useTypeForCollElemTag] -- use type name for coll-elem tag
[-useNullAttrId] -- use a null attribute
[-styleSheet <URI>] -- stylesheet processing instruction header
[-styleSheetType <type>] -- stylesheet header type (e.g.text/xsl)
[-setXSLT <URI>] -- XSLT to apply to XML doc
[-setXSLTRef <URI>] -- XSLT external entity reference
[-useLowerCase| -useUpperCase] -- the case of the tag names
[-withEscaping] -- if necessary do SQL-XML name escaping
[-errorTag <errorTagName>] -- error tag name
[-raiseException] -- raise exceptions for errors
[-raiseNoRowsException] -- raise exception if no returned
[-maxRows <maxRows>] -- maximum rows in output
[-skipRows <skipRows>] -- rows to skip in output
[-encoding <encoding_name>] -- encoding to be used
[-dateFormat <date format>] -- date format to be used
(<query>| -fileName <sqlfile>) -- SQL query | file containing the query
-- OR --
OracleXML putXML
[-user "username/password"] -- the user name and password
[-conn "JDBC_connect_string"] -- JDBC connect string
[-batchSize <size>] -- number of inserts executed at a time
[-commitBatch <size>] -- number of inserts commited at a time
[-rowTag <rowTagName>] -- the name for row elements
[-dateFormat <format>] -- the format of date elements
[-withEscaping] -- if necessary do SQL-XML name escaping
[-ignoreCase] -- ignore the case of the tag namess
[-setXSLT <URI>] -- XSLT to apply to XML doc
[-setXSLTRef <URI>] -- external entity reference for XSLT doc
[-fileName fileName | -- the XML document file name or
-URL url | - URL or
-xmlDoc <XMLDocumentString>] - XML string
<tableName> -- the table name to put into
This is Java, however, and it might not fit your needs at all. Alternatively, you could use the PL/SQL equivalent functionality to generate XML from a SQL query using the package DBMS_XMLQUERY:
SQL> var my_xml clob
SQL> set autoprint on
SQL> declare
2 -- l_clob clob;
3 l_ctx dbms_xmlgen.ctxHandle;
4 l_sql varchar2(400);
5 begin
6 l_sql := 'select *
7 from emp
8 where rownum < 5';
9
10 l_ctx := dbms_xmlgen.newContext(l_sql);
11 :my_xml := dbms_xmlgen.getXml(l_ctx); -- or you could assign it to l_clob
12 end;
13 /
PL/SQL procedure successfully completed.
MY_XML
----------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>17-DEC-80</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>20-FEB-81</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>22-FEB-81</HIREDATE>
<SAL>1250</SAL>
<COMM>500</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>02-APR-81</HIREDATE>
<SAL>2975</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
</ROWSET>
SQL> set autoprint off
The DBMS_XMLQUERY package has a GETXML function that takes in a SQL query and returns a CLOB without having to create a context and go into the overhead, but I recommend using the context handle in the PL/SQL block; it gives you an enormous amount of functionality (most of which is absolutely necessary in a production application) such as binding variables to your query, generating a DTD or Schema for your XML, modifying the names of the ROWSET and ROW elements, limiting the number of rows returned, etc.
You can find the latest copies of the XML Developer's Kits on the Oracle Technology Network's XML pages...
</code>
http://technet.oracle.com/tech/xml/content.html <code>
Hope that helps!