Flat XML to Unique Nested XML using XLST 1.0

Question

I'm trying to get the output from a filemaker database export into a form usable by a web application. I've attempted to write an XLST (1.0) to do the transformation. Filemaker output is essentially a spreadsheet (rows x columns), and I want to convert this based on relationships. Essentially for every tank "Col[1]" I want data collected on a certain date "Col[2]" associated with this "Col[3]".

Using XSLT, I've been able to get the unique dates, or the unique tanks, but not multiple dates for each tank.

I've included my XLST, as well as a "simplified" version of what the input XML looks like, and what I'm hoping to get out.

Many thanks in advance for any advise!

XLST so Far

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult" version="1.0" exclude-result-prefixes="fmp">
<xsl:output method="xml" version="1.0" encoding="windows-1251" indent="yes"/>

<xsl:key name="fishdate" match="fmp:ROW" use="fmp:COL[3]"/>
<xsl:key name="tanknumber" match="fmp:ROW" use="fmp:COL[9]"/>

<xsl:template match="/">
    <fishroom>
        <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[generate-id()=generate-id(key('tanknumber',fmp:COL[9]))]">

                <!-- <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[generate-id()=generate-id(key('fishdate',fmp:COL[3]))]"> -->
                        <date> <xsl:value-of select="fmp:COL[3]/fmp:DATA"/>
                            <tank> 
                                <temp><xsl:value-of select="fmp:COL[10]/fmp:DATA"/></temp>
                            </tank>
                        </date>
                 <!-- </xsl:for-each> -->
        </xsl:for-each>

    </fishroom>
</xsl:template>
</xsl:stylesheet>

Input XML:

<row>
    <col>1/1/14</col>
    <col>tank1</col>
    <col>data1</col
</row>
<row>
    <col>1/1/14</col>
    <col>tank2</col>
    <col>data3</col>
</row>
<row>
    <col>1/2/14</col>
    <col>tank1</col>
    <col>data2</col>
</row>

Desired Output:

<tank1>
    <date>1/1/14
         <somedata>data2</somedata>
    </date>
    <date>1/2/14
         <somedata>data1</somedata>
   </date>
</tank1>
<tank2>
     <date>1/2/14
         <somedata>data3</somedata>
      </date>
</tank2>

Answer 1

Your attempt has several issues, the most serious ones being:

1. The 'fishdate' key must use a concatenation of tank and date. Otherwise you will be sub-grouping all records under each tank;
2. After grouping bytank, you must continue and subgroup the records in the current group. These are accessible by the same key you used to determine the distinct tanks.
3. You left out the third step where you actually list the data in each subgroup.

Given the following example input:

XML

<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
    <!-- omitted -->
    <RESULTSET FOUND="4">
        <ROW MODID="1" RECORDID="1">
            <COL><DATA>tank1</DATA></COL>
            <COL><DATA>1/1/2014</DATA></COL>
            <COL><DATA>data1</DATA></COL>
        </ROW>
        <ROW MODID="1" RECORDID="2">
            <COL><DATA>tank2</DATA></COL>
            <COL><DATA>1/1/2014</DATA></COL>
            <COL><DATA>data3</DATA></COL>
        </ROW>
        <ROW MODID="1" RECORDID="3">
            <COL><DATA>tank1</DATA></COL>
            <COL><DATA>1/2/2014</DATA></COL>
            <COL><DATA>data2</DATA></COL>
        </ROW>
        <ROW MODID="1" RECORDID="4">
            <COL><DATA>tank1</DATA></COL>
            <COL><DATA>1/1/2014</DATA></COL>
            <COL><DATA>data4</DATA></COL>
        </ROW>
    </RESULTSET>
</FMPXMLRESULT>

the following stylesheet:

XSLT 1.0

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:fmp="http://www.filemaker.com/fmpxmlresult"
exclude-result-prefixes="fmp">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:key name="tanknumber" match="fmp:ROW" use="fmp:COL[1]"/>
<xsl:key name="fishdate" match="fmp:ROW" use="concat(fmp:COL[1], '|', fmp:COL[2])"/>

<xsl:template match="/">
    <fishroom>
        <!-- create a group for each distinct tank -->
        <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[generate-id()=generate-id(key('tanknumber',fmp:COL[1]))]">
            <xsl:element name="{fmp:COL[1]/fmp:DATA}">
               <!-- create a subgroup for each distinct date within a tank -->
               <xsl:for-each select="key('tanknumber', fmp:COL[1])[generate-id()=generate-id(key('fishdate',concat(fmp:COL[1], '|', fmp:COL[2])))]"> 
                    <date>
                        <xsl:value-of select="fmp:COL[2]/fmp:DATA"/>
                        <!-- enumerate the values in the current subgroup -->
                         <xsl:for-each select="key('fishdate',concat(fmp:COL[1], '|', fmp:COL[2]))">
                             <value>
                                <xsl:value-of select="fmp:COL[3]/fmp:DATA"/>
                            </value>
                        </xsl:for-each>
                    </date>
                </xsl:for-each>
            </xsl:element>
        </xsl:for-each>
    </fishroom>
</xsl:template>

</xsl:stylesheet>

will return this result:

<?xml version="1.0" encoding="UTF-8"?>
<fishroom>
   <tank1>
      <date>1/1/2014<value>data1</value>
         <value>data4</value>
      </date>
      <date>1/2/2014<value>data2</value>
      </date>
   </tank1>
   <tank2>
      <date>1/1/2014<value>data3</value>
      </date>
   </tank2>
</fishroom>

Note:
I don't know what your target application's requirements are. The XML format of the output above has two weaknesses that generally one tries to avoid:

1. Sequential numbering of sibling element names;
2. Mixed content.