Creating XML data from a relational table

Blog Index

 

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>