XSL Group by Country and Weight-CodePudding

I'm making a pricing chart from an XML that contains many rates (25k ). Each rate row specifies

A service name in @service
A country code (iso-3166) in @iso
A weight in @kg
The price in @pc

For each unique service I want to list

Every possible weight in column headings
List 1 row for every unique country
The 1st cell in the row shows the name (@iso) and then the @pc for the corresponding @kg

The problem I'm having, is that all of the country names are not appearing. Only 2 in Standard and Priority, and none in Express service.

        <h1>Standard</h1>
        <table>
                <tr>
                        <td>FR</td>
                </tr>
                <tr>
                        <td>CA</td>
                </tr>

        </table>
        <h1>Priority</h1>
        <table>
                <tr>
                        <td>DE</td>
                </tr>
                <tr>
                        <td>GB</td>
                </tr>
        </table>
        <h1>Express</h1>
        <table>
           NOTE--Should be 4 countries here
        </table>

I'm hoping to output something like this

 <h1>Standard</h1>
<table>
        <thead>
            <tr>
                <th>Country</th>
                <!-- One column for each unique weight -->
                <th>0.1</th>
                <th>0.15</th>
                <th>0.2</th>
                <th>0.25</th>
                <th>0.3</th>
            </tr>
        </thead>
        <tbody>
            <!-- One row for each unique country -->
            <tr>
                <td>FR</td>
                <!-- One column for each unique weight -->
                <td>202</td>
                <td>302</td>
                <td>402</td>
                <td>502</td>
                <td>602</td>
            </tr>
    
            <tr>
                <td>CA</td>
                <!-- One column for each unique weight -->
                <td>101</td>
                <td>151</td>
                <td>201</td>
                <td>251</td>
                <td>301</td>
            </tr>
    
            <!-- An so on for every unique country -->
        </tbody>
    </table>

Here's my XML / XSL

<xml>
 <rate service="Standard" iso="FR" kg="0.1" pc="202.0000"/>
 <rate service="Standard" iso="CA" kg="0.1" pc="101.0000" />
 <rate service="Standard" iso="CA" kg="0.15" pc="151.0000" />
 <rate service="Standard" iso="FR" kg="0.15" pc="302.0000" />
 <rate service="Standard" iso="FR" kg="0.2" pc="402.0000" />
 <rate service="Standard" iso="CA" kg="0.2" pc="201.0000" />
 <rate service="Standard" iso="CA" kg="0.25" pc="251.0000" />
 <rate service="Standard" iso="FR" kg="0.25" pc="502.0000" />
 <rate service="Standard" iso="FR" kg="0.3" pc="602.0000" />
 <rate service="Standard" iso="CA" kg="0.3" pc="301.0000" />

 <rate service="Priority" iso="CA" kg="0.5" pc="0.6000" />
 <rate service="Priority" iso="FR" kg="0.5" pc="0.6680" />
 <rate service="Priority" iso="DE" kg="0.5" pc="0.6070" />
 <rate service="Priority" iso="GB" kg="0.5" pc="0.7800" />
 <rate service="Priority" iso="GB" kg="0.75" pc="1.1050" />
 <rate service="Priority" iso="DE" kg="0.75" pc="0.8610" />
 <rate service="Priority" iso="FR" kg="0.75" pc="0.9470" />
 <rate service="Priority" iso="CA" kg="0.75" pc="0.8500" />
 <rate service="Priority" iso="CA" kg="1" pc="1.1000" />
 <rate service="Priority" iso="FR" kg="1" pc="1.2250" />
 <rate service="Priority" iso="DE" kg="1" pc="1.1140" />
 <rate service="Priority" iso="GB" kg="1" pc="1.4300" />

 <rate service="Express" iso="FR" kg="0.1" pc="64.6400" />
 <rate service="Express" iso="CA" kg="0.1" pc="101.0000" />
 <rate service="Express" iso="DE" kg="0.1" pc="129.2800" />
 <rate service="Express" iso="GB" kg="0.1" pc="147.6380" />
 <rate service="Express" iso="GB" kg="0.15" pc="220.7260" />
 <rate service="Express" iso="DE" kg="0.15" pc="193.2800" />
 <rate service="Express" iso="CA" kg="0.15" pc="151.0000" />
 <rate service="Express" iso="FR" kg="0.15" pc="96.6400" />
 <rate service="Express" iso="FR" kg="0.2" pc="128.6400" />
 <rate service="Express" iso="CA" kg="0.2" pc="201.0000" />
 <rate service="Express" iso="DE" kg="0.2" pc="257.2800" />
 <rate service="Express" iso="GB" kg="0.2" pc="293.8140" />
 <rate service="Express" iso="GB" kg="0.25" pc="366.9020" />
 <rate service="Express" iso="DE" kg="0.25" pc="321.2800" />
 <rate service="Express" iso="CA" kg="0.25" pc="251.0000" />
 <rate service="Express" iso="FR" kg="0.25" pc="160.6400" />
 <rate service="Express" iso="FR" kg="0.3" pc="192.6400" />
 <rate service="Express" iso="CA" kg="0.3" pc="301.0000" />
 <rate service="Express" iso="DE" kg="0.3" pc="385.2800" />
 <rate service="Express" iso="GB" kg="0.3" pc="439.9900" />
</xml>

And my XSL so far...

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:key name="service" match="rate" use="@service" />
<xsl:key name="iso" match="rate" use="@iso" />

<!-- Store off all the rates -->
<xsl:variable name='ratelist' select='//rate'/>

<xsl:template match="/">
<xml>
    <!-- Get a list of unique service names -->
    <xsl:variable name='servicelist' select="$ratelist[generate-id()=generate-id(key('service', @service))]"/>
    <xsl:for-each select='$servicelist'>
        <!-- Get the rates for just this service -->
        <xsl:variable name='servicerate' select="$ratelist[@service=current()/@service]"/>

        <!-- Get a list of unique country codes -->
        <xsl:variable name='isolist' select="$servicerate[generate-id()=generate-id(key('iso', @iso))]"/>

        <h1><xsl:value-of select='@service'/></h1>
        <table>
            <!-- Output 1 row for each unique country -->
            <xsl:for-each select='$isolist'>
                <tr>
                    <td><xsl:value-of select='@iso'/></td>
                </tr>
            </xsl:for-each>
        </table>
    </xsl:for-each>
</xml>
</xsl:template>
</xsl:stylesheet>

CodePudding user response：

As I mentioned in the comments, this is not a trivial undertaking. See if you can make sense of the following stylesheet:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:key name="rate-by-service" match="rate" use="@service" />
<xsl:key name="rate-by-country" match="rate" use="concat(@service, '|', @iso)" />
<xsl:key name="rate-by-weight" match="rate" use="concat(@service, '|', @kg)" />
<xsl:key name="value" match="rate" use="concat(@service, '|', @iso, '|', @kg)" />

<xsl:template match="/xml">
    <html>
        <!-- a group for each unique service -->
        <xsl:for-each select="rate[generate-id() = generate-id(key('rate-by-service', @service)[1])]">
            <xsl:variable name="group" select="key('rate-by-service', @service)" />
            <xsl:variable name="service" select="@service" />
            <!-- unique countries in current group  -->
            <xsl:variable name="countries" select="$group[generate-id() = generate-id(key('rate-by-country', concat(@service, '|', @iso))[1])]"/>
            <!-- unique weights in current group  -->
            <xsl:variable name="weights" select="$group[generate-id() = generate-id(key('rate-by-weight', concat(@service, '|', @kg))[1])]"/>
            <!-- output  -->
            <h1>
                <xsl:value-of select='@service'/>
            </h1>
            <table border="1">
                <thead>
                    <tr>
                        <th>Country</th>
                        <!-- a column for each unique weight in current group -->
                        <xsl:for-each select="$weights">
                            <th>
                                <xsl:value-of select='@kg'/>
                            </th>
                        </xsl:for-each>
                    </tr>
                </thead>
                <tbody>
                    <!-- a row for each unique country in current group -->
                    <xsl:for-each select="$countries">
                        <xsl:variable name="iso" select="@iso" />
                        <tr>
                            <td>
                                <xsl:value-of select='@iso'/>
                            </td>
                            <!-- a cell for each unique weight in current group -->
                            <xsl:for-each select="$weights">
                                <td>
                                    <xsl:value-of select="key('value', concat($service, '|', $iso, '|', @kg))/@pc"/>
                                </td>
                            </xsl:for-each>
                        </tr>
                    </xsl:for-each>
                </tbody>
            </table>
        </xsl:for-each>
            
    </html>
</xsl:template>

</xsl:stylesheet>

when applied to your input example, this will produce:

Result

<html>
   <h1>Standard</h1>
   <table border="1">
      <thead>
         <tr>
            <th>Country</th>
            <th>0.1</th>
            <th>0.15</th>
            <th>0.2</th>
            <th>0.25</th>
            <th>0.3</th>
         </tr>
      </thead>
      <tbody>
         <tr>
            <td>FR</td>
            <td>202.0000</td>
            <td>302.0000</td>
            <td>402.0000</td>
            <td>502.0000</td>
            <td>602.0000</td>
         </tr>
         <tr>
            <td>CA</td>
            <td>101.0000</td>
            <td>151.0000</td>
            <td>201.0000</td>
            <td>251.0000</td>
            <td>301.0000</td>
         </tr>
      </tbody>
   </table>
   <h1>Priority</h1>
   <table border="1">
      <thead>
         <tr>
            <th>Country</th>
            <th>0.5</th>
            <th>0.75</th>
            <th>1</th>
         </tr>
      </thead>
      <tbody>
         <tr>
            <td>CA</td>
            <td>0.6000</td>
            <td>0.8500</td>
            <td>1.1000</td>
         </tr>
         <tr>
            <td>FR</td>
            <td>0.6680</td>
            <td>0.9470</td>
            <td>1.2250</td>
         </tr>
         <tr>
            <td>DE</td>
            <td>0.6070</td>
            <td>0.8610</td>
            <td>1.1140</td>
         </tr>
         <tr>
            <td>GB</td>
            <td>0.7800</td>
            <td>1.1050</td>
            <td>1.4300</td>
         </tr>
      </tbody>
   </table>
   <h1>Express</h1>
   <table border="1">
      <thead>
         <tr>
            <th>Country</th>
            <th>0.1</th>
            <th>0.15</th>
            <th>0.2</th>
            <th>0.25</th>
            <th>0.3</th>
         </tr>
      </thead>
      <tbody>
         <tr>
            <td>FR</td>
            <td>64.6400</td>
            <td>96.6400</td>
            <td>128.6400</td>
            <td>160.6400</td>
            <td>192.6400</td>
         </tr>
         <tr>
            <td>CA</td>
            <td>101.0000</td>
            <td>151.0000</td>
            <td>201.0000</td>
            <td>251.0000</td>
            <td>301.0000</td>
         </tr>
         <tr>
            <td>DE</td>
            <td>129.2800</td>
            <td>193.2800</td>
            <td>257.2800</td>
            <td>321.2800</td>
            <td>385.2800</td>
         </tr>
         <tr>
            <td>GB</td>
            <td>147.6380</td>
            <td>220.7260</td>
            <td>293.8140</td>
            <td>366.9020</td>
            <td>439.9900</td>
         </tr>
      </tbody>
   </table>
</html>

Rendered: