I am trying to return XML from a relational database. I use the JDBC result set from the sql query and the JDBC result set meta data to create my XML. This works fine where an object maps to a table in the relational database and in cases where the object contains a collection of objects (ie cases where there is a one to many relationship in the database). I am using var array / nest tables / object views features in Oracle 8i to model the collection.
This works fine for a single level of one to many relationship. But if I have A single instance of A having many B and every instance of B has one or more C, I run into problems because i cannot create a table with in a table. Is there a better way of moddeling this.
Eg. A author can have many articles, and each artcle might have been published in many journals.
Author to Article is one to many and article to journal is one to many.
I wanted to get XML out of the database, if i am not able to model this then I will have to create java data objects and then generate XML from them.
i have done similar exercise recently under my project.i used collections to model 1:m relationship between database , metadatabase to generate xml.
As to my understanding you want to generate xml from database without using any java object(collections in my case).
I think there need be some mechanism to hold the relation between tables.So what this mechanism be--- may be java Object(e.g collections),some formated data which may be similar to streaming.
e.g you get resultset from table A(on condition pr key a), stream(file output stream)it onto some file xx.xml( with some declarations already streamed like first few lines in in xml).then read table B *condition over a*.Stream it to xx.xml with closing-ending tags at their appropriate place.Similarly for c.You have to maintain loops in your program for when and how to stream particular information so that relationships be maintained.
I didn't try for streaming but have an idea that xml can be generated by this method, obviously at the cost of more coding and streaming.
You might take a look at Oracle's XDK. They have ways ov resolving this no problem. They even have a servlet (XSQL) that will do this if you don't want to use any java.