How does one transform data in a table to XML
format? Just as one can select and format into
HTML code, and this is what takes place behind
the scene in iSQL*Plus, one can select from a
table and have the output be well-formed XML.
Use the DBMS_XMLGEN.GETXML
function to accomplish this.
The syntax is select DBMS_XMLGEN.GETXML(‘your
query here’) from dual and with
spool and SQL*Plus settings set correctly, the
output is a dump of data in XML format. Where is
Anywhere or anytime one needs to transform data
into XML format, the GETXML function can be
used. Of particular note, Oracle’s new reporting
tool Business Intelligence Publisher is
intimately tied to XML. In fact, report or
template development is largely driven by having
an XML file representation of data to start
with. Dump a portion of the data into XML
format, load the XML data into an RTF document
in Word, call the table wizard, and one has a
report template just like that.
Of course, much more can take place with respect
to manipulating the data. Oracle recommends that
data selection and formatting, as much as
possible, be done via the SELECT statement as
opposed to forcing the RTF processing engine to
manipulate the data. The RDBMS engine is
obviously much more powerful than what Microsoft
Word has to offer.
A select statement like so…
…yields the following output:
The ROWSET and ROW tags can be
set via other procedures within this package.
recommends that DBMS_XMLGEN be used over
This package can be used to upload XML data into
a table. It requires a good bit of manual typing
as each tag has to be quoted and concatenated.
Here is an example of uploading a new record
into the EMP table.
-- get the context handle
dbms_xmlsave.insertxml (insctx, s_xml);
-- this inserts the
A bit cumbersome, but it can be done. There must
be an easier way.
This package allows uploading an XML file
directly and inserting the contents into a
table. In this example, table EMP3 is a copy of
EMP. The generated XML data file is named
emp3.xml and is located in a directory object
named MYDIR - C:\Temp in this example.
Here is the procedure code to upload an
CREATE OR REPLACE
-- handle to the XML file
on the OS
xmlfile := Bfilename(UPPER(dirname),filename);
-- open file
-- copy contents of file
into empty clob
-- context handle
-- this inserts the file
|| ' rows inserted');
-- close handle
The process to upload a file is to execute the
procedure and pass in the directory object name,
the file name, and the target table.
These three XML-related packages and code
examples should enable one to handle basic XML
file or data operations. Querying XML via XPath
Query is beyond the scope of this book. However,
being able to quickly generate and upload XML
data is important, and as can be seen, the
process is not that complicated.
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from