Xml- oracle 9i

Aquí mle envioalgunos ejemplos esperando que sean de utilidad:
select xmlgen.getxml('select employee_id,first_name,salary from employees')
xml from dual;
Otro método es el siguiente:
select xmlelement("Employees",
2 xmlagg(
3 xmlelement(
4 "Employee",
5 xmlforest(empno, ename, sal)))) as "result"
6 from emp
7 /
otra alternativa es:
SELECT dbms_xmlquery.getXML('SELECT * FROM emp') FROM dual;
Para xml anidados use:
SELECT XMLELEMENT("Emp", XMLELEMENT("name", e.fname ||' '|| e.lname),
XMLELEMENT ( "hiredate", e.hire)) AS "result"
FROM employees e
WHERE employee_id > 200 ;
Para generar un elemento por cada empleado con su respectivo ID
SELECT XMLELEMENT ( "Emp",
XMLATTRIBUTES (e.id,e.fname ||' ' || e.lname AS "name")) AS "result"
FROM employees e
WHERE employee_id > 200;
ARA

Por lo general, para lograr tener un xml de salida, se debe contar con un template (plantilla) que permita dar el formato del output, esto es un xsl.
Te adjunto un ejemplo, sacado de Oracle, no como soporte, sino de ayuda educacional.
Espero te sirva, sino me comentas.
Saludos,
CaiM.
==
HOW TO OUTPUT XML FRAGMENTS VIA THE XSQL SERVLET
-------------------------------------------------
It is possible that when developing an XML based application you may want to
store XML gragments in database columns and then output this information
via a query from the xsql servlet.
Consider a table with the following structure
XMLFRAG
--------
EMPNO Number
EMPDATA VARCHAR2(250)
Containing data of the form
Empno empdata
----- ---------------------------------
7654 <ENAME>JONES</ENAME><JOB>SALESMAN</JOB>
If you issue the query:
SELECT EMPNO,EMPDATA via the xsql servlet, what you get returned is
<ROWSET>
<ROW num="1">
<EMPNO>7654</EMPNO>
<EMPDATA><ENAME>SMITH</ENAME><JOB>CLERK</JOB></EMPDATA>
</ROW
</ROWSET>
This is because, the XSQL servlet escapes all instances of the less than sign.
This is desirable in most cases since you normally would not want your data
to corrupt the XML being output. In this case however, you want the
information in the column to be part of the xml structure.
In version 9.0.2 of the XSQL servlet this will be possible via a new xsl:included-xml
tag, but up until then you have to pass the output through an XSLT template
to switch the escaped characters back using disable-output-escaping="yes".
The following files demonstrate this technique:
The stylesheet shown demonstrates 2 methods of acheiving this, one which
can be applied to any query and one more specific to the column containing the
XML fragment.
-----------Cretab.sql - create some sample data ---------------
drop table xmlfrag;
Create table xmlfrag (
Empno number,
empdata varchar2(250)
);
insert into xmlfrag values (7654,'<ENAME>SMITH</ENAME><JOB>CLERK</JOB>');
insert into xmlfrag values (7666,'<ENAME>JONES</ENAME><JOB>SALESMAN</JOB>');
commit;
---------- end ------------
---------- xmlfrag.xsql - .xsql page used to extract the data
<?xml version="1.0" encoding='ISO-8859-1'?>
<?xml-stylesheet type="text/xsl" href="xmlfrag.xsl" target="new" ?>
<page xmlns:xsql="urn:oracle-xsql" connection="demo">
<xsql:query>
select empno,empdata from xmlfrag
</xsql:query>
</page>
-------- end ------------
---------- xmlfrag.xsl - .xsl stylesheet which converts < back to <
<?xml version="1.0" encoding='ISO-8859-1'?>
<!-- Example stylesheet to make sure that xml fragments extracted
from a database column via the XSQL servlet are output correclty
-->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output encoding='ISO-8859-1'/>
<!-- Following line matches all nodes and attributes -->
<xsl:template match="@*|node()">
<!-- Copy the current value and apply templates to all nodes below -->
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
<!-- Following lines will apply the principle to all text values
returned from the query - so that all < signs in database columns
will actually be returned as '<' instead of <
-->
<!-- uncomment to use this!
<xsl:template match="text()">
<xsl:value-of select="." disable-output-escaping="yes"/>
</xsl:template>
-->
<!-- Following lines cause the principle to only be applied to the
column specified in this case EMPDATA
in case other column's data does need to be escaped
note that xsl:copy is used so that tag actually appears
-->
<xsl:template match="EMPDATA">
<xsl:copy>
<xsl:value-of select="." disable-output-escaping="yes"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
------------- end -----------------
------- sample output -------------
<?xml version="1.0" encoding="ISO-8859-1" ?>
<page xmlns:xsql="urn:oracle-xsql">
<ROWSET>
<ROW num="1">
<EMPNO>7654</EMPNO>
<EMPDATA>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
</EMPDATA>
</ROW>
<ROW num="2">
<EMPNO>7666</EMPNO>
<EMPDATA>
<ENAME>JONES</ENAME>
<JOB>SALESMAN</JOB>
</EMPDATA>
</ROW>
</ROWSET>
</page>

Hay muchos artículos con ejemplos. Sobre todo en las OTN de Oracle.com.
De todas formas te mando una URL donde encontraras ejemplos :
http://www.oracle-base.com/articles/9i/SQLXML9i.php
Piensa que el Oracle developed KIt lo tienes que tener instalado.
Ya veras como con una simple 'select' te genera la información en formato XML.