Search code examples
xmlxsltxslt-1.0aggregatetalend

Xslt 1.0 aggregate and sum

I need help to aggregate and sum in a XML file. I use Talend to aggregate this file, but the language used is the xslt 1.0.

This is my original XML:

<ROOT>
  <Id>1000021</Id>
  <Commandes>
    <Commande>
      <Id>12363806</Id>
      <CdeAteliers>
        <CdeAtelier>
          <AId>2</AId>
          <Cartons>
            <Carton>
              <Numero>0</Numero>
              <Produits>
                <Produit>
                  <CUG>48384</CUG>
                  <Intitule>MENES ALBERT Confiture extra 370 g</Intitule>
                  <Qty>4</Qty>
                  <QtyOrder>4</QtyOrder>
                  <EANs>
                    <EAN>316233</EAN>
                  </EANs>
                </Produit>
                <Produit>
                  <CUG>48384</CUG>
                  <Intitule>MENES ALBERT Confiture extra 370 g</Intitule>
                  <Qty>1</Qty>
                  <QtyOrder>1</QtyOrder>
                  <EANs>
                    <EAN>316233</EAN>
                  </EANs>
                </Produit>
              </Produits>
            </Carton>
          </Cartons>
        </CdeAtelier>
      </CdeAteliers>
    </Commande>
  </Commandes>
</ROOT>

I want this (Quantities are added according to the CUG, intitule or the EAN)

<ROOT>
  <Id>1000021</Id>
  <Commandes>
    <Commande>
      <Id>12363806</Id>
      <CdeAteliers>
        <CdeAtelier>
          <AId>2</AId>
          <Cartons>
            <Carton>
              <Numero>0</Numero>
              <Produits>
                <Produit>
                  <CUG>48384</CUG>
                  <Intitule>MENES ALBERT Confiture extra 370 g</Intitule>
                  <Qty>5</Qty>
                  <QtyOrder>5</QtyOrder>
                  <EANs>
                    <EAN>316233</EAN>
                  </EANs>
                </Produit>
              </Produits>
            </Carton>
          </Cartons>
        </CdeAtelier>
      </CdeAteliers>
    </Commande>
  </Commandes>
</ROOT>

This is my xslt (mapping):

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output indent="yes" />
   <xsl:template match="/">
            <ROOT>
                <Id><xsl:value-of select="ROOT/Id" /></Id>
                <Commandes> 
                <xsl:for-each select="ROOT/Commandes">
                    <Commande>          
                    <xsl:for-each select="Commande">        
                        <Id>
                            <xsl:value-of select="Id" />
                        </Id>
                        <CdeAteliers>
                        <xsl:for-each select="CdeAteliers">
                            <CdeAtelier>
                            <xsl:for-each select="CdeAtelier">
                                <AId><xsl:value-of select="AId" /></AId>
                                <Cartons>
                                <xsl:for-each select="Cartons">
                                    <Carton>
                                    <xsl:for-each select="Carton">
                                        <Numero><xsl:value-of select="Numero" /></Numero>
                                        <Produits>
                                        <xsl:for-each select="Produits" >
                                            <Produit>
                                            <xsl:for-each select="Produit">
                                                <CUG><xsl:value-of select="CUG" /></CUG>
                                                <Intitule><xsl:value-of select="Intitule" /></Intitule>
                                                <Qty><xsl:value-of select="Qty" /></Qty>
                                                <QtyOrder><xsl:value-of select="QtyOrder" /></QtyOrder>
                                                <EANs>
                                                <xsl:for-each select="EANs">
                                                    <EAN><xsl:value-of select="EAN" /></EAN>
                                                </xsl:for-each>
                                                </EANs>
                                            </xsl:for-each>
                                            </Produit>
                                        </xsl:for-each>
                                        </Produits>
                                    </xsl:for-each>
                                    </Carton>
                                </xsl:for-each>
                                </Cartons>
                            </xsl:for-each>
                            </CdeAtelier>
                        </xsl:for-each>
                        </CdeAteliers>
                    </xsl:for-each>
                    </Commande>
                </xsl:for-each>
                </Commandes>        
            </ROOT>
   </xsl:template>
</xsl:stylesheet>

