<html>
<!--AD_DND-->
<!--xml.html,v 3.0 2000/02/06 03:27:31 ron Exp-->
<head>
<title>Working with XML Data in ArsDigita Community System</title>
</head>
<body bgcolor=white>
<h2>Working with XML</h2>
in the <a href="index.html">ArsDigita Community System</a>
by <a href="mailto:karlg@arsdigita.com">Karl Goldstein</a>
<hr>
Perspective: the ArsDigita philosophy is not to put stuff into our
toolkit that is part of the core Oracle RDBMS. Oracle Corporation has
thousands of programmers, their most reliable product is the core RDBMS
server, they have documentation, training, support, etc. Oracle has
wonderful support for XML built into their core RDBMS from version 8.1.5
forward. This support is available to any Oracle client, including
an AOLserver running the ArsDigita Community System. The XML support is
made possible by the fact that the core Oracle server is capable of
running programs in procedural languages such as Java and PL/SQL.
<p>
Basically what we do for a site that needs to make heavy use of XML is
to download the appropriate Java libraries for Oracle's built-in Java
VM. After that it is just a question of how to use those libraries:
<ul>
<li><b>I want to grab some data from an Oracle table and publish it
in XML:</b> run the <code>xmlgen.getXML</code> procedure inside Oracle,
which will automatically figure out how to go from the Oracle data
dictionary into XML; AOLserver gets a big string of XML back from Oracle
<li><b>I want to grab some data from a server elsewhere on the Internet
and stuff it into an Oracle table:</b> Take the entire XML document and
feed it to Oracle, then run <code>xmlgen.insertXML</code>.
<li><b>I want to render some data stored in Oracle as XML and publish it
on the Web in HTML:</b> Here you have an Oracle CLOB column containing
an XML document. You want a reader to be able to view it in a standard
browser. You want to use XSL to render the XML document into HTML. Do
this by calling our <code>apply_xsl</code> procedure (invokes some Java
code within the database).
</ul>
Generally AOLserver wants to parse XML that comes from a database
table. In this case, it makes sense to do the parsing
that is already in the database
<h3>Overview</h3>
<p>This document describes how to use XML to publish and retrieve data
from Oracle tables. The procedure relies on Oracle's XML parser and
SQL to XML utility, which makes it easy to transform a query result into
a simple XML document with the following general structure:</p>
<pre><tt>
<rowset>
<row>
<column1>value</column1>
<column2>value</column2>
...
<columnN>value</columnN>
</row>
...
</rowset>
</tt></pre>
<p>The parser is written in Java, but Oracle provides a PL/SQL wrapper
package so that the methods can be called directly from SQL. The
package includes methods to format a query result as an XML document,
and to insert an XML document into a database table. However, <em>you
cannot specify a URL as the document source</em>; the document must
already be stored locally in a CLOB or varchar variable. It would be
easy to write a stored procedure in Java that performed the retrieval
(the Oracle Java classes support this form), but Oracle requires you
to have special permissions to open a socket from a Java stored procedure.
We will rely on AOLserver to do the retrieval for us.</p>
<h3>Preparation: Load the Utility</h3>
<p>The first step in any XML project is to load the Java classes and
PL/SQL package into your tablespace. The whole package is available
from the Oracle web site at <a
href="http://technet.oracle.com/tech/xml/oracle_xsu/">
http://technet.oracle.com/tech/xml/oracle_xsu/ </a>. You may have to
register with Oracle TechNet to get it.</p>
<p>Once you have managed to get the tar file onto your server,
explode it and change to the <tt>lib</tt> directory. Edit
the database user and password in the file <tt>oraclexmlsqlload.csh</tt>
and run the script from the shell command line. This
will load everything and perform some tests to ensure that
it is working properly.</p>
<h3>Example data model</h3>
<p>For the examples below, suppose that you have this database table you
want to publish as XML:</p>
<pre><tt>
create table xmltest (
pk integer primary key,
color varchar2(40),
shape varchar2(40)
);
insert into xmltest values (1, 'red', 'circle');
insert into xmltest values (2, 'blue', 'triangle');
insert into xmltest values (3, 'green', 'square');
commit;
</tt></pre>
<h3>Creating XML from Oracle table data</h3>
<p>The Oracle package <tt>xmlgen</tt> allows you to publish any query
result as an XML document. As an example, we will publish the simplest
possible query:</p>
<pre><tt>
select * from xmltest;
</tt></pre>
<p>The Oracle package <tt>xmlgen</tt> has a <tt>getXML</tt> function
that turns a query into a simple XML document. You might hope that
something like this would work:</p>
<pre><tt>
select xmlgen.getXML('select * from xmltest') from dual;
</tt></pre>
<p>This works fine in SQL*plus, but only works <em>once</em> per
session if called from AOLserver. This probably has to do
with the fact that the function returns a temporary CLOB which
has to be freed before the function can be called again, although
this doesn't really explain why it works in SQL*plus.</p>
<p>The workaround is to use a <em>temporary table</em>, which is a new
feature in Oracle 8i that stores session- or transaction- specific
information and deletes it at the end of the session or transaction.
This table will hold the XML document CLOB long enough to get it into
a Tcl string. We will use the <tt>on commit delete rows</tt> option
(this is the default) so that any rows inserted during a transaction
are deleted at the end of the transaction.</p>
<p>First you have a create a table to store the generated XML
documents. Here is a skeleton table, although you may want to
extend it to suit your needs:</p>
<pre><tt>
create sequence xmldoc_seq start with 1;
create global temporary table xmldocs (
doc_id integer primary key,
doc CLOB
) on commit delete rows;
</tt></pre>
<p>Next, you need a PL/SQL wrapper function that generates the
XML document into the temporary CLOB, stores it, and returns
the id of the stored document:</p>
<pre><tt>
create or replace function get_xml (
query varchar2)
return integer is
doc_id integer;
begin
select xmldoc_seq.nextval into doc_id from dual;
insert into xmldocs values (doc_id, xmlgen.getXML(query));
return doc_id;
end;
/
show errors;
</tt></pre>
<h3>Publish an XML document from Oracle data</h3>
<p>To actually publish the query as an XML document, create an AOLserver
tcl page called <tt>xmltest-publish.tcl</tt>:
<pre><tt>
set db [ns_db gethandle]
ns_db dml $db "begin transaction"
set doc_id [ns_ora exec_plsql $db "
begin
:1 := get_xml('select * from xmltest');
end;
"]
set result [ns_db 1row $db "select doc from xmldocs where doc_id = $doc_id"]
set xmldoc [ns_set value $result 0]
ns_db dml $db "end transaction"
ns_return 200 text/plain $xmldoc
</tt></pre>
<p>This code obtains the document ID from the <tt>get_xml</tt> function
created above, and then retrieves the actual document. Note
that the <tt>ns_ora exec_plsql</tt> procedure must be used because
the function has the side effect of inserting a row into a table.
The entire block is wrapped in a transaction so that the generated
XML document is automatically deleted once the page is written.
</p>
<h3>Retrieving an XML document and store its data in an Oracle table</h3>
<p>To retrieve an XML document and store its field values into a database table,
create another copy of the above table named <tt>xmltest2</tt>.
Then create an AOLserver tcl page called <tt>xmltest-retrieve.tcl</tt>:
<pre><tt>
set xmldoc [ns_httpget http://yourdomain/xmltest-publish.tcl]
regsub -all "\[\r\n\]" $xmldoc {} xmldoc
set db [ns_db gethandle]
set statement "
declare
rowsp integer;
begin
rowsp := xmlgen.insertXML('xmltest2', [ns_dbquotevalue $xmldoc]);
end;
"
ns_db dml $db $statement
ns_return 200 text/html "XML inserted."
</tt></pre>
<p>Once the XML document is retrieved using the <tt>ns_httpget</tt>
method, all line breaks in the document must be removed to
avoid breaking the SQL statement. The <tt>insertXML</tt>
function itself must be executed within a PL/SQL block; it
returns the number of rows successfully inserted.</p>
<h3>Transforming an XML document with an XSL stylesheet</h3>
<p>Version 2 of the Oracle XML parser supports XSL stylesheets, which
provide a convenient way to transform XML documents into HTML or any
other format. The <tt>xmlgen</tt> PL/SQL package does not provide this
capability, but I have created my own Java code to support such
transformations. The code is invoked by the <tt>apply_xsl</tt>
procedure in SQL/plus. It can be found in <tt>doc/sql/XMLPublisher</tt>
in the ACS distribution.</p>
<p>To use the function, you need a table to store XSL stylesheets in
the database:</p>
<pre>
create sequence xsldoc_seq start with 1;
create table xsldocs (
doc_id integer primary key,
doc_name varchar2(100),
doc CLOB
);
</pre>
<p>Once you have inserted a stylesheet into the table, you can apply
it to any generated xml document. Simply generate the XML document
into the <tt>xmldocs</tt> table as above, and then call
<tt>apply_xsl</tt> to apply a transformation:</p>
<pre>
...
set doc_id [ns_ora exec_plsql $db "
begin
:1 := get_xml('select * from xmltest');
apply_xsl(:1, 'mystyle.xsl');
end;
"]
...
</pre>
<p>The <tt>apply_xsl</tt> procedure is bound to a Java stored
procedure that retrieves the document from the <tt>xmldocs</tt>
temporary table the named stylesheet from the <tt>xsldocs</tt>
table. It applies the transformation to the document and updates
the xmldocs table with the transformed version of the XML document,
which can then be retrieved as before.</p>
<hr>
<a href="mailto:karlg@arsdigita.com">karlg@arsdigita.com</a>