[xsl] grouping by x number of elements

["kevin p" <kpear1@xxxxxxxxxxx>]

hi,

i'm trying to create pure xml excel workbooks from a manifest list of xml 
documents, and i actually have this working, the problem is that there are 
inherent rowlimitations, so i need to test for and create a new worksheet at 
each 65535 rows.  so if i have a xml manifest file as such:


<?xml version="1.0" encoding="UTF-8"?>
<workbook>
<filename>cat.xml</filename>
<filename>dog.xml</filename>
<filename>horse.xml</filename>
<filename>cow.xml</filename>
</workbook>

and those files all containing a similar syntax, with the exception of field 
names and quantities, these are based on any sql on a database, so there can 
be 10 fields, 3 fields, etc. all with different names, but "DATA_RESULT" and 
"record" are consistent across files, but there could be potentially a 
couple of million records in each file.

<DATA_RESULT>
<record>
  <field1>tabby</field1>
  <field2>1</field2>
  <field3>male</field3>
  <field4>Mar 01, 2004</field4>
</record>
<record>.... etc.
</DATA_RESULT>

with the following xslt:


<?xml version="1.0" encoding="UTF-8"?>
<?mso-application ="Excel.Sheet"?>
<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"; 
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40";>
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:template match="/">
<Workbook>
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author/>
<LastAuthor/>
<Created/>
<Company/>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
</ExcelWorkbook>
<xsl:for-each select="/workbook/filename">
<xsl:apply-templates select="document(.)/DATA_RESULT">
<xsl:with-param name="FILE" select=".">
</xsl:with-param>
</xsl:apply-templates>
</xsl:for-each>
</Workbook>


<xsl:template match="DATA_RESULT">
<xsl:param name="FILE"/>
<xsl:variable name="WORKSHEET">
<xsl:if test="substring($FILE, string-length($FILE) -3) = '.xml'" >
<xsl:value-of select="substring($FILE, 1, string-length($FILE) -4)">
</xsl:value-of>
</xsl:if>
</xsl:variable>

<!--- REENTRY POINT  -->

<Worksheet>
<xsl:attribute name="ss:Name">
<xsl:value-of select="$WORKSHEET"/>
</xsl:attribute>
<Table>
<xsl:for-each select="record">
<Row>
<xsl:for-each select="*">
<Cell>
<xsl:if test='number(.)'>
<Data ss:Type="Number">
<xsl:value-of select="."/>
</Data>
</xsl:if>
<xsl:if test="not(number(.))">
<Data ss:Type="String">
<xsl:value-of select="."/>
</Data>
</xsl:if>
</Cell>
</xsl:for-each>
</Row>
</xsl:for-each>
</Table>
</Worksheet>

</xsl:template>

</xsl:stylesheet>





Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" 
xmlns:html="http://www.w3.org/TR/REC-html40"; 
xmlns:o="urn:schemas-microsoft-com:office:office" 
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 
xmlns:x="urn:schemas-microsoft-com:office:excel">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author xmlns="urn:schemas-microsoft-com:office:office"/>
<LastAuthor xmlns="urn:schemas-microsoft-com:office:office"/>
<Created xmlns="urn:schemas-microsoft-com:office:office"/>
<Company xmlns="urn:schemas-microsoft-com:office:office"/>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"/>
<Worksheet ss:Name="cat">
<Table>
<Row>
<Cell>
<Data ss:Type="String">tabby</Data>
</Cell>
<Cell>
<Data ss:Type="Number">1</Data>
</Cell>
<Cell>
<Data ss:Type="String">male</Data>
</Cell>
<Cell>
<Data ss:Type="String">Mar 01, 2004</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Type="String">tigger</Data>
</Cell>
<Cell>
<Data ss:Type="Number">1</Data>
</Cell>
<Cell>
<Data ss:Type="String">male</Data>
</Cell>
<Cell>
<Data ss:Type="String">Mar 01, 2004</Data>
</Cell>
</Row>
</Table>
</Worksheet>
<!-- assuming over 65355 records
<Worksheet ss:Name="cat2">
<Table>
etc.
<Worksheet ss:Name="cat3">
<Table>
etc.
<Worksheet ss:Name="dog">
<Table>
etc.
<Worksheet ss:Name="dog2">
<Table>

Iv'e been able to test position at the record level and do something every 5 
records or whatever, but basically once I hit that position of 65,535, I 
want to re-write a new worksheet tag, and start reprocessing from record 
65,536 and no matter what kind of matching template i use, with absolute 
paths, instead of the above, I remain stuck down at the field_name/cell 
context so no records are found. this seems like it wouldn't be a unique 
problem, splitting on X number of records and starting a new page or xhtml 
table, but I didn't see anything in the archives off the bat.  Admitedly, 
I've just started hacking away a week ago, any ideas?

kp