Help me to select group based on maximum value

CCPuser · August 31st, 2012, 11:13 AM

Hi ,

I want to use XSL 2.0 to transform very big file of size 20 MB XML data to filter some of the elements. I downsized that input and pasted here. I want to select only one node based on highest number in it's element 'rank' from multiple product elements by grouping it's attribute 'id'. Output should look like shown below.

My XSL is selecting all productids and can't get the logic to group by id. I will appreciate if you can help me doing group by id attribute in this XSL.

Here is my input XML :

<?xml version="1.0"?>
<Products groupid="14567-567">
<Manager id="8789"/>
<Product id="998">
<rank>1</rank>
<LocationID>676</LocationID>
<CompanyID>A58</CompanyID>
<QAcheckID>Q349</QAcheckID>
</Product>
<Product id="998">
<rank>2</rank>
<LocationID>897</LocationID>
<CompanyID>A56</CompanyID>
<QAcheckID>Q349</QAcheckID>
</Product>
<Product id="998">
<rank>3</rank>
<LocationID>556</LocationID>
<CompanyID>A51</CompanyID>
<QAcheckID>Q349</QAcheckID>
</Product>
<Product id="998">
<rank>4</rank>
<LocationID>544</LocationID>
<CompanyID>A50</CompanyID>
<QAcheckID>Q349</QAcheckID>
</Product>
<Product id="754">
<rank>1</rank>
<LocationID>672</LocationID>
<CompanyID>A58</CompanyID>
<QAcheckID>Q349</QAcheckID>
</Product>
<Product id="754">
<rank>2</rank>
<LocationID>891</LocationID>
<CompanyID>A56</CompanyID>
<QAcheckID>Q350</QAcheckID>
</Product>
<Product id="442">
<rank>3</rank>
<LocationID>556</LocationID>
<CompanyID>A51</CompanyID>
<QAcheckID>Q350</QAcheckID>
</Product>
<Product id="998">
<rank>5</rank>
<LocationID>544</LocationID>
<CompanyID>A50</CompanyID>
<QAcheckID>Q349</QAcheckID>
</Product>
<ratingrank>A1</ratingrank>
<salesratio>.82</salesratio>
</Products>

output:

<?xml version="1.0"?>
<Products groupid="14567-567">
<Manager id="8789"/>
<Product id="998">
<rank>4</rank>
<LocationID>544</LocationID>
<CompanyID>A50</CompanyID>
<QAcheckID>Q349</QAcheckID>
</Product>
<Product id="754">
<rank>2</rank>
<LocationID>891</LocationID>
<CompanyID>A56</CompanyID>
<QAcheckID>Q350</QAcheckID>
</Product>
<Product id="442">
<rank>3</rank>
<LocationID>556</LocationID>
<CompanyID>A51</CompanyID>
<QAcheckID>Q350</QAcheckID>
</Product>
<Product id="998">
<rank>5</rank>
<LocationID>544</LocationID>
<CompanyID>A50</CompanyID>
<QAcheckID>Q349</QAcheckID>
</Product>
<ratingrank>A1</ratingrank>
<salesratio>.82</salesratio>
</Products>

my XSL:

<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:my="my:my">
<xsl:output omit-xml-declaration="yes" indent="yes"/>
<xsl:strip-space elements="*"/>

<xsl:template match="node()|@*">
<xsl:copy>
<xsl:apply-templates select="node()|@*"/>
</xsl:copy>
</xsl:template>

<xsl:template match=
"Product [@id]
[not(rank eq my:max(../Product/rank))
or
rank = preceding-sibling::Product /rank
]"/>

<xsl:function name="my:max" as="xs:string">
<xsl:param name="pValues" as="xs:string+"/>

<xsl:sequence select=
"if(not(distinct-values($pValues)[2]))
then $pValues[1]
else
for $vMax1 in
max(for $s in $pValues
return
xs:integer(substring-before(concat($s,'.'),'.'))
),

$vcntMax1Values in
count($pValues[starts-with(., string($vMax1))])

return
if($vcntMax1Values eq 1)
then $pValues[starts-with(., string($vMax1))]
[1]
else
for $submax in
(my:max(for $val in
$pValues[starts-with(., string($vMax1))]
[contains(., '.')],
$subval in substring-after($val, '.')
return
$subval
)
)
return
concat($vMax1, '.', $submax)

"/>
</xsl:function>
</xsl:stylesheet>

- Sunny

mhkay · August 31st, 2012, 11:36 AM

Please don't ask the same question on more than one forum.

CCPuser · August 31st, 2012, 12:03 PM

Sorry, I posed to get quick help here. I will use this forum only to post from now on. Could you please shed some light on my XSL darkenss in that problem?

samjudson · August 31st, 2012, 03:09 PM

If all your Products appear in the middle of your XML and are together then something like this would work:

Code:

<xsl:template match="Products">
	<xsl:copy>
		<xsl:apply-templates select="@*"/>
		<xsl:apply-templates select="Product[1]/preceding-sibling::*"/>
		<xsl:for-each-group select="Product" group-by="@id">
			<xsl:copy-of select="current-group()[rank=max(current-group()/rank)]"/>
		</xsl:for-each-group>
		<xsl:apply-templates select="Product[last()]/following-sibling::*"/>
	</xsl:copy>
</xsl:template>

If they are spread throughout the XML with other elements between them then it might not be so simple.