Stored procedures in concatenated XML
I've got a flash client that expects to get data in XML format from a
call to a HTTP service. Right now I've got Oracle HTTP (Apache)
installed that calls PL/SQL stored procedures. The stored procedures
HTP.P calls to embed data from SELECT statements with concatenated
tags. It seems to work ok, but it also seems to be a kludgy way to
it done. Is there a more effecient, secure, better practice to use?
Interesting question. Oracle 10g provides many possibilities for
If you want to keep your HTTP and stored procedure architecture and
just replace the
concatenated tags in your select statements, look at DBMS_XMLQuery or
the SQLX functions
like XMLELEMENT and XMLFOREST.
However, if you want a more robust solution with security and
management tools, 10g XML DB
provides a great solution to your problem. It's called WebDAV.
Here is a brief blurb from the docs:
"Oracle XML DB repository provides a hierarchical data repository in
the database modeled on
XML. Oracle XML DB repository maps path names (or URLs) onto database
objects of XMLType and
provides management facilities for these objects.
Oracle XML DB also provides the Oracle XML DB protocol server. This
supports standard Internet
protocols, FTP, WebDAV, and HTTP, for accessing its hierarchical
repository or file system.
Because XML documents reference each other using URLs, typically HTTP
URLs, Oracle XML DB
repository and its protocol support are important Oracle XML DB
You can find the documentation on this in the XML DB Application
Developer's Guide on OTN.
In the 10gR1 Document, it's section 24.
This was first published in July 2005