XSL: group unique nodes into one node and recalculate Amount

I have the following input

<Test>
	<Payment xmlns="http://services.com/v07">
		<Amount curr="USD">4.50</Amount>
		<FOP>
			<CreditCard>
				<Code>AX</Code>
				<Number>...............</Number>
				<ExpireDate>0715</ExpireDate>
			</CreditCard>
		</FOP>
		<FOI code="ID">
			<Number>1</Number>
		</FOI>
	</Payment>
	<Payment xmlns="http://services.com/v07">
		<Amount curr="USD">4.50</Amount>
		<FOP>
			<CreditCard>
				<Code>AX</Code>
				<Number>...............</Number>
				<ExpireDate>0715</ExpireDate>
			</CreditCard>
		</FOP>
		<FOI code="ID">
			<Number>2</Number>
		</FOI>
	</Payment>
	<Payment xmlns="http://services.com/v07">
		<Amount curr="USD">4.50</Amount>
		<FOP>
			<CreditCard>
				<Code>AX</Code>
				<Number>...............</Number>
				<ExpireDate>0715</ExpireDate>
			</CreditCard>
		</FOP>
		<FOI code="ID">
			<Number>3</Number>
		</FOI>
	</Payment>
</Test>

Open in new window


I want to group this into at most 2 Payment nodes. So if the CreditCard's used are the same then group them and recalculate the Amount.
So the above should result in:

<Test>
	<Payment xmlns="http://services.com/v07">
		<Amount curr="USD">13.50</Amount>
		<FOP>
			<CreditCard>
				<Code>AX</Code>
				<Number>...............</Number>
				<ExpireDate>0715</ExpireDate>
			</CreditCard>
		</FOP>
	</Payment>
</Test>

Open in new window


And just the same if there are multiple credit cards used then still group into at most 2 Payment nodes:
<Test>
	<Payment xmlns="http://services.com/v07">
		<Amount curr="USD">13.50</Amount>
		<FOP>
			<CreditCard>
				<Code>AX</Code>
				<Number>...............</Number>
				<ExpireDate>0715</ExpireDate>
			</CreditCard>
		</FOP>
		<FOI code="ID">
			<Number>1</Number>
		</FOI>
	</Payment>
	<Payment xmlns="http://services.com/v07">
		<Amount curr="USD">3.50</Amount>
		<FOP>
			<CreditCard>
				<Code>MC</Code>
				<Number>...............</Number>
				<ExpireDate>0715</ExpireDate>
			</CreditCard>
		</FOP>
		<FOI code="ID">
			<Number>2</Number>
		</FOI>
	</Payment>
	<Payment xmlns="http://services.com/v07">
		<Amount curr="USD">7.50</Amount>
		<FOP>
			<CreditCard>
				<Code>MC</Code>
				<Number>...............</Number>
				<ExpireDate>0718</ExpireDate>
			</CreditCard>
		</FOP>
		<FOI code="ID">
			<Number>3</Number>
		</FOI>
	</Payment>
</Test>


<Test>
	<Payment xmlns="http://services.com/v07">
		<Amount curr="USD">13.50</Amount>
		<FOP>
			<CreditCard>
				<Code>AX</Code>
				<Number>...............</Number>
				<ExpireDate>0715</ExpireDate>
			</CreditCard>
		</FOP>
	</Payment>
	<Payment xmlns="http://services.com/v07">
		<Amount curr="USD">11.00</Amount>
		<FOP>
			<CreditCard>
				<Code>MC</Code>
				<Number>...............</Number>
				<ExpireDate>0718</ExpireDate>
			</CreditCard>
		</FOP>
	</Payment>
</Test>

Open in new window

