Re: How to transform flat structure into hierarchical one?

[Jeni Tennison <Jeni.Tennison@xxxxxxxxxxxxxxxx>]

Aleksandrs,

>I get a flat structure as a result of SQL query and want to transform it
>into hierarchical structure.

Getting a grouped or hierarchical structure from a flat structure is a very
common question, and there are design patterns in the FAQ that will help
you to do this.  I also agree with Mike and Wendell that adjusting your SQL
output would probably be more efficient.  However, I'm going to talk you
through how to use the structures that are in the FAQ in your particular
situation, because I know it's sometimes hard to translate between the two.  

There are two things that you need to know about to solve your problem
using the Meunchian method: using keys and comparing nodes.

First, you need to group the things that you want to group together.  In
your case, you want to group the records according to the house that they
refer to.  You do this by defining a key using xsl:key.  The three
attributes are:
1. name - this can be anything you want 'records', for example
2. match - these are the things that you want to group: the 'record'
elements in your case
3. use - this is the thing that you want to index the groups by: the value
of the 'house_id' element in your case

In other words:

  <xsl:key name="records" match="record" use="house_id" />

This allows you to use the key() function to access the groups that you've
made.  The first argument gives the name of the key.  The second argument
gives the value of the thing that you have grouped the groups by.  In your
case, to find out all the records that have a house_id of 'h1', you can say:

  key('records', 'h1')

Second, you want to step through each of these groups.  You do this by
stepping through each of the things that are grouped and identifying those
that occur first in the groups.  In your case, you step through each of the
records and find out which ones are the first records about a particular
house.  Given a particular house_id, you can find the first ones in the
groups by taking the first one of the node set that you get from the key:

  key('records', 'h1')[1]

To find out whether a node is the same as the first node in the group that
you've defined using the key, you have to compare the nodes.  One way of
doing this is by comparing the unique id that is generated for the
particular node with the unique id that is generated for the first node in
the group.  In your case, you can get the first record in each group using
the pattern:

  record[generate-id() = generate-id(key('records', house_id)[1])]

You want to process only these items, the ones that are first within their
particular group.  Whether you process them using a xsl:for-each of an
xsl:apply-templates is up to you: the select expression for both will be
the same.

Finally, for each of these items, you want to cycle through the rest of the
items in the group.  You use the key() function to get at that group, and
then just go through them (again using xsl:for-each or xsl:apply-templates
as you desire).

In your case, I've used xsl:for-each because you're not doing anything
particularly complicated and because this saves processing time because the
XSL processor doesn't have to go off hunting for the correct template to
apply next.  The template is:

<xsl:template match="record_set">
  <house_list>
    <!-- cycle through the first records in each group -->
    <xsl:for-each select="record[generate-id() = generate-id(key('records',
house_id)[1])]">
      <house>
        <id><xsl:value-of select="house_id" /></id>
        <rooms>
          <!-- cycle through each of the records in the group -->
          <xsl:for-each select="key('records', house_id)">
            <room>
              <id><xsl:value-of select="room_id" /></id>
            </room>
          </xsl:for-each>
        </rooms>
      </house>
    </xsl:for-each>
  </house_list>
</xsl:template>

I have tested this within SAXON, using your example, and it gives the
desired output.

I hope that this helps,

Jeni

Dr Jeni Tennison
Epistemics Ltd, Strelley Hall, Nottingham, NG8 6PE
Telephone 0115 9061301 ? Fax 0115 9061304 ? Email
jeni.tennison@xxxxxxxxxxxxxxxx



 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list