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?
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.

Geert BormansInformation 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

0
Geert BormansInformation ArchitectCommented:
I used Muenchian grouping to get the grouping correct

Doing so, you can use the stylesheet with any XSLT processor, including XSLT1
0
badtz7229Author Commented:
this didn't seem to work for the 2nd example. it only returned 1 Payment node instead of 2
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

badtz7229Author Commented:
also , the sum is wrong
0
badtz7229Author Commented:
can i use a  for-each-group ?
0
badtz7229Author Commented:
or maybe no for-each-group since i'm on xslt v1.0
0
Geert BormansInformation 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
0
Geert BormansInformation 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
0
Geert BormansInformation 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

0

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
Geert BormansInformation 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)
0
badtz7229Author Commented:
thx so much
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.