Generate XML document in PL/SQL from Oracle tables
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?
Answer
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.