[jdom-interest] SQL join -> Structured XML

[Philippe Lang]

These classes are useful for reporting, not exactly for search. We send
a query with joins to the database, get back a tabular result
(CachedRowSet), create a structured XML file from this result, and sent
it to FOP, which can ouput PDF, PCL, PS... The classes do not generate
code.

Is there another way to get XML from a database query with joins? I know
some databases can actually give back XML directly, instead of a tabular
result, but this is not a feature we can rely on. It's not portable
enough.

Philippe

-----Message d'origine-----
De : Mani Doraisamy [mailto:MDoraisamy at SELECTICA.com]
Envoyé : jeudi, 25. avril 2002 17:03
À : Nott, Matthias; Philippe Lang; jdom-interest at jdom.org
Objet : RE: [jdom-interest] SQL join -> Structured XML


Transforming XML to db might follow the standard 
object model to ER conversion. so by establishing 
a schema which has one to one relationship with 
ER tables, we might able to convert from db to 
xml and from xml to db back. What Phillippe has done 
might be useful in search. it would be great if he 
can share the code. 
mani 

-----Original Message----- 
From: Nott, Matthias [mailto:mnott at vignette.com] 
Sent: Thursday, April 25, 2002 3:57 PM 
To: Philippe Lang; jdom-interest at jdom.org 
Subject: RE: [jdom-interest] SQL join -> Structured XML 


goes sort of the direction of program generators, 
if you want to reimport xml into the db by transforming 
your xml into e.g. stored proc calls. There's quite a lot 
of material around on that. 
M 

-----Original Message----- 
From: Philippe Lang [mailto:philippe.lang at attiksystem.ch] 
Sent: Thursday, April 25, 2002 8:18 AM 
To: jdom-interest at jdom.org 
Subject: [jdom-interest] SQL join -> Structured XML 


Hi, 
I have written a set of classes that take as input the result of an SQL 
query, with joined tables. Given some parameters you give to the classes

(groups, sort order...), very similar to what you can configure in 
Access or Crystal Reports, it outputs an XML file, with the same data, 
but in a structured way. It works with any number of joins. 
I wonder if it would be a good idea to incorporate these classes in 
JDOM? Or does that go beyond the scope of JDOM? 


Example: 
-------- 
(CachedRowSet) 
A        P1   P2 
A        P3   P4 
A        P5   P6 
B        P7   P8 
B        P9   P10 
A        P11  P12 
C        P13  P14 
gives... 


(XML) 
----- A ----- P1   P2 
   |      |-- P3   P4 
   |      |-- P5   P6 
   |      |-- P11  P12 
   | 
   |- B ----- P7   P8 
   |      |-- P9   P10 
   | 
   |- C ----- P13  P14 



------------------------- 
Philippe Lang 
Attik System 
http://www.attiksystem.ch