I tried to sum the amounts but it does not increase the amounts. I saw the way XSLT/Muenchian grouping (https://en.wikipedia.org/wiki/XSLT/Muenchian_grouping) but i didn't understand how the system works.

I'm lost with this problem Thank you in advance for your help :)

EDIT:

Thank you Martin for you answer. I am very grateful !

But if it's my file is like this:

<ROOT>
  <Id>1000021</Id>
  <Commandes>
    <Commande>
      <Id>12363806</Id>
      <CdeAteliers>
        <CdeAtelier>
          <AId>2</AId>
          <Cartons>
            <Carton>
              <Numero>0</Numero>
              <Produits>
                <Produit>
                  <CUG>48384</CUG>
                  <Intitule>MENES ALBERT Confiture extra 370 g</Intitule>
                  <Qty>5</Qty>
                  <QtyOrder>5</QtyOrder>
                  <EANs>
                    <EAN>316233</EAN>
                  </EANs>
                </Produit>
                   <Produit>
                  <CUG>48384</CUG>
                  <Intitule>MENES ALBERT Confiture extra 370 g</Intitule>
                  <Qty>5</Qty>
                  <QtyOrder>5</QtyOrder>
                  <EANs>
                    <EAN>316233</EAN>
                  </EANs>
                </Produit>
              </Produits>
            </Carton>
          </Cartons>
        </CdeAtelier>
      </CdeAteliers>
    </Commande>
  </Commandes>
   <Commandes>
    <Commande>
      <Id>12363807</Id>
      <CdeAteliers>
        <CdeAtelier>
          <AId>2</AId>
          <Cartons>
            <Carton>
              <Numero>0</Numero>
              <Produits>
                <Produit>
                  <CUG>48384</CUG>
                  <Intitule>MENES ALBERT Confiture extra 370 g</Intitule>
                  <Qty>5</Qty>
                  <QtyOrder>5</QtyOrder>
                  <EANs>
                    <EAN>316233</EAN>
                  </EANs>
                </Produit>
                   <Produit>
                  <CUG>48384</CUG>
                  <Intitule>MENES ALBERT Confiture extra 370 g</Intitule>
                  <Qty>5</Qty>
                  <QtyOrder>5</QtyOrder>
                  <EANs>
                    <EAN>316233</EAN>
                  </EANs>
                </Produit>
              </Produits>
            </Carton>
          </Cartons>
        </CdeAtelier>
      </CdeAteliers>
    </Commande>
  </Commandes>
</ROOT>

I have this file output file:

<?xml version="1.0" encoding="UTF-8"?><ROOT>
  <Id>1000021</Id>
  <Commandes>
    <Commande>
      <Id>12363806</Id>
      <CdeAteliers>
        <CdeAtelier>
          <AId>2</AId>
          <Cartons>
            <Carton>
              <Numero>0</Numero>
              <Produits>
                <Produit>
                  <CUG>48384</CUG>
                  <Intitule>MENES ALBERT Confiture extra 370 g</Intitule>
                  <Qty>20</Qty>
                  <QtyOrder>20</QtyOrder>
                  <EANs>
                    <EAN>316233</EAN>
                  </EANs>
                </Produit>
              </Produits>
            </Carton>
          </Cartons>
        </CdeAtelier>
      </CdeAteliers>
    </Commande>
  </Commandes>
   <Commandes>
    <Commande>
      <Id>12363807</Id>
      <CdeAteliers>
        <CdeAtelier>
          <AId>2</AId>
          <Cartons>
            <Carton>
              <Numero>0</Numero>
              <Produits>


              </Produits>
            </Carton>
          </Cartons>
        </CdeAtelier>
      </CdeAteliers>
    </Commande>
  </Commandes>
</ROOT>

But i want this: All of the quantity is aggregated in the first control.

<?xml version="1.0" encoding="UTF-8"?><ROOT>
  <Id>1000021</Id>
  <Commandes>
    <Commande>
      <Id>12363806</Id>
      <CdeAteliers>
        <CdeAtelier>
          <AId>2</AId>
          <Cartons>
            <Carton>
              <Numero>0</Numero>
              <Produits>
                <Produit>
                  <CUG>48384</CUG>
                  <Intitule>MENES ALBERT Confiture extra 370 g</Intitule>
                  <Qty>10</Qty>
                  <QtyOrder>10</QtyOrder>
                  <EANs>
                    <EAN>316233</EAN>
                  </EANs>
                </Produit>
              </Produits>
            </Carton>
          </Cartons>
        </CdeAtelier>
      </CdeAteliers>
    </Commande>
  </Commandes>
   <Commandes>
    <Commande>
      <Id>12363807</Id>
      <CdeAteliers>
        <CdeAtelier>
          <AId>2</AId>
          <Cartons>
            <Carton>
              <Numero>0</Numero>
              <Produits>
                <CUG>48384</CUG>
                  <Intitule>MENES ALBERT Confiture extra 370 g</Intitule>
                  <Qty>10</Qty>
                  <QtyOrder>10</QtyOrder>
                  <EANs>
                    <EAN>316233</EAN>
                  </EANs>
              </Produits>
            </Carton>
          </Cartons>
        </CdeAtelier>
      </CdeAteliers>
    </Commande>
  </Commandes>
</ROOT>

How can I do ?

Solution

  • If you want to group Produits by CUG separately for each Commande, then you must define your key as:

    <xsl:key name="group" match="Produit" use="concat(ancestor::Commande/Id, '|', CUG)"/>

    and adjust the calls to the key() function accordingly - e.g.:

    <xsl:template match="Produit[generate-id() = generate-id(key('group', concat(ancestor::Commande/Id, '|', CUG))[1])]/Qty">

    Here's a complete stylesheet, which is also (IMHO) a bit simpler:

    XSLT 1.0

    <xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>

<xsl:key name="group" match="Produit" use="concat(ancestor::Commande/Id, '|', CUG)"/>

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

<xsl:template match="Produits">
    <xsl:copy>
        <xsl:apply-templates select="Produit[count(. | key('group', concat(ancestor::Commande/Id, '|', CUG))[1]) = 1]"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="Qty">
    <xsl:copy>
        <xsl:value-of select="sum(key('group', concat(ancestor::Commande/Id, '|', ../CUG))/Qty)"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="QtyOrder">
    <xsl:copy>
        <xsl:value-of select="sum(key('group', concat(ancestor::Commande/Id, '|', ../CUG))/QtyOrder)"/>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>