Generate XML document in PL/SQL from Oracle tables

Question

I have to generate XML documents in PL/SQL from some tables in Oracle. I have never done this before, and I have found there seem to be a few main ways to do it:

• xmldom API
• xml functions (such as xmlelement, xmlagg, xmlroot)
• dbms_xmlgen functions

There are 65 tables that will be referenced to generate a single document, and I will have to validate the output against an xsd. The documents will be generated in a batch (rather than on-demand) - I don't know if that makes a difference. Using Oracle 10g.

I was initially leaning towards using the xmldom package as it looked more flexible, but I am having trouble finding good examples or documentation for it, whereas the xml functions seem better documented and generally more popular. Is there a reason for this?

What approach do people generally recommend for this type of task?

Surmise

Answer 1

In my experience, DBMS_XMLGEN is good for quick and dirty data-to-xml translations, but I've never liked it much because you have to pass the SQL as a string. Plus, your control over element names and ROWSET/ROW structure is severely limited.

The XML functions are extremely handy, and my favorite, if you're dealing with relatively simple structures. Once you get into multiple XMLAgg levels, for instance, I find it devolves quickly into a confusing mess.

XMLDOM is the most flexible way to generate XML, especially if the structure is more complex or there is iterative logic involved. The main drawback here is that its essentially a wrapper around the Java DOM where most of the methods accept a DOMNode input, but PL/SQL doesn't support polymorphism directly, so you end up with a lot of explicit casts between DOMElement and DOMNode and vice versa, etc. Generally I create my own package of overloaded procedures to encapsulate all of this and make it a little less painful to work with.

Zina

How is performance (specifically: speed) for XMLDOM compared to other methods? This code will be generating min. 10k files in batch. I know it's maybe a bit premature to start worrying about perforance optimization, but if a choice now could have a very serious impact later on, I'd rather worry about it now rather than have to rewrite later. – Surmise

I've never measured performance for comparison purposes, but I suspect that the primary factor in any of these cases is the underlying queries more than the XMLification of the results. The only "gotcha" with XMLDOM that I discovered the hard way is you have to explicitly call the freeDocument method to release the memory you've been using after you're done with that document. – Zina

Answer 2

Well, I never used Oracle for generating any very complex XML document, but using DBMS_XMLDOC is quite easy.

You may see a skeleton here(and here, only the pl/sql code). Also use Google Code Search, there is surely something more to find.
Then there is the DBMS_XMLDOM reference which helps even if the documentation is rather dry.

Make sure you also read these two posts:
http://www.liberidu.com/blog/?p=365
http://www.liberidu.com/blog/?p=369

Alternatively you may look into generating XML using a Java Stored Procedure.

Deferred

I had seen the first two links already, but the DBMS_XMLDOM reference was new. Thanks for that link!! – Surmise

Answer 3

I generally use the xml functions (XMLElement, XMLForest, etc.) because I have control over the XSD. I make the XSD generally match the structure of the documents, so the things match together nicely.

If the schema you're trying to work with is exceptionally complex or funky you should consider the DBMS_XMLGEN or DOM methods.

Camphorate

Answer 4

Here is a good discussion of some options available to you:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4980337843276

Also if you search for "Sean Dillon" on AskTom you will get a good collection of articles detailing various problems and solutions using XML within Oracle.

Loathly