How to create hiearchical xml using ESQL

This How-To describes the steps to write xsp that produces XML hierarchical structure using ESQL nested queries.
Author: Pavel Vrecion


There is quite often need to create master-detail web pages based on database queries.
Typical example is company that has more departments with employees.
In this case we would like to create XML structure starting with company, with more child elements describing department and on next level also child elements for each employee. Note that for such tasks Cocoon has already partial support in ESQL called group queries, but this is limited to two level queries. We will examine the other way, how to achieve this. It will enable us to create richer XML structures.
During the process we will also learn how to interweave Java and SQL.


We will need Cocoon (of course) and database with at least two tables linked by “one to many” reference.
In the example we will use Firebird database engine and we will utilize example databases employee.fdb, which is included in Firebird installation. But other databases with JDBC support will work, too.
Consult Cocoon and/or JDBC and your database documentations for details regarding database installation, running, parameters and JDBC connections.

Steps briefly

We will:
1. create at least two tables (or you can skip this step and use already existing database)
2. define database connection pool in cocoon.xconf (Cocoon main configuration file)
3. create application with:
- simple sitemap
- and simple xsp (XML Server Page)
4. create simple stylesheet to transform XML into HTML

Steps in detail

Database tables

We will need two tables, one for department and second for employee.
Two tables will be linked through “one to many” relationship.
Employee table will have department foreign key – department id, which will link employee to department.

You can create tables with following SQL DDL statements:

    HEAD_DEPT   CHAR(3),

    EMP_NO       INTEGER,
    DEPT_NO      CHAR(3),
    JOB_CODE     VARCHAR(4),

Well, if you like, you can also create some integrity constraints and other fancy database stuff like sequences, triggers, but for our purpose SQL, we have mentioned above, is fine.
Note that column DEPT_NO is primary key in DEPARTMENT table (and so should have mandatory and unique value). Same column and its value is used in EMPLOYEE table to link specific employee to specific department. HEAD_DEPT is “self reference” and can be used to create rich company structure that consists from divisions, departments, sub-departments etc.
To verify that everything works you should test database using some SQL tool. It is good idea to add some test, but meaningful, data during the process, too.
When we have tables in the database we must tell Cocoon, where to find the data and how to connect to database. That is why we will add new datasource to Cocoon configuration file.

Adding new datasource to cocoon.xconf

