Grouping XML data with XSLT

Hello.

I’m pulling my hair here. I may need help from someone familiar with XSLT.

One of our systems generates XML data what we can obtain and process.

One of these is an XML file with data that I have historically shown in a table for payment calendars. I have need to duplicate the functionality of this summarised table in XSLT, as it needs to be sent in XML to a different system for an automated report.

For the life of me, I can’t figure out how to make such a summary transformation in XSLT so the end XML is correct.

I have XML like this:

<?xml version="1.0" encoding="utf-8"?> <Proposal_AmmortisationXML> <Amortisation> <CF No="1" Month="0" Date="12/12/2018" Payment="300" FeesAPR="200"></CF> <CF No="2" Month="1" Date="12/01/2019" Payment="300" FeesAPR="0"></CF> <CF No="3" Month="2" Date="12/02/2019" Payment="300" FeesAPR="0"></CF> <CF No="4" Month="3" Date="12/03/2019" Payment="300" FeesAPR="0"></CF> <CF No="5" Month="4" Date="12/04/2019" Payment="200" FeesAPR="0"></CF> <CF No="6" Month="3" Date="12/05/2019" Payment="300" FeesAPR="0"></CF> <CF No="7" Month="5" Date="12/06/2019" Payment="3000" FeesAPR="0"></CF> </Amortisation> </Proposal_AmmortisationXML>

And I need a final table that has this data (I’ve simplified down to the specific data I need):

1 first payment of 500€ (300€ rental plus 200€ in fees) on 12/12/2018 3 payments of 300€ starting on 12/01/2019 1 payment of 200€ on 12/04/2019 1 payment of 300€ on 12/04/2019 1 last payment of 3000€ on 12/05/2019

Essentially, what I can’t figure out is how can I in XSLT group by consecutive lines that have the same sum of two fields, showing the sum of that amount for the group, indicating the number of lines that has the same amount and the date shown in the first of the group and isolating what’s the first and last.

I have offered to do this in Xojo and in other languages, but it seems the integration chosen uses XSLT (which I’m not as familiar with as I’d wish I was). I can learn XSLT in the mid/long-term but I need to deliver this in the short term and this is the only part that has me stumped of the report.

With this:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:template match="/Proposal_AmmortisationXML">
		<Report>
			<xsl:apply-templates select="Amortisation"/>
		</Report>
	</xsl:template>
	
	<xsl:template match="Amortisation">
		<Proposal>
			<xsl:attribute name="number" select="position()"/>
<!-- The first payment -->			
			<xsl:apply-templates select="CF[1]" mode="others"/>
			
			
<!-- Get the payment value for the sequence -->
			<xsl:variable name="p" select="CF[2]/@Payment"/>
<!-- Create the group info data -->
			<Group>
<!-- The number of consecutive elements -->
				<xsl:attribute name="no" select="count(CF[@Payment=$p and @No!=1  and @No=(preceding-sibling::CF[@ Payment =300][1]/@No+1)])"/>
				<xsl:apply-templates select="CF[2]" mode="info"/>
			</Group>

<!-- Get the next payment in list and the final one -->
			<xsl:variable name="lastGroup" select="CF[@Payment=$p and @No!=1  and @No=(preceding-sibling::CF[@ Payment =300][1]/@No+1)][last()]/@No"/>
<!--Write them -->
			<xsl:apply-templates select="CF[position()>$lastGroup]" mode="others"/>
		</Proposal>
	</xsl:template>
	
<!-- Models for the items -->	
	<xsl:template match="CF" mode="others">
		<xsl:choose>
			<xsl:when test="@No=1">
				<First>
					<xsl:apply-templates select="." mode="info"/>
				</First>
			</xsl:when>
			<xsl:when test="position()=last()">
				<Last>
					<xsl:apply-templates select="." mode="info"/>
				</Last>
			</xsl:when>
			<xsl:otherwise>
				<Other>
					<xsl:apply-templates select="." mode="info"/>
				</Other>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	
<!-- Model for the infos -->
	<xsl:template match="CF" mode="info">
		<xsl:attribute name="total" select="number(@Payment)+number(@FeesAPR)"/>
		<xsl:attribute name="rental" select="@Payment"/>
		<xsl:attribute name="fee" select="@FeesAPR"/>
		<xsl:attribute name="on" select="@Date"/>	
	</xsl:template>
</xsl:stylesheet>

The result will be:

<Report> <Proposal number="1"> <First total="500" rental="300" fee="200" on="12/12/2018"/> <Group no="3" total="300" rental="300" fee="0" on="12/01/2019"/> <Other total="200" rental="200" fee="0" on="12/04/2019"/> <Other total="300" rental="300" fee="0" on="12/05/2019"/> <Last total="3000" rental="3000" fee="0" on="12/06/2019"/> </Proposal> </Report>

Thank you!

I notice your code is tied directly to the example but I understand what you’re doing and I can probably make my own version to accept arbitrary amounts of payments (and multiple groups of payment) with other amounts.

I see this is pretty different to what I had thought of doing (I kept trying to find out how to make loops and variables I could update in the loop. I now realize I kept trying to do this as if XSL was a procedural language rather than a functional one.

I really appreciate it.