Looking for XML tools.

[abhijit]

Folks,
The current requirement in my shop is

1) Connect to a source database.
2) Write a query to fetch data.
3) Use an existing XSD file.
4) Transform the data in the source table using the XSD and then create an XML file.

I have been using a trial version of Pervasive SQL to evaluate if this is the tool for our job.

This works for simple tables, but I haven't worked out a mechanism to apply this to a hierarchical structure.

To give you an example.

Table tbStudent
StudentID number,
StudentName varchar2(100)
StudentDob date
ClassroomId Number

Table tbTeacher
TeacherID number
TeacherName varchar2(100)
ClassRoomId number

Table tbClassroom
ClassroomID number
ClassroomName varchar2(100)

I can easily create three XML files from these three tables using the Data Integrator product.

If I had to create a file which were to have a nested structure like

<CLASSROOM ID = "1" NAME="FIFTH B">
 <TEACHER NAME="Mr Smith"/>
 <STUDENT>
  <ID="100"/><NAME="John Jones"/><DOB="01-01-2001"/>
</STUDENT>
 <STUDENT>
  <ID="101"/><NAME="Jane Jones"/><DOB="01-01-2002"/>
</STUDENT>
</CLASSROOM> 

This is the structure I am trying to reproduce.

One way would be a manual way and write a script. This is the painful route that I would like to avoid, since the actual structure has several levels of nesting. Do you have any tools that you can recommend?