I am creating an application that takes data and generates a
schema-based XML document.  I am trying to figure out how to automate
that process for creating XML on the fly.  I know that I can do this
using Java's JAXB.  I want to know if I can do it using Oracle's XML
functionality.
Basically, I want to use Oracle functionality to automatically
generate a table or tables based on my schema so that, when receiving
the non-XML data, I have a place to insert it (on a field by field
basis) for the purpose of retrieving it and, again using Oracle XML
functionality, automatically generating an XML document conforming to my
schema.  By "automatically" I mean something like a simple select
statement that does not require me to write any actual XML tags.  I
should not have to know anything about the XML itself.
I need to know if this is possible and would like to see a
clear example of how to do it, or I need to know definitively that it
is not possible with Oracle.

---

Clarification of Question by kellybluesky-ga on 29 Mar 2004 14:15 PST

I should specify Oracle 9.2. Thanks.

---

Request for Question Clarification by mathtalk-ga on 29 Mar 2004 17:43 PST

Hi, kellybluesky-ga:

Perhaps your Question has two sides.  First, can you receive "non-XML
data" and insert it into some Oracle tables?  Second, can you then
extract that data out into an "automatically generated" XML document?

I mention these two aspects because although your subject line and
other remarks focus on XML document generation without knowing
"anything about the XML itself," the usefulness of such an exercise is
apt to depend largely on the underlying data "schema" which you
mention briefly.

If you can provide additional details about what you're trying to do,
it would greatly increase the chances of finding some ideas that will
work for you.

On the other hand, perhaps you simply want some links that describe
what facilities there are in Oracle 9.2 to create XML extracts... I'm
not trying to make your Question more complicated than it needs to be!

regards, mathtalk-ga

---

Clarification of Question by kellybluesky-ga on 30 Mar 2004 06:54 PST

When I say "schema" I'm not referring to a schema in the Oracle
database sense, but rather an XML schema.  This is essentially a
specification of an XML document, which defines it's elements,
attributes, hierarchy, etc.  I have some reason to believe (from my
research so far on Oracle's rather confusing XML documentation) that
Oracle has some facility to take an XML schema as input and generate a
table or tables that corresponds to that schema so that there is a
place to store data that is received in an XML document that conforms
to that XML schema.
I imagine it works like this (and this is what I need confirmation on
and greater detail/examples of):

Developer has an XML schema (.xsd file)

XML schema is used as input to some unknown Oracle facility which
generates a set of tables which correspond to the XML schema in both
field mapping and hierarchical structure.

Once the tables exist an application can be created which receives
non-XML data and inserts it into these tables.

The application can then call some unknown Oracle facility (probably
some supplied packages, i.e. DBMS_XXXX) which extracts the data from
these tables and creates an XML document from it which conforms to the
XML schema.

So, in answer to one of your questions, no, I'm not really asking if
non-XML data can be inserted into Oracle tables, because I know that
of course it can.  But I'm looking for automatic creation of those
tables based on the XML schema and automatic XML document creation
based on those tables, the data, and the XML schema.  The fact that I
have the XML schema is what should allow me to otherwise not have to
know anything about the XML (because the schema itself contains all
the necessary information that Oracle can use, in theory, to do what
I'm expecting).  I hope this clarifies the question sufficiently. 
Please let me know if you need anything further.

Hi, kellybluesky-ga:

I've not used these new Oracle XML generation tools myself, so I
cannot judge to what extent they might fit your requirement of not
needing to know anything about the XML itself.

Here's a set of articles on the Oracle tools for both XML input and output:

[Oracle9i XML Articles]
http://www.oracle-base.com/articles/9i/Articles9i.php#XML

with this article in particular:

[XML Generation In Oracle9i Using DBMS_XMLQuery, DBMS_XMLGen,
Sys_XMLGen And Sys_XMLAgg]
http://www.oracle-base.com/articles/9i/XMLGeneration9i.php

My guess is that there are tradeoffs between easier to use functions
like the one described in this link:

http://www.oracle-base.com/articles/9i/GenerateXML1.sql

and more flexible functions like the one described in this link:

http://www.oracle-base.com/articles/9i/GenerateXML4.sql

Perhaps the right mixture of easy of use and flexibility will have to
await the emerging standard for producing XML from SQL queries:

[SQL/XML (SQLX)]
http://www.oracle-base.com/articles/9i/SQLXML9i.php

Oracle notes that their support for SQL/XML is subject to change, and
the changes from release 1 to release 2 tend to underscore this point.

For a broader perspective (beyond Oracle) you might be interested in this article:

[The Joy of SQLX]
http://consulting.dthomas.co.uk/ooad_articles_resources/SQLX-SQL-XML.pdf

From the example given here:

[askTom - RSS Feeds Done with SQLX]
http://asktom.oracle.com/~sdillon/rss.html

it certainly appears that the issues of tag-nesting are "pushed back"
into SQL-like syntax for the query.  I think a glance at this ("The
SQLX Queries" near the middle of the page) will give you a good idea
whether it would be worth further investigation on your part.

For those interested in a more XML-centric discussion of the Oracle9i
Release 2 features of SQLX, see here:

[Oracle9i Release 2: Native SQLX, XPath, and XML Schema Features]
http://www.idealliance.org/papers/xmle02/dx_xmle02/papers/03-06-06/03-06-06.html

regards, mathtalk-ga

Unfortunately it looks like that last link is to a conference paper
that was never posted on the site (beyond the abstract given by the
existing link).

-- mathtalk-ga

Thanks for looking.  This is mostly the same as what I had found
myself though and none of it really touches on the specific
functionality I'm looking for.  It may be that it does not exist but
I'll keep looking.
Thanks again.