I'm adding a lesson to my classes for "Using XML as a Physical Data Source", and wanted to create an XML file from an existing relational table. The Oracle documentation shows us several different methods. Here is a script that worked for me. Save the following commands as a script and run in SQL*Plus.
--Suppress output to the command window
set termout off;
--Suppress the # of rows returned message
set
feedback off;
--Suppress the display of the column heading row
set heading off;
--Suppress paging
set pagesize 0;
--Set maximum line width for CLOB datatype (the result of the XMLForest function)
set long 200;
--Set default line width to 200
set linesize 200;
--Set RESULTS column to 200 characters wide
column results format a200;
--Open output file
spool c:\ClassAgg.xml;
select '<Table Name="ClassAgg.xml">' from dual;
select xmlelement("ROW", XMLForest(channel, region, division, month, cost, sales, units)) as results
from agg_reg_div_mv;
--Close output file
spool off;
--Reenable output to the command window
set termout on;
It's a bit wordy, of course, because all of those SET commands could be combined. But the important part of the syntax is the use of the select xmlelement ... xmlforest command.
The columns in the agg_reg_div_mv table are channel, region, and so forth. I've chosen to use the actual column names as the XML tags, but you could use alias names just as you would in an ordinary select statement. For example:
xmlforest(channel "chan", region "reg"....)
The first three rows of output look like this:
<Table Name="ClassAgg.xml">
<ROW><CHANNEL>Web</CHANNEL><REGION>East</REGION><DIVISION>Mens Apparel</DIVISION><MONTH>Aug-2008</MONTH><COST>36072.32</COST><SALES>43218.35</SALES><UNITS>2858</UNITS></ROW>
<ROW><CHANNEL>Retail</CHANNEL><REGION>West</REGION><DIVISION>Childrens Apparel</DIVISION><MONTH>Aug-2008</MONTH><COST>39950.54</COST><SALES>50192.62</SALES><UNITS>252</UNITS></ROW>
