Women in Technology

Hear us Roar



Article:
  Generating Database Server-Side Cross Tabulations
Subject:   Crosstabs are the playground for XML
Date:   2003-12-08 12:11:53
From:   anonymous2
IMHO, Crosstabs can easily be done by XML. XML is very flexible at rows, columns, ...


The only way is how to create the XML.


I personally prefer Cocoon to do this. I'm an Open Source user but Cocoon runs as a servlet on Tomcat, so it's available for Windows.


I give a simple example from the Cocoon User Guide:


<page xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<execute-query xmlns="http://apache.org/cocoon/SQL/2.0">
<query name="department" >
select id,name from department_table
</query>
<execute-query>
<query name="employee">
select id,name from employee_table where department_id =
<ancestor-value
sql:name="id" sql:level="1"/>
</query>
</execute-query>
</execute-query>
</page>



This would result in something like this:


<page xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<rowset nrofrows="2" name="department"
xmlns="http://apache.org/cocoon/SQL/2.0">
<row>
<id>1</id>
<name>Programmers</name>
<rowset nrofrows="2" name="employee">
<row>
<id>1</id>
<name>Donald Ball</name>
</row>
<row>
<id>2</id>
<name>Stefano Mazzocchi</name>
</row>
</rowset>
</row>
<row>
<id>2</id>
<name>Loungers</name>
<rowset nrofrows="1" name="employee">
<row>
<id>3</id>
<name>Pierpaolo Fumagalli</name>
</row>
</rowset>
</row>
</rowset>
</page>


Glue this together with some XSLT, and you have your report.


More info: http://cocoon.apache.org


yves.vindevogel@implements.be



Full Threads Oldest First

Showing messages 1 through 1 of 1.

  • Crosstabs are the playground for XML
    2003-12-08 14:23:57  gmax [View]

    "IMHO, Crosstabs can easily be done by XML"

    Are you talking about producing or representing them? Client-side or server-side? (And since we are talking about database servers, don't forget that web servers are database clients.)

    Crosstabs are statistical reports. They manipulate data from a database.
    The purpose of my article is about doing it server-side rather than client-side.
    I don't understand where exactly XML enters the equation.


    XML is a markup language. It can represent data, but AFAIK it can't produce it. I hope this is not a case of confusing databases with their media (http://www.dbazine.com/pascal2.html).

    Anyway, if Cocoon can send a query to a database, it can also send a crosstab query. The relevant point is how to create the SQL query and then how to transform the result from the database into a hierarchical structure. Notice that, unlike a normal database recordset, a crosstab query has a tree structure at the top and one at its left. Each row is related to both structures at once.
    I am not saying that this data can't be represented in XML (because you can see several examples of XML representation at http://gmax.oltrelinux.com/cgi_bin/xtab.cgi) but it is less trivial that it seems.

    The bottom line is: XML can represent data, but to extract data from a database you need some help from a major language.

    Cheers

    gmax