badtz7229Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gertone (Geert Bormans)Information ArchitectCommented:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet 
    xmlns:sc="http://services.com/v07"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">
    
    <xsl:key name="paym" match="sc:Payment" use="sc:FOP/sc:CreditCard/sc:Number"/>

    <xsl:strip-space elements="*"/>
    <xsl:output indent="yes"/>
    
    <xsl:template match="node()">
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="node()"/>
        </xsl:copy>
    </xsl:template>
    
    
    <xsl:template match="Test">
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="sc:Payment[generate-id() = generate-id(key('paym', sc:FOP/sc:CreditCard/sc:Number)[1])]"/>
        </xsl:copy>
    </xsl:template>
    
    <xsl:template match="sc:Payment">
        <xsl:element name="Payment" namespace="http://services.com/v07">
            <xsl:element name="Amount" namespace="http://services.com/v07">
                <xsl:value-of select="sum(key('paym', sc:FOP/sc:CreditCard/sc:Number)/sc:Amount)"/>
            </xsl:element>
            <xsl:copy-of select="sc:FOP"/>
        </xsl:element>
        
    </xsl:template>
    
    

</xsl:stylesheet>

Open in new window

Gertone (Geert Bormans)Information ArchitectCommented:
I used Muenchian grouping to get the grouping correct

Doing so, you can use the stylesheet with any XSLT processor, including XSLT1
badtz7229Author Commented:
this didn't seem to work for the 2nd example. it only returned 1 Payment node instead of 2
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

badtz7229Author Commented:
also , the sum is wrong
badtz7229Author Commented:
can i use a  for-each-group ?
badtz7229Author Commented:
or maybe no for-each-group since i'm on xslt v1.0
Gertone (Geert Bormans)Information ArchitectCommented:
Hi, I was not around yesterday, so sorry for my late response

I used muenchian in order to work on an XSLT 1.0 processor
Likely I have understood the specs not exactly the way you meant them.
The XSLT does exactly what I want it to do. So let's have a look
Gertone (Geert Bormans)Information ArchitectCommented:
There is two issues I think
- I used the credit card number solely to decide what is a unique card, I will add the expiry date to it. I will change that into a combination of expiry date and card number
(I will exclude the code from it because a card code can be derived from the number, so it does not add value
- I don't limit to two payment nodes maximum... but you did not specify what needs to happen if the grouping adds up to more than two cards
Gertone (Geert Bormans)Information ArchitectCommented:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet 
    xmlns:sc="http://services.com/v07"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">
    
    <xsl:key name="paym" match="sc:Payment" use="concat(sc:FOP/sc:CreditCard/sc:Number, sc:FOP/sc:CreditCard/sc:ExpireDate)"/>
    
    <xsl:strip-space elements="*"/>
    <xsl:output indent="yes"/>
    
    <xsl:template match="node()">
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="node()"/>
        </xsl:copy>
    </xsl:template>
    
    
    <xsl:template match="Test">
        <xsl:copy>
            <xsl:copy-of select="@*"/>
            <xsl:apply-templates select="sc:Payment[generate-id() = generate-id(key('paym', concat(sc:FOP/sc:CreditCard/sc:Number, sc:FOP/sc:CreditCard/sc:ExpireDate))[1])]"/>
        </xsl:copy>
    </xsl:template>
    
    <xsl:template match="sc:Payment">
        <xsl:element name="Payment" namespace="http://services.com/v07">
            <xsl:element name="Amount" namespace="http://services.com/v07">
                <xsl:copy-of select="sc:Amount/@*"/>
                <xsl:value-of select="format-number(sum(key('paym', concat(sc:FOP/sc:CreditCard/sc:Number, sc:FOP/sc:CreditCard/sc:ExpireDate))/sc:Amount), '#.00')"/>
            </xsl:element>
            <xsl:copy-of select="sc:FOP"/>
        </xsl:element>
        
    </xsl:template>
    
    
    
</xsl:stylesheet>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gertone (Geert Bormans)Information ArchitectCommented:
Note that the Amount is calculated right (unless you expect different currencies, that would make for another grouping)
The only issue I see with the amount sum calculation is that your source XML does not match the expected result (unless you can explain the anomaly to me)
badtz7229Author Commented:
thx so much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
CSS

From novice to tech pro — start learning today.