I came up with the below solution which seems to work but when the source
XML has about 400 <CUSTOMER> nodes this is taking a long time (approx 4
mins) to do the grouping. Any thoughts ?
Thanks
Manish
---------------------------------------------------------------------------------------------
<?xml version="1.0" ?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:key name="CATgrp" match="SITE" use="CATEGORY" />
<xsl:key name="TYPgrp" match="SITE" use="concat(CATEGORY,'~',TYPE)" />
<xsl:key name="DATEgrp" match="SITE"
use="concat(CATEGORY,'~',TYPE,'~',ORDER_DATE)" />
<xsl:template match="ORDER_DETAILS">
<table width="95%" border="1" cellpadding="1" cellspacing="0"
border-color="#ffffff">
<tr>
<th>Customer Name</th>
<th>Site Address</th>
<th>Site City</th>
<th>Site State</th>
<th>Site Zip</th>
<th>Account Number</th>
<th>Category</th>
<th>Type</th>
<th>Order Date</th>
<th>Price</th>
</tr>
<xsl:apply-templates select="CUSTOMER"/>
</table>
</xsl:template>
<xsl:template match="CUSTOMER">
<xsl:for-each select="SITE[count(. | key('CATgrp',
CATEGORY)[1]) = 1]">
<xsl:for-each select="key('CATgrp',CATEGORY)[generate-id() =
generate-id(key('TYPgrp',concat(CATEGORY,'~',TYPE)))]">
<xsl:for-each
select="key('TYPgrp',concat(CATEGORY,'~',TYPE))[generate-id() =
generate-id(key('DATEgrp',concat(CATEGORY,'~',TYPE,'~',ORDER_DATE)))]">
<!--xsl:for-each
select="key('TYPgrp',concat(CATEGORY,'~',TYPE))"-->
<xsl:for-each
select="key('DATEgrp',concat(CATEGORY,'~',TYPE,'~',ORDER_DATE))">
<tr>
<td><xsl:value-of
select="../NAME"/></td>
<td><xsl:value-of
select="ADDRESS/STREET"/></td>
<td><xsl:value-of
select="ADDRESS/CITY"/></td>
<td><xsl:value-of
select="ADDRESS/STATE"/></td>
<td><xsl:value-of
select="ADDRESS/ZIP"/></td>
<td><xsl:value-of
select="ACCOUNT_NUMBER"/></td>
<td><xsl:value-of
select="CATEGORY"/></td>
<td><xsl:value-of
select="TYPE"/></td>
<td><xsl:value-of
select="ORDER_DATE"/></td>
<td><xsl:value-of
select="PRICE"/></td>
</tr>
</xsl:for-each>
<xsl:variable name="orderTotal"
select="format-number(sum(key('DATEgrp',concat(CATEGORY,'~',TYPE,'~',ORDER_DATE))/PRICE[number(.)
= number(.)]),'#.##')"/>
<tr>
<td colspan="9" align="right"><b> <font face="Verdana" size="2"
color="#0000FF"><xsl:value-of select="CATEGORY"/> <xsl:text>
</xsl:text></font><u><xsl:value-of select="TYPE"/></u> Totals: </b></td>
<td class="total"><xsl:value-of
select="$orderTotal"/></td>
</tr>
</xsl:for-each>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
---------------------------------------------------------------------------------------------
From: "Manish Rambabu" <manishrb(_at_)hotmail(_dot_)com>
Reply-To: xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
To: xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
Subject: [xsl] 3 level Muenchian Grouping ?
Date: Tue, 20 Jan 2004 19:45:34 +0000
MIME-Version: 1.0
X-Originating-IP: [63.76.134.2]
X-Originating-Email: [manishrb(_at_)hotmail(_dot_)com]
X-Sender: manishrb(_at_)hotmail(_dot_)com
Received: from mc8-f12.hotmail.com ([65.54.253.148]) by mc8-s18.hotmail.com
with Microsoft SMTPSVC(5.0.2195.6824); Tue, 20 Jan 2004 11:48:33 -0800
Received: from biglist.com ([216.223.208.41]) by mc8-f12.hotmail.com with
Microsoft SMTPSVC(5.0.2195.6824); Tue, 20 Jan 2004 11:48:33 -0800
Received: from localhost (daemon(_at_)localhost)by biglist.com (8.8.8/8.8.5/BL-2)
with SMTP id OAA12789;Tue, 20 Jan 2004 14:46:33 -0500 (EST)
Received: by biglist.com (km_bm v1.13); Tue, 20 Jan 2004 14:46:22 -0500
Received: (from majordom(_at_)localhost)by biglist.com (8.8.8/8.8.5/BL-2) id
OAA12761;Tue, 20 Jan 2004 14:46:16 -0500 (EST)
X-Message-Info: TiNwL5K19MFkDu6vryF1VHoTosA/OzcNg3CxqAZeHKY=
Message-ID: <BAY2-F86LSAQV6uPDVn0000da2f(_at_)hotmail(_dot_)com>
X-OriginalArrivalTime: 20 Jan 2004 19:45:34.0931 (UTC)
FILETIME=[F8845230:01C3DF8D]
X-MailLoop: 1
Return-Path: owner-xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
Hello All,
Below are the XML and XSL files where I am able to do a 2 level Muenchian
grouping. Can someone please shed some light on how I can group further by
ORDER_DATE and calculate a subTotal for each Order Date. I know I need to
generate a 3rd concatenated key.
Thanks Much
M
---------------------------------------------------------------------------------------
<?xml version="1.0" ?>
<ORDER_DETAILS>
<CUSTOMER>
<NAME>John Smith</NAME>
<SITE>
<ADDRESS>
<STREET>111 Freedom Drive</STREET>
<CITY>Chicago</CITY>
<STATE>IL</STATE>
<ZIP>60111</ZIP>
</ADDRESS>
<ACCOUNT_NUMBER>12222</ACCOUNT_NUMBER>
<CATEGORY>1</CATEGORY>
<TYPE>AX</TYPE>
<ORDER_DATE>01/01/03</ORDER_DATE>
<PRICE>21.99</PRICE>
</SITE>
</CUSTOMER>
<CUSTOMER>
<NAME>Mary Jane</NAME>
<SITE>
<ADDRESS>
<STREET>16 Penn Ave NW</STREET>
<CITY>Washington DC</CITY>
<STATE>DC</STATE>
<ZIP>20333</ZIP>
</ADDRESS>
<ACCOUNT_NUMBER>67889</ACCOUNT_NUMBER>
<CATEGORY>1</CATEGORY>
<TYPE>AX</TYPE>
<ORDER_DATE>02/01/03</ORDER_DATE>
<PRICE>11.98</PRICE>
</SITE>
</CUSTOMER>
<CUSTOMER>
<NAME>Mark Anderson</NAME>
<SITE>
<ADDRESS>
<STREET>175 Commerce Park</STREET>
<CITY>San Francisco</CITY>
<STATE>CA</STATE>
<ZIP>93311</ZIP>
</ADDRESS>
<ACCOUNT_NUMBER>66145</ACCOUNT_NUMBER>
<CATEGORY>1</CATEGORY>
<TYPE>BX</TYPE>
<ORDER_DATE>01/31/03</ORDER_DATE>
<PRICE>11.98</PRICE>
</SITE>
</CUSTOMER>
<CUSTOMER>
<NAME>Chris Dewalt</NAME>
<SITE>
<ADDRESS>
<STREET>1344 Washington Park</STREET>
<CITY>San Francisco</CITY>
<STATE>CA</STATE>
<ZIP>93344</ZIP>
</ADDRESS>
<ACCOUNT_NUMBER>000234</ACCOUNT_NUMBER>
<CATEGORY>1</CATEGORY>
<TYPE>BX</TYPE>
<ORDER_DATE>01/31/03</ORDER_DATE>
<PRICE>41.00</PRICE>
</SITE>
</CUSTOMER>
<CUSTOMER>
<NAME>Sam Dewar</NAME>
<SITE>
<ADDRESS>
<STREET>123 Hunington Way</STREET>
<CITY>Mclean</CITY>
<STATE>VA</STATE>
<ZIP>20121</ZIP>
</ADDRESS>
<ACCOUNT_NUMBER>100988</ACCOUNT_NUMBER>
<CATEGORY>2</CATEGORY>
<TYPE>BX</TYPE>
<ORDER_DATE>01/31/03</ORDER_DATE>
<PRICE>11.00</PRICE>
</SITE>
</CUSTOMER>
<CUSTOMER>
<NAME>Peter Henderson</NAME>
<SITE>
<ADDRESS>
<STREET>421 RidgeTop Place</STREET>
<CITY>Sarosota</CITY>
<STATE>MI</STATE>
<ZIP>10121</ZIP>
</ADDRESS>
<ACCOUNT_NUMBER>800223</ACCOUNT_NUMBER>
<CATEGORY>1</CATEGORY>
<TYPE>AX</TYPE>
<ORDER_DATE>01/01/03</ORDER_DATE>
<PRICE>83.00</PRICE>
</SITE>
</CUSTOMER>
</ORDER_DETAILS>
----------------------------------------------------------------------------
---------------------------------------
<?xml version="1.0" ?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:key name="CATgrp" match="SITE" use="CATEGORY" />
<xsl:key name="TYPgrp" match="SITE" use="concat(CATEGORY,'~',TYPE)" />
<xsl:template match="ORDER_DETAILS">
<table width="95%" border="1" cellpadding="1" cellspacing="0"
border-color="#ffffff">
<tr>
<th>Customer Name</th>
<th>Site Address</th>
<th>Site City</th>
<th>Site State</th>
<th>Site Zip</th>
<th>Account Number</th>
<th>Category</th>
<th>Type</th>
<th>Order Date</th>
<th>Price</th>
</tr>
<xsl:apply-templates select="CUSTOMER"/>
</table>
</xsl:template>
<xsl:template match="CUSTOMER">
<xsl:for-each select="SITE[count(. | key('CATgrp', CATEGORY)[1]) = 1]">
<xsl:for-each select="key('CATgrp',CATEGORY)[generate-id() =
generate-id(key('TYPgrp',concat(CATEGORY,'~',TYPE)))]">
<xsl:for-each select="key('TYPgrp',concat(CATEGORY,'~',TYPE))">
<tr>
<td><xsl:value-of select="../NAME"/></td>
<td><xsl:value-of select="ADDRESS/STREET"/></td>
<td><xsl:value-of select="ADDRESS/CITY"/></td>
<td><xsl:value-of select="ADDRESS/STATE"/></td>
<td><xsl:value-of select="ADDRESS/ZIP"/></td>
<td><xsl:value-of select="ACCOUNT_NUMBER"/></td>
<td><xsl:value-of select="CATEGORY"/></td>
<td><xsl:value-of select="TYPE"/></td>
<td><xsl:value-of select="ORDER_DATE"/></td>
<td><xsl:value-of select="PRICE"/></td>
</tr>
</xsl:for-each>
<xsl:variable name="subTotal"
select="format-number(sum(key('TYPgrp',concat(CATEGORY,'~',TYPE))/PRICE[numb
er(.) = number(.)]),'#.##')"/>
<tr>
<td colspan="9" align="right"><b> <font face="Verdana" size="2"
color="#0000FF"><xsl:value-of select="CATEGORY"/> <xsl:text>
</xsl:text></font><u><xsl:value-of select="TYPE"/></u> Totals: </b></td>
<td class="total"><xsl:value-of select="$subTotal"/></td>
</tr>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
----------------------------------------------------------------------------
_________________________________________________________________
There are now three new levels of MSN Hotmail Extra Storage! Learn more.
http://join.msn.com/?pgmarket=en-us&page=hotmail/es2&ST=1
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
_________________________________________________________________
Check out the new MSN 9 Dial-up ? fast & reliable Internet access with prime
features! http://join.msn.com/?pgmarket=en-us&page=dialup/home&ST=1
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list