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:
xmlgen.getXML 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
xmlgen.insertXML.
apply_xsl procedure (invokes some Java
code within the database).
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:
<rowset>
<row>
<column1>value</column1>
<column2>value</column2>
...
<columnN>value</columnN>
</row>
...
</rowset>
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, you cannot specify a URL as the document source; 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.
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 http://technet.oracle.com/tech/xml/oracle_xsu/ . You may have to register with Oracle TechNet to get it.
Once you have managed to get the tar file onto your server, explode it and change to the lib directory. Edit the database user and password in the file oraclexmlsqlload.csh and run the script from the shell command line. This will load everything and perform some tests to ensure that it is working properly.
For the examples below, suppose that you have this database table you want to publish as XML:
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;
The Oracle package xmlgen allows you to publish any query result as an XML document. As an example, we will publish the simplest possible query:
select * from xmltest;
The Oracle package xmlgen has a getXML function that turns a query into a simple XML document. You might hope that something like this would work:
select xmlgen.getXML('select * from xmltest') from dual;
This works fine in SQL*plus, but only works once 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.
The workaround is to use a temporary table, 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 on commit delete rows option (this is the default) so that any rows inserted during a transaction are deleted at the end of the transaction.
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:
create sequence xmldoc_seq start with 1;
create global temporary table xmldocs (
doc_id integer primary key,
doc CLOB
) on commit delete rows;
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:
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;
To actually publish the query as an XML document, create an AOLserver tcl page called xmltest-publish.tcl:
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
This code obtains the document ID from the get_xml function created above, and then retrieves the actual document. Note that the ns_ora exec_plsql 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.
To retrieve an XML document and store its field values into a database table, create another copy of the above table named xmltest2. Then create an AOLserver tcl page called xmltest-retrieve.tcl:
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."
Once the XML document is retrieved using the ns_httpget method, all line breaks in the document must be removed to avoid breaking the SQL statement. The insertXML function itself must be executed within a PL/SQL block; it returns the number of rows successfully inserted.
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 xmlgen 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 apply_xsl procedure in SQL/plus. It can be found in doc/sql/XMLPublisher in the ACS distribution.
To use the function, you need a table to store XSL stylesheets in the database:
create sequence xsldoc_seq start with 1;
create table xsldocs (
doc_id integer primary key,
doc_name varchar2(100),
doc CLOB
);
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 xmldocs table as above, and then call apply_xsl to apply a transformation:
...
set doc_id [ns_ora exec_plsql $db "
begin
:1 := get_xml('select * from xmltest');
apply_xsl(:1, 'mystyle.xsl');
end;
"]
...
The apply_xsl procedure is bound to a Java stored procedure that retrieves the document from the xmldocs temporary table the named stylesheet from the xsldocs 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.