Search code examples
xmlcsvxsltxslt-grouping

get with csl equal product Id from xml in on row csv and sum size in one column

I have an xml file and have to parse it to csv. In this case I like to delete all double parent_product_id and sum the size and stock_quantity in one column. Unfortunately I am not able to find a solution how to group it up when parent_product_id is equal and how to sum size and stock_quantity. Thanks for every help that I can get. 

<?xml version="1.0" encoding="UTF-8" ?>
  <products> 
   <product>
 <parent_product_id>RMA021-05J</parent_product_id>
        <brand_name>Regatta</brand_name>
        <colour>blauw</colour>
        <currency>EUR</currency>
        <rrp_price>29.95 EUR</rrp_price>
        <size>XXL</size>
        <stock_quantity>2</stock_quantity>
    </product> 
  <product>
 <parent_product_id>RMA021-05J</parent_product_id>
        <brand_name>Regatta</brand_name>
        <colour>blauw</colour>
        <currency>EUR</currency>
        <rrp_price>29.95 EUR</rrp_price>
        <size>XL</size>
        <stock_quantity>4</stock_quantity>
    </product>
  </products>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
<xsl:output method="text" omit-xml-declaration="yes" encoding="UTF-8"/>
<xsl:strip-space elements="*"/>
<xsl:template match="products">
<xsl:for-each select="product">
<xsl:text>"</xsl:text>
 <xsl:value-of select="normalize-space(parent_product_id)"/><xsl:text>"|"</xsl:text>
 <xsl:value-of select="normalize-space(brand_name)"/><xsl:text>"|"</xsl:text>
 <xsl:value-of select="normalize-space(colour)"/><xsl:text>"|"</xsl:text>
 <xsl:value-of select="normalize-space(currency)"/><xsl:text>"|"</xsl:text>
 <xsl:value-of select="normalize-space(rrp_price)"/><xsl:text>"|"</xsl:text>
  <xsl:value-of select="normalize-space(size)"/><xsl:text>"|"</xsl:text>
 <xsl:value-of select="normalize-space(stock_quantity)"/><xsl:text>"|"</xsl:text>
<xsl:text>"
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
That's how my result look like now: 
"RMA021-05J"|"Regatta"|"blauw"|"EUR"|"29.95 EUR"|"XXL"|"2"|""
"RMA021-05J"|"Regatta"|"blauw"|"EUR"|"29.95 EUR"|"XL"|"4"|""

The result that I need has to look like this: 

"RMA021-05J"|"Regatta"|"blauw"|"EUR"|"29.95 EUR"|"XXL,XL"|"6"|""

Solution

  • Try this:

    <xsl:template match="products">
    <xsl:for-each-group select="product" group-by="parent_product_id">
        <xsl:text>"</xsl:text>
        <xsl:value-of select="parent_product_id"/><xsl:text>"|"</xsl:text>
        <xsl:value-of select="brand_name"/><xsl:text>"|"</xsl:text>
        <xsl:value-of select="colour"/><xsl:text>"|"</xsl:text>
        <xsl:value-of select="currency"/><xsl:text>"|"</xsl:text>
        <xsl:value-of select="rrp_price"/><xsl:text>"|"</xsl:text>
        <xsl:value-of select="distinct-values(current-group()/size)" separator=","/><xsl:text>"|"</xsl:text>
        <xsl:value-of select="sum(current-group()/stock_quantity)"/><xsl:text>"|"</xsl:text>
        <xsl:text>"&#xa;</xsl:text>
    </xsl:for-each-group>
</xsl:template>

    you can see transformation at https://xsltfiddle.liberty-development.net/94hvTyS