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!
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>