XSLT: Grouping and sorting....how?

Question

Hi... I have an XML file that looks like the following...

    <states>
 <state>
  <name>North Carolina</name>
  <city>Charlotte</city>
 </state>
 <state>
  <name>Alaska</name>
  <city>Fairbanks</city>
 </state>
 <state>
  <name>Virginia</name>
  <city>Leesburg</city>
 </state>
 <state>
  <name>Alaska</name>
  <city>Coldfoot</city>
 </state>
 <state>
  <name>North Carolina</name>
  <city>Harrisburg</city>
 </state>
 <state>
  <name>Virginia</name>
  <city>Ashburn</city>
 </state>  
</states>

I need to produce a report that lists each state, is alphabetical order with each city following.... such as ..

Alaska - Fairbanks, Coldfoot
North Carolina - Charlotte, Harrisburg
Virginia - Leesburg, Ashburn

(the cities do not have to be in alpha order, just the states)

I tried to solve this by doing a for-each on states/state, sorting it by name and processing it. Like this....

    <xsl:for-each select="states/state">
       <xsl:sort select="name" data-type="text" order="ascending"/>
       <xsl:value-of select="name"/>-<xsl:value-of select="city"/>
    </xsl:for-each>   

This gave me....

 Alaska - Fairbanks
 Alaska - Coldfoot
 North Carolina - Charlotte
 North Carolina - Harrisburg
 Virginia - Leesburg
 Virginia - Ashburn

The sorting worked, now I want to group. The only thing I could think to do was to compare to the previous state, since it is sorted, it should recognize if the state value has not changed. Like this...

<xsl:for-each select="states/state">
             <xsl:sort select="name" data-type="text" order="ascending"/>
  <xsl:variable name="name"><xsl:value-of select="name">
  <xsl:variable name="previous-name"><xsl:value-of select="(preceding-sibling::state)/name">
  <xsl:if test="$name != $previous-name">
   <br/><xsl:value-of select="name"/>-
  </xsl:if>
  <xsl:value-of select="city"/>
 </xsl:for-each>

Sadly, it appears that the preceding-sibling feature does not work well with the sort, so, the first time through (on the first Alaska) it saw the first North Carolina as a preceding sibling. This causes some weird results, which were not at all to my liking.

So, I am using XSLT1.0... Any thoughts/suggestions?

Thanks

Answer 1

For grouping in XSLT 1.0 you are probably going to have to use the Muenchian Method. It can be hard to understand but once you get it working you should be good to go.

Answer 2

This stylesheet:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
    <xsl:key name="kStateByName" match="state" use="name"/>
    <xsl:output method="text"/>
    <xsl:template match="/">
        <xsl:apply-templates
                   select="/*/state[count(.|key('kStateByName',name)[1])=1]">
            <xsl:sort select="name"/>
        </xsl:apply-templates>
    </xsl:template>
    <xsl:template match="state">
        <xsl:value-of select="concat(name,' - ')"/>
        <xsl:apply-templates select="key('kStateByName',name)/city"/>
    </xsl:template>
    <xsl:template match="city">
        <xsl:value-of select="concat(.,substring(', ', 
                                                 1 div (position()!=last())),
                                       substring('&#xA;',
                                                 1 div (position()=last())))"/>
    </xsl:template>
</xsl:stylesheet>

Output:

Alaska - Fairbanks, Coldfoot
North Carolina - Charlotte, Harrisburg
Virginia - Leesburg, Ashburn

Note: Grouping by State's name. Separator substring expression only works with a pull style (applying templates to city)

An XSLT 2.0 solution:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
    <xsl:output method="text"/>
    <xsl:template match="states">
        <xsl:for-each-group select="state" group-by="name">
            <xsl:sort select="name"/>
            <xsl:value-of select="concat(name,
                                         ' - ',
                                         string-join(current-group()/city,', '),
                                         '&#xA;')"/>
        </xsl:for-each-group>
    </xsl:template>
</xsl:stylesheet>

Just for fun, this XPath 2.0 expression:

string-join(for $state in distinct-values(/*/*/name)
            return concat($state,
                          ' - ',
                          string-join(/*/*[name=$state]/city,
                                      ', ')),
            '
')

Answer 3

This will return a distinct list of states:

  <xsl:for-each select="states/state">
     <xsl:sort select="name" />
     <xsl:if test="not(name = preceding-sibling::state/name)" >
         <xsl:value-of select="name" />
     </xsl:if>
  </xsl:for-each>

I used your example XML, built a little style sheet with the above, ran it through Xalan-j, and it returns:

Alaska North Carolina Virginia

So from there you should be able to apply a template or another for-each loop to pull the list of cities for each distinct state.

Chris