grouping by x number of elements

Play the video

Subject: grouping by x number of elements
From: "kevin p" <kpear1@xxxxxxxxxxx>
Date: Fri, 08 Jul 2005 12:41:17 +0000

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>

<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

Current Thread
grouping by x number of elements kevin p - 8 Jul 2005 12:41:44 -0000 <= David Carlisle - 8 Jul 2005 12:55:48 -0000 <Possible follow-ups> kevin p - 12 Jul 2005 20:09:32 -0000 David Carlisle - 12 Jul 2005 20:53:30 -0000

<- Previous	Index	Next ->
RE: Concatenating the attribu, Mukul Gandhi	Thread	Re: grouping by x number of e, David Carlisle
Re: Concatenating the attribu, Mukul Gandhi	Date	Re: grouping by x number of e, David Carlisle
	Month

PURCHASE STYLUS STUDIO ONLINE TODAY!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Download The World's Best XML IDE!

Accelerate XML development with our award-winning XML IDE - Download a free trial today!

Subscribe in XML format

RSS 2.0
Atom 0.3

XML Editor - Download a 15 Day Free Trial Now >

See What's New in Stylus Studio >

Buy Stylus Studio - XML Editor - Now >