Find cocoon.xconf (usually located in Cocoon directory build/webapp/WEB-INF), open it with some text editor, find datasources section and add new connection parameters. After editing datasource part of {cocoon.xconf it will look somehow like this: {{{<datasources> ....

..... }}} We suppose that you have already downloaded and installed database engine and libraries, and you have added JDBC driver name to Cocoon web.xml configuration file (located in the same directory as cocoon.xconf).
Your web.xml should have param-value element under servlet/init-param elements similar to this: {{{<init-param>

</init-param> }}} Note that in this example there are specified drivers for several database engines. If you use some other database, please consult corresponding documentation.
After these steps we should have database and Cocoon up and running, Cocoon also should already “know” how to connect to database.
We are ready to start writing xsp and ultimately to show some data to crowd of cheering users.

Writing XSP

As you remember, main purpose of our xsp is to present data from database in rich xml structure. We will do it by executing SQL SELECT command that will return rowset of departments.
We will save department id (identification, or primary key) into Java variable and use it in nested query.
Nested query will then produce list of employees for each department.
Clear? No? Don’t worry, and let’s go to examples.
Create new directory called test (for example) in Cocoon build/webapp directory (other applications are located there, too) and create new text file named empxml.xsp (you can name it differently, of course). Add following code to it and save it.
{{{<?xml version="1.0"?> <xsp:page language="java"

<!-- declare integer variable called deptno --> <xsp:logic>int deptno=-1;</xsp:logic>

<!-- create root xml element --> <Company>

<!-- start esql section using database connection declared in cocoon.xconf --> <esql:connection>

<!-- query departments -->

<!-- get department id from database and store it in deptno variable -->

<!-- create XML element for department and add some interesting values from database -->

<!-- create XML element for emloyees and add some data -->

<!-- nested query for employees


<!-- create XML element for employee and add some child elements with values -->



</xsp:page> }}} OK we have xsp page. Note that we have filled Java variable with ESQL command
{{{<xsp:logic>deptno=<esql:get-int column="DEPT_NO"/>;</xsp:logic> }}} And then used Java variable in ESQL command {{{<esql:query>

</esql:query> }}} That is the main trick of nested queries and hierarchical structures.
As a next step we will create simple sitemap in our application directory.

Simple sitemap

Sitemap will be really simple. Its only purpose is to show one page, we have just created. Create new text file called sitemap.xmap in application directory and fill it with following text: {{{<?xml version="1.0"?> <map:sitemap xmlns:map="">

</map:sitemap> }}} At this stage we should have 2 files in our example application directory:

We have created xsp that produces xml structure. We have also sitemap with pipeline that calls this xsp when Cocoon gets employee.xml request.
So let’s try it. In your favorite browser type url (when you run Cocoon locally, your url will probably be http://localhost:8888/test/employees.xml).
You should see xsp result in the structured XML form, something like this: {{{<Company>

}}} Note that department can be linked to parent department through HEAD_DEPT column. So, as homework, you can enhance example application by modifying xsp so that it will contain nested queries:

select * from DEPARTMENT where HEAD_DEPT is null -- (get company divisions)
select * from EMPLOYEE where DEPT_NO = <xsp:expr>deptno</xsp:expr> -- (get division employees)
select * from DEPARTMENT where HEAD_DEPT = <xsp:expr>deptno</xsp:expr> -- (get divisions departments i.e. second level departments)
select * from EMPLOYEE where DEPT_NO = <xsp:expr>deptno</xsp:expr> -- (get department employees)

You should see xml, maybe even in the structure, you have expected. You are happy, but your users expect some fancy graphics with company logo, and they strictly demand yellow text on pink background.
Well, make them happy, too. We will write xsl and postpone our celebration, but just a little.
XSL has one hidden virtue, it divides data and presentation. And we just know, that our users will strictly demand something else next week, and quite often they will act in separated groups divided by languages, browsers etc. This approach opens the way how to serve them all, and how to make later changes fast.

Simple XSL

To keep things simple and clear, we will start with several HTML lines for each department and one table for department employees.
Create new file employees.xsl in application directory and fill it with following content: {{{<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0"

<xsl:template match="Company">


</xsl:stylesheet> }}} Now we will use this xslt code to transform xml data into html. To do it, we must enhance Cocoon application sitemap a little. Add several lines to sitemap.xmap, so the new sitemap will look like this:

<?xml version="1.0"?>
<map:sitemap xmlns:map="">


                        <map:match pattern="employees.xml">
                                <map:generate type="serverpages" src="empxml.xsp" />
                                <map:serialize type="xml"/>

                        <map:match pattern="employees.html">
                                <map:generate type="serverpages" src="empxml.xsp" />
                                <map:transform src="employees.xsl"/>
                                <map:serialize type="html"/>



At this stage we should have 3 files in our example application directory:

After calling employees.html (for example http://localhost:8888/test/employees.html we should see html formatted text:

Corporate Headquarters
Location: Monterey
Phone: (408) 555-1234

Department employees


Job code

Job country

Phone extension

Sales and Marketing
Location: San Francisco
Phone: (415) 555-1234

Department employees


Job code

Job country

Phone extension

Warvick Mary S.




Yanowski Michael




Location: Monterey
Phone: (408) 555-1234

Department employees


Job code

Job country

Phone extension

Brown Kelly




Nelson Robert




In praxis you will probably spend some more time playing with HTML layout, fonts, colors and graphics, but believe me, better is to find somebody who really has the taste, and who will do HTML coding in xsl much better then we, programmers, usually do.


We have learned how to use xsp and how to mix Java and SQL codes to create hierarchical structures.
Such approach has several advantages. Code is short and more readable.
Hierarchical XML structure simplifies transformations like using XSLT for HTML pages, as well as any other automated processing. And, as a bonus, data, logic and presentation layers are kept apart.


Care to comment on this How-To?
Got another tip?
Help keep this How-To relevant by passing along any useful feedback to the author, Pavel Vrecion.

HowToCreateHiearchicalXmlUsingESQL (last edited 2009-09-20 23:40:19 by localhost)