You are viewing a plain text version of this content. The canonical link for it is here.

Posted to users@cocoon.apache.org by "Schmitt, Christian" <Ch...@Dresdner-Bank.com> on 2000/04/07 17:35:57 UTC

Grouping of SQL result set

Hi, 
I've been playing around with Cocoon for a couple of days now. I got it to
run pretty well.
What I'm trying to do now is the following:

Suppose I execute the following xml:

<?xml version="1.0"?>
<?cocoon-process type="sql"?>
<?cocoon-format type="text/xml"?>
<page>
 <connectiondefs>
  <connection name="sample_connection">
   <driver>com.informix.jdbc.IfxDriver</driver>
 
<dburl>jdbc:informix-sqli://server:1530/mydb:INFORMIXSERVER=server</dburl>
   <username>***</username>
   <password>***</password>
  </connection>
 </connectiondefs> 
 <query connection="sample_connection">
  select ename, dname
  from emp, dept
  where dept.deptno = emp.deptno
  group by dname
</query>
</page>

What I get back now as a result looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<page>
 
 <ROWSET>
   <ROW ID="0">
     <ename>Scott</ename>
     <dname>SALES</dname>
   </ROW>
   <ROW ID="1">
     <ename>King</ename>
     <dname>SALES</dname>
   </ROW>
   <ROW ID="2">
     <ename>Adams</ename>
     <dname>MARKETING</dname>
   </ROW>
 </ROWSET>

</page>


What I would like to get back is something like this:

<?xml version="1.0" encoding="UTF-8"?>
<page>
 
 <ROWSET>
   <DNAME name="SALES">
     <ename>Scott</ename>
     <ename>King</ename>
   </DNAME>
   <DNAME name="MARKETING">
     <ename>Adams</ename>
   </DNAME>
 </ROWSET>

</page>


Now my question is... Would I have to run the xml I'm getting back (the
first one) through the processor again and apply a style sheet (xsl I
suppose) and group the dname and ename in this style sheet? Or is there
another way? Please bear with me, as I'm a newbie with this XML/XSL thing.

Thanks,
Christian Schmitt

btw, from what I can tell Cocoon rocks!

Re: Grouping of SQL result set

Posted by Sebastien Koechlin I-VISION <sk...@n-soft.com>.

"Schmitt, Christian" wrote :

> <?xml version="1.0" encoding="UTF-8"?>
> <page>
> 
>  <ROWSET>
>    <ROW ID="0">
>      <ename>Scott</ename>
>      <dname>SALES</dname>
>    </ROW>
>    <ROW ID="1">
>      <ename>King</ename>
>      <dname>SALES</dname>
>    </ROW>
>    <ROW ID="2">
>      <ename>Adams</ename>
>      <dname>MARKETING</dname>
>    </ROW>
>  </ROWSET>
> 
> </page>

> Now my question is... Would I have to run the xml I'm getting back (the
> first one) through the processor again and apply a style sheet (xsl I
> suppose) and group the dname and ename in this style sheet? Or is there
> another way? Please bear with me, as I'm a newbie with this XML/XSL
> thing.

I tried to solve this with XSLT, but failed, It looks like my
variable isn't working in a attribut value. If someone can tell
me what is wrong...

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="ROWSET">
  <xsl:processing-instruction
name="cocoon-format">type="text/html"</xsl:processing-instruction>
    <html>
      <body bgcolor="white">
        <xsl:apply-templates
          select="ROW[position()=1 or
dname!=preceding-sibling::ROW/dname]"
          mode="group"/>
      </body>
    </html>
  </xsl:template>

  <xsl:template match="ROW" mode="group">
    <xsl:variable name="dname"><xsl:value-of
select="dname"/></xsl:variable>
    <dl><dt><xsl:value-of select="$dname"/> :</dt><dd>
      <xsl:apply-templates
select="/page/ROWSET/ROW/ename[../dname='{$dname}']"/>
    </dd></dl>
  </xsl:template>

</xsl:stylesheet>

-- 
Seb