Generating formatted XML From Oracle

SQL> SELECT EMP_ID, FIRSTNAME, LASTNAME, PHONENUMBER FROM EMP WHERE ROWNUM <= 5

To transform this Oracle output into properly formatted XML.  All we do is change the SQL to embed the requested columns into a call to the dbms_xmlgen.getxml procedure:

set pages 0

set linesize 150

set long 9999999

set head off

SQL> select dbms_xmlgen.getxml('select EMP_ID, FIRSTNAME, LASTNAME, PHONENUMBER from employees where rownum < 6') xml from dual

OUTPUT

=======================

<?xml version="1.0"?>

<ROWSET>

 <ROW>

  <EMP_ID>100</EMP_ID>

  <FIRSTNAME>Steven</FIRSTNAME>

  <LASTNAME>King</LASTNAME>

  <PHONENUMBER>515.123.4567</PHONENUMBER>

 </ROW>

 <ROW>

  <EMP_ID>101</EMP_ID>

  <FIRSTNAME>Neena</FIRSTNAME>

  <LASTNAME>Kochhar</LASTNAME>

  <PHONENUMBER>515.123.4568</PHONENUMBER>

 </ROW>

 <ROW>

  <EMP_ID>102</EMP_ID>

  <FIRSTNAME>Lex</FIRSTNAME>

  <LASTNAME>De Haan</LASTNAME>

  <PHONENUMBER>515.123.4569</PHONENUMBER>

 </ROW>

 <ROW>

  <EMP_ID>103</EMP_ID>

  <FIRSTNAME>Alexander</FIRSTNAME>

  <LASTNAME>Hunold</LASTNAME>

  <PHONENUMBER>590.423.4567</PHONENUMBER>

 </ROW>

 <ROW>

  <EMP_ID>104</EMP_ID>

  <FIRSTNAME>Bruce</FIRSTNAME>

  <LASTNAME>Ernst</LASTNAME>

  <PHONENUMBER>590.423.4568</PHONENUMBER>

 </ROW>

</ROWSET>

XML can be easily integrated into any application, with ROWSET and ROW tags in place to identify nodes, and tags for each column you pulled out of the database. 

read DBMS_XMLGEN