cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nicola Ken Barozzi" <nicola...@supereva.it>
Subject Re: Querying joined tables with SQLProcessor
Date Mon, 03 Jul 2000 08:35:54 GMT
----- Original Message -----
From: "Henning von Bargen" <H.vonBargen@Triestram-Partner.de>
To: <cocoon-users@xml.apache.org>
Sent: Monday, July 03, 2000 9:34 AM
Subject: AW: Querying joined tables with SQLProcessor


>The approach is only usable for a small amount of data.
>It would be better if one could nest queries in the sql processor or taglib.
>Another option (for Oracle users) is to use Oracle XSQL which supports what
>you need with nested queries.

Yes and no. (glad you pointed out though).
It is not only the amount of data, but how the data is organized.
If you need to make a lot of queries, maybe it is faster to get a single
table and group it yourself.
It is true though that with xsl you may need to organize a <big> xml table
file in memory with "generic" xsl commands.
I think that the solution is that the processor should allow grouping definitions
and that he can both do nested queries and construct the xml by comparing
and grouping (one row at a time, you have only the resulting xml in memory).
Here you have to decide if it's faster to query a lot or to do a lot of computer
processing on the <client> side.

>> -----Urspr√ľngliche Nachricht-----
>> Von: Nicola Ken Barozzi [SMTP:nicolaken@supereva.it]
>> Gesendet am: Samstag, 1. Juli 2000 11:51
>> An: cocoon-users@xml.apache.org
>> Betreff: Re: Querying joined tables with SQLProcessor
>>
>> ----- Original Message -----
>> From: "Michael Karliner" <mkarliner@viant.com>
>>
>> >
>> > Hi,
> > I'm trying to query a database that has two
>> > tables that represent a many to one relationship,
>> > that is a book with more than one author. If I
>> > write a query to join these two tables and then
>> > XML of the form:
>> >
>> > <ROW><TITLE>BOOK1</TITLE><AUTHOR>ME</AUTHOR></ROW>
>> >             <ROW><TITLE>BOOK1</TITLE><AUTHOR>HIM</AUTHOR></ROW>
>> >
>> > Is there a neat way to try to get output of the
>> > form:
>> >
>> > BOOK1
>> > Authors: ME,HIM
>> >
>>
>> You basically want to group Authors per Titles.
>> You can do it by applying an appropriate XSL stylesheet to the SQL
>Processor
>> output.
>> To group efficiently you can use the so called Meunchian method: using
>keys
>> and comparing nodes.
>> An example with the explanation is at:
>> http://freespace.virgin.net/b.pawson/xsl/flatfile.html  .
>>
>> CODE (tested with Cocoon 1.74 on Tomcat 3.1, W2000P):
>> Let's suppose that this is what the SQL Processor returns:
>>
>> <?xml version="1.0"?>
>>
>> <?cocoon-process type="sql"?>
>> <?xml-stylesheet href="groupTest.xsl" type="text/xsl"?>
>> <?cocoon-process type="xslt"?>
>>
>> <?cocoon-format type="text/xml"?>
>>
>> <page>
>>
>>  <ROW><TITLE>BOOK1</TITLE><AUTHOR>ME</AUTHOR></ROW>
>>  <ROW><TITLE>BOOK1</TITLE><AUTHOR>HIM</AUTHOR></ROW>
>>
>> </page>
>>
>> And that you created a groupTest.xsl file with:
>>
>> <?xml version="1.0"?>
>> <xsl:stylesheet version="1.0"
>> xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
>>
>>
>> <xsl:key name="ROWs" match="ROW" use="TITLE" />
>>
>> <xsl:template match="page">
>>
>>  <xsl:processing-instruction
>> name="cocoon-format">type="text/xml"</xsl:processing-instruction>
>>
>>   <page>
>>     <!-- cycle through the first ROWs in each group -->
>>     <xsl:for-each select="ROW[generate-id() =
>> generate-id(key('ROWs',TITLE)[1])]">
>>       <ROW>
>>         <TITLE><xsl:value-of select="TITLE" /></TITLE>
>>           <!-- cycle through each of the ROWs in the group -->
>>           <AUTHORS>
>>           <xsl:for-each select="key('ROWs', TITLE)">
>>             <AUTHOR>
>>               <xsl:value-of select="AUTHOR" />
>>             </AUTHOR>
>>           </xsl:for-each>
>>           </AUTHORS>
>>       </ROW>
>>     </xsl:for-each>
>>   </page>
>> </xsl:template>
>>
>>
>> </xsl:stylesheet>
>>
>> This is what it returns you:
>>
>> <?xml version="1.0?>
>> -    <page>
>> -      <ROW>
>>          <TITLE>BOOK1</TITLE>
>> -       <AUTHORS>
>>           <AUTHOR>ME</AUTHOR>
>>           <AUTHOR>HIM</AUTHOR>
>>     </AUTHORS>
>>   </ROW>
>>  </page>
>> <!-- This page was served in 90 milliseconds by Cocoon 1.7.4   -->
>>
>> Now you can apply your stylesheet for displaying easily.
>>
>> Nicola Ken Barozzi - AISA Industries S.p.A
>> http://www.aisaindustries.it/
>> Via Leonardo da Vinci,2 Ticengo (CR) Italy
>> Personal homepage at Java Guru:
>> http://www.jguru.com/jguru/guru/viewchannel.jsp?EID=39153
>> Personal FAQ at Java Guru:
>> http://www.jguru.com/jguru/guru/viewfaqs.jsp?EID=39153
>> Research Activity:
>> Politecnico di Milano - Dipartimento di Meccanica
>> Piazza Leonardo da Vinci, n.32 - 20133 Milano (Italy)



Mime
View raw message