CODEWITHSUNDEEP
xmlxslt
Tom
Tom

Reputation: 2873

XSLT sort and number on specified, but non-unique data fields

I have an input XML file that is coming from FileMaker, so I have little control over the naming of XML elements, etc. in fact, this XML is basically an xml'ified CSV, with the data looking like this:

    <RESULTSET FOUND="1">
    <ROW MODID="0" RECORDID="123">
        <COL>
            <DATA>xxx</DATA>
        </COL>
        <COL>
            <DATA><<<</DATA>
        </COL>
        <COL>
            <DATA>6</DATA>
            <DATA>500</DATA>
            <DATA>40</DATA>
        </COL>
        <COL>
            <DATA>10</DATA>
            <DATA>41</DATA>
            <DATA>13</DATA>
        </COL>

Those last two fields are what I care about. As you can see, they are not sorted and AFAIK it is impossible to export them sorted from FM, so I need to sort them in my XSLT. These two are key-value pairs, it's just that FM exports them like this. Semantically, I have:

Their location within the XML is guaranteed, so at the moment my transformation is:

        <xsl:if test="fmp:COL[10]/fmp:DATA[1]">
            <entry key="key1"><xsl:value-of select="fmp:COL[10]/fmp:DATA[1]"/></entry>
            <entry key="value1"><xsl:value-of select="fmp:COL[11]/fmp:DATA[1]"/></entry>
        </xsl:if>
        <xsl:if test="fmp:COL[10]/fmp:DATA[2]">
            <entry key="key2"><xsl:value-of select="fmp:COL[10]/fmp:DATA[2]"/></entry>
            <entry key="value2"><xsl:value-of select="fmp:COL[11]/fmp:DATA[2]"/></entry>
        </xsl:if>
        (...)

That is ugly and not perfect but I'm stuck here. I would like to:

a) loop over them, generating the "key1", "key2" etc. names programmatically (so that it works for any number of entries) b) sort them from low to high of the first value (the key) so that the pairs are guaranteed to stay together

Both of these I can't for the life of me figure out. I've found enough about XSLT:sort that would apply if I had a unique element identifier, but nothing that does sorting of pairs.

I'm not an XSLT expert, but I've figured out everything else so far, so even pointers in the right direction would be helpful.

Upvotes: 0

Views: 53

Answers (2)

michael.hor257k
michael.hor257k

Reputation: 116992

Here's another way you could look at it:

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="value" match="fmp:COL[4]/fmp:DATA" use="concat(count(preceding-sibling::fmp:DATA), '|', generate-id(ancestor::fmp:ROW))" />

<xsl:template match="/fmp:FMPXMLRESULT">
    <output>
        <xsl:for-each select="fmp:RESULTSET/fmp:ROW">
            <record>
            
                <!-- other data -->

                <xsl:for-each select="fmp:COL[3]/fmp:DATA">
                    <xsl:sort select="." data-type="number"/>
                        <entry key="{.}">
                            <xsl:value-of select="key('value', concat(count(preceding-sibling::fmp:DATA), '|', generate-id(ancestor::fmp:ROW)))"/>
                        </entry>
                </xsl:for-each>
            </record>
        </xsl:for-each>
    </output>
</xsl:template>

</xsl:stylesheet>

Upvotes: 0

michael.hor257k
michael.hor257k

Reputation: 116992

Consider the following example:

XML

<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">

    <!-- omitted -->

    <RESULTSET>
        <ROW>
            <COL/>
            <COL/>
            <COL>
                <DATA>6</DATA>
                <DATA>500</DATA>
                <DATA>40</DATA>
            </COL>
            <COL>
                <DATA>10</DATA>
                <DATA>41</DATA>
                <DATA>13</DATA>
            </COL>
        </ROW>
    </RESULTSET>
</FMPXMLRESULT>

XSLT 1.0

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

<xsl:template match="/fmp:FMPXMLRESULT">
    <output>
        <xsl:for-each select="fmp:RESULTSET/fmp:ROW">
            <record>

                <!-- other data -->

                <xsl:variable name="pairs">
                    <xsl:for-each select="fmp:COL[3]/fmp:DATA">
                        <xsl:variable name="i" select="position()" />
                        <entry key="{.}">
                            <xsl:value-of select="../../fmp:COL[4]/fmp:DATA[$i]"/>
                        </entry>
                    </xsl:for-each>
                </xsl:variable>
                <xsl:for-each select="exsl:node-set($pairs)/entry">
                    <xsl:sort select="@key" data-type="number"/>
                    <xsl:copy-of select="."/>
                </xsl:for-each>
            </record>
        </xsl:for-each>
    </output>
</xsl:template>

</xsl:stylesheet>

Result

<?xml version="1.0" encoding="utf-8"?>
<output>
  <record>
    <entry key="6">10</entry>
    <entry key="40">13</entry>
    <entry key="500">41</entry>
  </record>
</output>

A side note:

AFAIK it is impossible to export them sorted from FM

That depends on where is this data coming from: if it's related records, then you certainly can sort the relationship; if it's repeating fields (hopefully not!), then it might be more difficult - though I don't think impossible.

In any case, if the only purpose of the sort is for the export, then IMHO it is good practice to leave the sorting to the stylesheet, as shown above.

Upvotes: 1

Related Questions