Return-Path: <H.vonBargen@Triestram-Partner.de>
Mailing-List: contact cocoon-users-help@xml.apache.org; run by ezmlm
Delivered-To: mailing list cocoon-users@xml.apache.org
Received: (qmail 36168 invoked from network); 3 Jul 2000 07:34:43 -0000
Received: from elvis.triestram-partner.de (HELO
 BLAHFASEL.Triestram-Partner.de) (212.185.173.195)
  by locus.apache.org with SMTP; 3 Jul 2000 07:34:43 -0000
Received: by BLAHFASEL with Internet Mail Service (5.5.2650.21)
	id <NPZ9RSZ4>; Mon, 3 Jul 2000 09:34:41 +0200
Message-ID: <4094ADEA26D8D3119F020000D1106EC4061A23@BLAHFASEL>
From: Henning von Bargen <H.vonBargen@Triestram-Partner.de>
To: cocoon-users@xml.apache.org
Subject: AW: Querying joined tables with SQLProcessor
Date: Mon, 3 Jul 2000 09:34:40 +0200 
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2650.21)
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Spam-Rating: locus.apache.org 1.6.2 0/1000/N

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=FCngliche 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
>=20
> ----- Original Message -----
> From: "Michael Karliner" <mkarliner@viant.com>
>=20
> >
> > 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
> >
>=20
> 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  .
>=20
> CODE (tested with Cocoon 1.74 on Tomcat 3.1, W2000P):
> Let's suppose that this is what the SQL Processor returns:
>=20
> <?xml version=3D"1.0"?>
>=20
> <?cocoon-process type=3D"sql"?>
> <?xml-stylesheet href=3D"groupTest.xsl" type=3D"text/xsl"?>
> <?cocoon-process type=3D"xslt"?>
>=20
> <?cocoon-format type=3D"text/xml"?>
>=20
> <page>
>=20
>  <ROW><TITLE>BOOK1</TITLE><AUTHOR>ME</AUTHOR></ROW>
>  <ROW><TITLE>BOOK1</TITLE><AUTHOR>HIM</AUTHOR></ROW>
>=20
> </page>
>=20
> And that you created a groupTest.xsl file with:
>=20
> <?xml version=3D"1.0"?>
> <xsl:stylesheet version=3D"1.0"
> xmlns:xsl=3D"http://www.w3.org/1999/XSL/Transform">
>=20
>=20
> <xsl:key name=3D"ROWs" match=3D"ROW" use=3D"TITLE" />
>=20
> <xsl:template match=3D"page">
>=20
>  <xsl:processing-instruction
> name=3D"cocoon-format">type=3D"text/xml"</xsl:processing-instruction>
>=20
>   <page>
>     <!-- cycle through the first ROWs in each group -->
>     <xsl:for-each select=3D"ROW[generate-id() =3D
> generate-id(key('ROWs',TITLE)[1])]">
>       <ROW>
>         <TITLE><xsl:value-of select=3D"TITLE" /></TITLE>
>           <!-- cycle through each of the ROWs in the group -->
>           <AUTHORS>
>           <xsl:for-each select=3D"key('ROWs', TITLE)">
>             <AUTHOR>
>               <xsl:value-of select=3D"AUTHOR" />
>             </AUTHOR>
>           </xsl:for-each>
>           </AUTHORS>
>       </ROW>
>     </xsl:for-each>
>   </page>
> </xsl:template>
>=20
>=20
> </xsl:stylesheet>
>=20
> This is what it returns you:
>=20
> <?xml version=3D"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   -->
>=20
> Now you can apply your stylesheet for displaying easily.
>=20
> 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=3D39153
> Personal FAQ at Java Guru:
> http://www.jguru.com/jguru/guru/viewfaqs.jsp?EID=3D39153
> Research Activity:
> Politecnico di Milano - Dipartimento di Meccanica
> Piazza Leonardo da Vinci, n.32 - 20133 Milano (Italy)
>=20
>=20
>=20
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org