CODEWITHSUNDEEP
javaxmlxslt
Sam Barnum
Sam Barnum

Reputation: 10734

XSLT to generate sequential numbers which can be reset

<?xml version="1.0" encoding="UTF-8" ?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
    <ERRORCODE>0</ERRORCODE>
    <PRODUCT BUILD="01-25-2011" NAME="FileMaker" VERSION="Pro 11.0v3"/>
    <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="Export to Ledes" NAME="StateFarmLedes1998b.fp7" RECORDS="27" TIMEFORMAT="h:mm:ss a"/>
    <METADATA>
        <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="date of bill" TYPE="DATE"/>
        <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Invoice #" TYPE="NUMBER"/>
        <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Company Name" TYPE="TEXT"/>
    </METADATA>
    <RESULTSET FOUND="25">
        <ROW MODID="25" RECORDID="54">
            <COL>
                <DATA>20110707</DATA>
            </COL>
            <COL>
                <DATA>2949801</DATA>
            </COL>
            <COL>
                <DATA>Foo</DATA>
            </COL>
        </ROW>
        <ROW MODID="25" RECORDID="54">
            <COL>
                <DATA>20110707</DATA>
            </COL>
            <COL>
                <DATA>2949802</DATA>
            </COL>
            <COL>
                <DATA>Bar</DATA>
            </COL>
        </ROW>
    </RESULTSET>
</FMPXMLRESULT>

For the above XML, how would you write an XSLT transform which generates a pipe-delimited output, with a sequential "line item" count for each line item? The line item count should reset to 1 every time the invoice number changes.

Upvotes: 1

Views: 1689

Answers (1)

Dimitre Novatchev
Dimitre Novatchev

Reputation: 243549

This XSLT 2.0 transformation:

<xsl:stylesheet version="2.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:xs="http://www.w3.org/2001/XMLSchema"
 xmlns:x="http://www.filemaker.com/fmpxmlresult">

 <xsl:output method="text"/>

 <xsl:template match="/">
  <xsl:for-each-group select="/*/x:RESULTSET/x:ROW"
   group-adjacent="x:COL[2]/x:DATA">
    <xsl:apply-templates select="current-group()"/>
   </xsl:for-each-group>
 </xsl:template>

 <xsl:template match="x:ROW">
  <xsl:text>&#xA;</xsl:text>
  <xsl:sequence select=
  "string-join((xs:string(position()), x:COL/x:DATA), ',')"/>
 </xsl:template>
</xsl:stylesheet>

when applied on this XML document (essentially the provided one but added one more row to make it interesting):

<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
    <ERRORCODE>0</ERRORCODE>
    <PRODUCT BUILD="01-25-2011" NAME="FileMaker" VERSION="Pro 11.0v3"/>
    <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="Export to Ledes" NAME="StateFarmLedes1998b.fp7" RECORDS="27" TIMEFORMAT="h:mm:ss a"/>
    <METADATA>
        <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="date of bill" TYPE="DATE"/>
        <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Invoice #" TYPE="NUMBER"/>
        <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Company Name" TYPE="TEXT"/>
    </METADATA>
    <RESULTSET FOUND="25">
        <ROW MODID="25" RECORDID="54">
            <COL>
                <DATA>20110707</DATA>
            </COL>
            <COL>
                <DATA>2949801</DATA>
            </COL>
            <COL>
                <DATA>Foo</DATA>
            </COL>
        </ROW>
        <ROW MODID="25" RECORDID="54">
            <COL>
                <DATA>20110707</DATA>
            </COL>
            <COL>
                <DATA>2949801</DATA>
            </COL>
            <COL>
                <DATA>Foo</DATA>
            </COL>
        </ROW>
        <ROW MODID="25" RECORDID="54">
            <COL>
                <DATA>20110707</DATA>
            </COL>
            <COL>
                <DATA>2949802</DATA>
            </COL>
            <COL>
                <DATA>Bar</DATA>
            </COL>
        </ROW>
    </RESULTSET>
</FMPXMLRESULT>

produces the wanted, correct result:

1,20110707,2949801,Foo
2,20110707,2949801,Foo
1,20110707,2949802,Bar

Explanation:

Use of <xsl:for-each-group> with the group-adjacent attribute.

II. XSLT 1.0 Solution:

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:x="http://www.filemaker.com/fmpxmlresult"
 >
 <xsl:output method="text"/>

 <xsl:key name="kFollowing"
      match="x:ROW"
      use="generate-id(
            preceding-sibling::x:ROW
              [x:COL[2]/x:DATA
              =
               current()/x:COL[2]/x:DATA
              and
               not(preceding-sibling::x:ROW/x:COL[2]/x:DATA
                  =
                   current()/x:COL[2]/x:DATA
                  )
               ]
               [1]
                        )"
 />

 <xsl:template match=
  "x:ROW[not(x:COL[2]/x:DATA
            =
            preceding-sibling::x:ROW/x:COL[2]/x:DATA
            )
        ]
  ">
  <xsl:apply-templates mode="group"
       select=".|key('kFollowing',generate-id())"/>
 </xsl:template>

 <xsl:template match="x:ROW" mode="group">
  <xsl:value-of select=
   "concat('&#xA;',position())"/>

  <xsl:for-each select="x:COL/x:DATA">
   <xsl:value-of select="concat(',',.)"/>
  </xsl:for-each>
 </xsl:template>

 <xsl:template match="text()"/>
</xsl:stylesheet>

when this transformation is applied on the same XML document (shown above), the same correct result is produced:

1,20110707,2949801,Foo
2,20110707,2949801,Foo
1,20110707,2949802,Bar

Explanation:

  1. We use a template that matches every "head of group" x:ROW element.

  2. We have specified a key which indexes any x:ROW in a group by the generate-id() of the "head of the group". This is convenient to specify easily the whole group and to apply templates on its elements (in mode "group").

Upvotes: 2

Related Questions