XSLT to transform a flat XML to a hierarchy / tree using grouping

A common problem in the SOA development is that you need to provide a webservice which produce a hierarchy/tree-structure XML such as:

<toys>
  <category> Train </category>
  <members>
    <name>Thomas </name>
    <name> Percy</name>
  </members>
</toys>
<toys>
  <category> Sesame street Monsters </category>
  <members>
    <name> Elmo </name>
    <name> Zoe </name>
  </members>
</toys>

Where the data come from a relational database table such as:

Category	Name
Train	Thomas
Train	Percy
Monster	Elmo
Monster	Zoe

When you build this webservice using Oracle SOA suite, first you make a database adapter (the right side in the picture bellow) then expose this to a web service (the left side in the picture below). You can add a mediator between them which is handy for routing (if you use more than one database adapters / external webservice calls) and/or transformations.

However the output from this database-webservice is apparently just a flat XML such as:

<toy>
  <category>Train </category>
  <name>Thomas </name>
</toy>
<toy>
  <category>Train </category>
  <name>Percy </name>
</toy>
<toy>
  <category>Monster </category>
  <name>Elmo </name>
</toy>
<toy>
  <category>Monster </category>
  <name>Zoe </name>
</toy>

Thus we need to transform this flat XML to the hierarchical XML as mentioned above. With Oracle SOA Suite, you can define de Schema in the exposed webservice and then apply a transformation in the reply route from the database adapter to the exposed webservice.

A common XSLT solution for this problem is the Muenchian method. However, thanks to XSLT 2.0 now we have a much easier solution using xsl:for-each-group. Here is an example of the XSLT code that I used:



You're welcome to drop comments or discuss more XSL/XML/SOA tricks.

To learn more about grouping or XSLT in general, please read the "Beginning XSLT and Xpath."


Oracle SOA Suite is a great way to build a web service to publish your database. This solution can be built within minutes. Please read "Getting Started With Oracle SOA Suite" to learn more.