This is the mail archive of the xsl-list@mulberrytech.com mailing list .


Index Nav: [Date Index] [Subject Index] [Author Index] [Thread Index]
Message Nav: [Date Prev] [Date Next] [Thread Prev] [Thread Next]

RE: How to transform flat structure into hierarchical one?


First some related info and then an answer.
The info might not apply to you but the (TESTED) answer does.

The info can be useful to someone else doing something similar.

<relatedinfo>
I recently solved a much more complex problem (many levels of data
hierarchy) of that kind using ADO 2.5 and its "Shape" functionality.
- One can shape a (define a hierarchical) recordset by defining the
  queries for each branching of the tree and how the data is related;
- A specific syntax encapsulates the standard SQL queries to help
  this definition;
- You can have quite complex trees - I had 10 branchings/SELECT's
  building a maximal "deepness" of 6 levels of data;
- Since I did not need to parameterise the queries, in my case ADO
  executed each query ONCE merging all data - in order to build the
  hierarchy - in memory. Damn fast!
- One can build this kind of think with any data source that ADO
  supports;
- The "ugly" XML that ADO generates can be efficiently used or
  transformed in something nicer using XSL.

(In a way... SHAPED SQL returns something that is not flat already.)
</relatedinfo>

*** Now, for the answer part:

As I understand it your operation has to steps:
 1 - Getting data from a database in XML format;
 2 - Transforming that format into something you can work with using XSLT.

And the typical algorithm to group the nodes has you wish involves a few
logical steps:
 A - Sort the nodes by house number;
 B - Separate the records in groups with the same house_id.

After having A done B is simple: you just have to find when a record's
house_id is different from the previous one. Trivial. We all know it.

But in you initial posting it sounds like you want to do both A and B in
step 2 (XSLT). That is why Michael gave you the obvious key to make it
fast by remarking:
  "Alternatively, change the SQL query so it does the grouping for you"

Here he does not mean group as in "GROUP BY" but simply as putting
related records next to each other as in "ORDER BY". Don't think in SQL
all the time - English is trickier than that.


Only then, in your reply you state: "...using the fact that SQL result
set is already grouped and sorted according to my needs?"

This is not the necessarily the same has having the resulting XML sorted.
Anyway, you are probably using a tool/component to transform a SQL query
in XML, and that thing should generate the XML elements in the order it
gets the data.

Now, you just need something really simple. I am sure you could find out
something similar in the great and many (even if, at times, a bit
confusing) David Pawson's samples.

But since you were not in the mood to make a deeper search (I know it can
be difficult) and since I am having some fun doing it on my own, here
goes the commented XSL you need:


<?xml version="1.0" encoding="ISO-8859-1" ?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >

    <xsl:output method="xml" indent="yes" encoding="ISO-8859-1" />

  <xsl:template match="/">
    <house_list>
        <!-- Assuming that source record nodes come sorted by house_id -->
        <!-- let's build a house node for each different house_id by   -->
        <!-- looping trough all the records...                         -->
        <xsl:for-each select="/record_set/record">
            <!-- ...and, if it is the first node OR this house_id is != from
the previous... -->
            <xsl:if test="(1=position()) or
(preceding-sibling::*[1]/house_id != house_id)">
                <!-- ...buid a house for this node_id -->
                <xsl:call-template name="housebuilder">
                  <xsl:with-param name="i_house_id"><xsl:value-of
select="house_id" /></xsl:with-param>
                </xsl:call-template>
            </xsl:if>
          </xsl:for-each>
    </house_list>
  </xsl:template>


  <xsl:template name="housebuilder">
    <xsl:param name="i_house_id" />
    <!-- Building a house with the house_id $i_house_id... -->
    <house>
      <id><xsl:value-of select="$i_house_id" /></id>
      <rooms>
        <!-- ...selecting all the records for this house... -->
        <xsl:for-each select="/record_set/record[house_id = $i_house_id]">
          <!-- ...and building a room for each -->
          <room>
            <id><xsl:value-of select="room_id" /></id>
          </room>
        </xsl:for-each>
      </rooms>
    </house>
  </xsl:template>

</xsl:stylesheet>


If your parser is bad with axis, you can code the "xsl:if" sentence like
this:

  <!-- ...and, if it is the first node OR this house_id is != from the
previous... -->
  <xsl:variable name="prevrecpos" select="position()-1" />
  <xsl:variable name="prevhouse_id"><xsl:value-of
select="../record[$prevrecpos]/house_id" /></xsl:variable>
  <xsl:if test="(1=position()) or ($prevhouse_id != house_id)">


The reasoning is just like in a procedural language, isn't it?


Have fun,

Paulo
(paulo.gaspar@krankikom.de)

> -----Original Message-----
> From: owner-xsl-list@mulberrytech.com
> [mailto:owner-xsl-list@mulberrytech.com]On Behalf Of Aleksandrs
> Jakovlevs
> Subject: RE: How to transform flat structure into hierarchical one?
>
>
> Sorry, but IMO it is absolutely another problem than what is discussed in
> the FAQ list you have suggested. I have no idea how many "rooms" in each
> "house". And I don't want to search all records containing "houses" with
> the same id, because it degrades performance significantly (BTW, I don't
> understand how SQL can return me anything else than the flat structure I
> have described). My question is what way should I create XSLT to make
> transformation as fast as possible using the fact that SQL result set is
> already grouped and sorted according to my needs?
>
> Thanks.
> Alex
>
> >Date: Tue, 6 Jun 2000 14:18:26 +0100
> >From: Kay Michael <Michael.Kay@icl.com>
> >Subject: RE: How to transform flat structure into hierarchical one?
> >
> >> I get a flat structure as a result of SQL query and want to
> >> transform it into hierarchical structure.
> >
> >In other words, you have a grouping problem. See Sorting and Grouping in
> the
> >Dave Pawson FAQ. Alternatively, change the SQL query so it does the
> grouping
> >for you.
> >
> >Mike Kay
>

****************************************************************************
*
I am adding the original posting here just to get all the thing in one
place:
****************************************************************************
*

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

E.g. initial XML could look like:

<record_set>
    <record>
        <house_id>h1</house_id>
        <room_id>r1</room_id>
    </record>
    <record>
        <house_id>h1</house_id>
        <room_id>r2</room_id>
    </record>
    <record>
        <house_id>h2</house_id>
        <room_id>r3</room_id>
    </record>
</record_set>

and the result of transformation could look like:

<house_list>
    <house>
        <id>h1</id>
        <rooms>
            <room>
                <id>r1</id>
            </room>
            <room>
                <id>r2</id>
            </room>
        </rooms>
    </house>
    <house>
        <id>h2</id>
        <rooms>
            <room>
                <id>r3</id>
            </room>
        </rooms>
    </house>
</house_list>

Thanks.
Alex


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

Index Nav: [Date Index] [Subject Index] [Author Index] [Thread Index]
Message Nav: [Date Prev] [Date Next] [Thread Prev] [Thread Next]