Systems Engineering and RDBMS

Generating XML documents in Oracle

Posted by decipherinfosys on February 3, 2007

Generation of XML in Oracle can be achieved in quite a few different ways.

1) SQLX : In my opinion, this is the best way to generate XML.  It is the most natural way to generate a result since you can just use a query for this.  They generate query plans and they use the cost-based optimizer and they use indexed reads, etc.  This isn’t to say any of the others don’t but SQLX is Oracle’s latest and greatest mechanism for generating XML.
2) DBMS_XMLQUERY:  This is another way to query the database to ask for an XML result set, but the options you have for customizing the way Oracle generates the final document aren’t quite as good as SQLX’s options.  SQLX is a bit cumbersome though just like any other option for generating the result-set in a particular format.
3) DBMS_XMLGEN.GETXML : Short, simple and sweet…but this is limited in functionality…this goes in hand to hand with other system XML functions.

Storing/Processing XML can be done by either of these 2 options:

a) DBMS_XMLSAVE:  This is a PL/SQL supplied package for taking an XML document and storing it into relational tables and/or objects.  It removes a lot of the work from the programmers hands but the XML has to be in a particular “canonical” format which must match the storage architecture of your tables and/or objects.  This works but isn’t quite XML DB.
b) XML DB:  Using XML DB, there are many options you have for storing and interacting with your XML.  There’s structured storage, where the database automatically breaks down your XML document based on your XML Schema, there’s unstructured storage which places the entire XML document into a CLOB column in the XML DB schema.  There’s the XML Repository which is yet another way to interface with the databases’s XML technology.  You can upload an XML document via FTP, WebDAV or just using SQL.  The XML document is then stored in a CLOB column or a set of object-relational tables & structures for further processing/queries.  When you need the XML document, you pull it out via FTP, HTTP, WebDAV or SQL.  Oracle is then responsible for reconstructing the XML document for you behind the scenes.

One Response to “Generating XML documents in Oracle”

  1. […] by decipherinfosys on October 30th, 2007 In one of our previous blog post, we had covered how to generate XML documents in Oracle. And in subsequent posts, we had covered […]

Sorry, the comment form is closed at this time.

%d bloggers like this: