xslt 1.0 - Multiple groupings of XML nodes

Question

I'm trying to group the input below by the destination and assortment values using muenchian-grouping which is new for me so I'm not sure how to do it properly. The input files will be much larger than this so performance is important.

<?xml version="1.0"?>
<ns0:Data xmlns:ns0="http://BizTalk_Projects.input">
    <transports>
        <destination>destination 1</destination>
        <assortment>Volvo_GA961</assortment>
        <quantity>10</quantity>
    </transports>
    <transports>
        <destination>destination 1</destination>
        <assortment>Volvo_GA961</assortment>
        <quantity>15</quantity>
    </transports>
    <transports>
        <destination>destination 1</destination>
        <assortment>Volvo_GA969</assortment>
        <quantity>15</quantity>
    </transports>   
    <transports>
        <destination>destination 1</destination>
        <assortment>Volvo_GA972</assortment>
        <quantity>5</quantity>
    </transports>   
    <transports>
        <destination>destination 1</destination>
        <assortment>Volvo_SA980</assortment>
        <quantity>20</quantity>
    </transports>   
    <transports>
        <destination>destination 2</destination>
        <assortment>Volvo_GA960</assortment>
        <quantity>10</quantity>
    </transports>
    <transports>
        <destination>destination 1</destination>
        <assortment>Nissan_GA963</assortment>
        <quantity>5</quantity>
    </transports>   
    <transports>
        <destination>destination 1</destination>
        <assortment>Nissan_GA963</assortment>
        <quantity>5</quantity>
    </transports>
</ns0:Data>

Expected output:

<?xml version="1.0" encoding="UTF-8"?>
<ns0:Destinations xmlns:ns0="http://BizTalk_Projects.output">
    <Destination>
        <name>destination 1</name>
        <assortment>
            <name>Volvo_GA</name>
            <row>
                <type>sumPerAssortment</type>
                <id>961</id>
                <totalQuantity>25</totalQuantity>
                <region>1</region>
            </row>
            <row>
                <type>sumPerAssortment</type>
                <id>969</id>
                <totalQuantity>15</totalQuantity>
                <region>1</region>
            </row>          
            <row>
                <type>sumPerAssortment</type>
                <id>972</id>
                <totalQuantity>5</totalQuantity>
                <region>2</region>
            </row>      
            <row>
                <type>sumPerRegion</type>
                <id />
                <totalQuantity>40</totalQuantity>
                <region>1</region>
            </row>          
            <row>
                <type>sumPerRegion</type>
                <id />
                <totalQuantity>5</totalQuantity>
                <region>2</region>
            </row>
            <row>
                <type>totalSum</type>
                <id />
                <totalQuantity>45</totalQuantity>
                <region />
            </row>          
        </assortment>
        <assortment>
            <name>Volvo_SA</name>
            <row>
                <type>sumPerAssortment</type>
                <id>980</id>
                <totalQuantity>20</totalQuantity>
                <region>3</region>
            </row>  
            <row>
                <type>sumPerRegion</type>
                <id />
                <totalQuantity>20</totalQuantity>
                <region>3</region>
            </row>  
            <row>
                <type>totalSum</type>
                <id />
                <totalQuantity>20</totalQuantity>
                <region />
            </row>                  
        </assortment>       
        <assortment>
            <name>Nissan_GA</name>
            <row>
                <type>sumPerAssortment</type>
                <id>963</id>
                <totalQuantity>10</totalQuantity>
                <region>1</region>
            </row>  
            <row>
                <type>sumPerRegion</type>
                <id />
                <totalQuantity>10</totalQuantity>
                <region>1</region>
            </row>  
            <row>
                <type>totalSum</type>
                <id />
                <totalQuantity>10</totalQuantity>
                <region />
            </row>          
        </assortment>
    </Destination>
    <Destination>
        <name>destination 2</name>
        <assortment>
            <name>Volvo_GA</name>
            <row>
                <type>sumPerAssortment</type>
                <id>960</id>
                <totalQuantity>10</totalQuantity>
                <region>1</region>
            </row>
            <row>
                <type>sumPerRegion</type>
                <id />
                <totalQuantity>10</totalQuantity>
                <region>1</region>
            </row>          
            <row>
                <type>totalSum</type>
                <id />
                <totalQuantity>10</totalQuantity>
                <region />
            </row>                  
        </assortment>       
    </Destination>  
</ns0:Destinations>

Note:

assortment number starting with 96 = region 1

assortment number starting with 97 = region 2

assortment number starting with 98 = region 3

Start of my XSLT:

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var"
                exclude-result-prefixes="msxsl var s0"
                version="1.0"
                xmlns:s0="http://BizTalk_Projects.input"
                xmlns:ns0="http://BizTalk_Projects.output">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:key name="destinationKey" match="transports" use="destination"/>
  <xsl:template match="/">
    <xsl:apply-templates select="/s0:Data" />
  </xsl:template>

  <xsl:template match="/s0:Data">
    <ns0:Destinations>
      <xsl:for-each select="transports[count(. | key('destinationKey',destination)[1]) = 1]">
        <Destination>
          <name>
            <xsl:value-of select="destination/text()" />
          </name>
          <xsl:for-each select="key('destinationKey',destination)">
            <assortment>
              <name>
                <xsl:value-of select="substring(assortment/text(),1,string-length(assortment)-3)" />
              </name>
            </assortment>
          </xsl:for-each>
        </Destination>
      </xsl:for-each>
    </ns0:Destinations>
  </xsl:template>
</xsl:stylesheet>

With this code, I'm getting this output (duplicate rows, but correct assortments for each destination);

<ns0:Destinations xmlns:ns0="http://BizTalk_Projects.output">
    <Destination>
        <name>destination 1</name>
        <assortment>
            <name>Volvo_GA</name>
        </assortment>
        <assortment>
            <name>Volvo_GA</name>
        </assortment>
        <assortment>
            <name>Volvo_GA</name>
        </assortment>
        <assortment>
            <name>Volvo_GA</name>
        </assortment>
        <assortment>
            <name>Volvo_SA</name>
        </assortment>
        <assortment>
            <name>Nissan_GA</name>
        </assortment>
        <assortment>
            <name>Nissan_GA</name>
        </assortment>
    </Destination>
    <Destination>
        <name>destination 2</name>
        <assortment>
            <name>Volvo_GA</name>
        </assortment>
    </Destination>
</ns0:Destinations>

Any suggestions on how I can solve this? Help is very appreciated!

Answer 1

It's difficult to see how exactly the output relates to the input. Try this as your starting point:

XSLT 1.0

<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:strip-space elements="*"/>

<xsl:key name="transports-by-destination" match="transports" use="destination" />
<xsl:key name="transports-by-assortment" match="transports" use="concat(destination, '|', assortment)" />

<xsl:template match="/*">
    <xsl:copy>
        <!-- for each unique destination -->
        <xsl:for-each select="transports[count(. | key('transports-by-destination', destination)[1]) = 1]">
            <Destination>
                <name>
                    <xsl:value-of select="destination"/>
                </name>
                <xsl:variable name="group" select="key('transports-by-destination', destination)" />
                <!-- for each unique assortment in this destination -->
                <xsl:for-each select="$group[count(. | key('transports-by-assortment', concat(destination, '|', assortment))[1]) = 1]">
                    <assortment>
                        <name>
                            <xsl:value-of select="assortment"/>
                        </name>
                        <!-- process this subgroup -->
                        <xsl:for-each select="key('transports-by-assortment', concat(destination, '|', assortment))" >
                            <row>
                                <!-- not sure what goes in here -->
                                <totalQuantity>
                                    <xsl:value-of select="quantity"/>
                                </totalQuantity>
                            </row>
                        </xsl:for-each>
                    </assortment>
                </xsl:for-each>
            </Destination>
        </xsl:for-each>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>