cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Henning von Bargen <H.vonBar...@Triestram-Partner.de>
Subject AW: Querying joined tables with SQLProcessor
Date Mon, 03 Jul 2000 07:34:40 GMT
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.
Henning

> -----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
> > want to format it through SQL Processor, I'll get
> > 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)
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org

Mime
View raw message