XSLT - Group and Sum in multiple childnode-CodePudding

Trying to make an XSLT transformation of a BalanceList xml.

The Input is structured with Child elements to drill down the detail from Product -> Batch -> Locations (with StockResevationKey).

The Output needs to be flattend with repeating product elements grouped and summed by BatchId and StockResevationKey (if exists)

So the "unique product Quantity" would be identified by: ExtProductId BatchId StockResevationKey (Both BatchId and StockResevationKey is'nt always present on all products).

Input xml:

<?xml version="1.0" encoding="utf-8"?>
<BalanceList>
    <TransactionId>20642</TransactionId>
    <Products>
        <Product>
            <Quantity>100.000</Quantity>     
            <ProductText>Item_1</ProductText>
            <ExtProductId>0320366</ExtProductId>
            <ExtId/>
            <Batches>
                <Batch>
                    <Quantity>80.000</Quantity>       
                    <BatchId>1234</BatchId>
                    <ProductLocations>
                        <ProductLocation>
                            <ExtLocationId>100362-01-01</ExtLocationId>
                            <Quantity>20.000</Quantity>                         
                            <StockReservationKey>1111</StockReservationKey>
                        </ProductLocation>
                        <ProductLocation>
                            <ExtLocationId>100359-01-01</ExtLocationId>
                            <Quantity>40.000</Quantity>
                            <StockReservationKey>1111</StockReservationKey>
                        </ProductLocation>
                        <ProductLocation>
                            <ExtLocationId>100368-01-01</ExtLocationId>
                            <Quantity>20.000</Quantity>
                            <StockReservationKey>2222</StockReservationKey>
                        </ProductLocation>
                    </ProductLocations>
                </Batch>
                <Batch>
                    <Quantity>20.000</Quantity>
                    <BatchId>2345</BatchId>
                    <ProductLocations>
                        <ProductLocation>
                            <ExtLocationId>100897-01-01</ExtLocationId>
                            <Quantity>10.000</Quantity>
                            <StockReservationKey>2222</StockReservationKey>
                        </ProductLocation>
                        <ProductLocation>
                            <ExtLocationId>104567-01-01</ExtLocationId>
                            <Quantity>10.000</Quantity>
                            <StockReservationKey />
                        </ProductLocation>
                    </ProductLocations>
                </Batch>
            </Batches>
        </Product>
        <Product>
            <Quantity>200.000</Quantity>
            <ProductText>Item_2</ProductText>
            <ExtProductId>0358523</ExtProductId>
            <ExtId/>
            <Batches>
                <Batch>
                    <Quantity>100.000</Quantity>
                    <BatchId>222</BatchId>
                    <ProductLocations>
                        <ProductLocation>
                            <ExtLocationId>100365-01-01</ExtLocationId>
                            <Quantity>100.000</Quantity>
                            <StockReservationKey/>
                        </ProductLocation>
                    </ProductLocations>
                </Batch>
                <Batch>
                    <Quantity>100.000</Quantity>
                    <BatchId>333</BatchId>
                    <ProductLocations>
                        <ProductLocation>
                            <ExtLocationId>100399-01-01</ExtLocationId>
                            <Quantity>100.000</Quantity>
                            <StockReservationKey/>
                        </ProductLocation>
                    </ProductLocations>
                </Batch>
            </Batches>
        </Product>
    </Products>
</BalanceList>

Wanted Result:

<?xml version="1.0" encoding="utf-8"?>
<BalanceList>
    <TransactionId>20642</TransactionId>
    <Product>
        <ProductText>Item_1</ProductText>
        <ExtProductId>0320366</ExtProductId>  --(Concat key = 0320366 1234 1111)
        <Quantity>60.000</Quantity>
        <BatchId>1234</BatchId>
        <StockReservationKey>1111</StockReservationKey>
    </Product>
    <Product>
        <ProductText>Item_1</ProductText>
        <ExtProductId>0320366</ExtProductId>  --(Concat key = 0320366 1234 2222)
        <Quantity>20.000</Quantity>
        <BatchId>1234</BatchId>
        <StockReservationKey>2222</StockReservationKey>
    </Product>
    <Product>
        <ProductText>Item_1</ProductText>
        <ExtProductId>0320366</ExtProductId>   --(Concat key = 0320366 2345 2222)
        <Quantity>10.000</Quantity>
        <BatchId>2345</BatchId>
        <StockReservationKey>2222</StockReservationKey>
    </Product>
    <Product>
        <ProductText>Item_1</ProductText>
        <ExtProductId>0320366</ExtProductId>   --(Concat key = 0320366 2345)
        <Quantity>10.000</Quantity>
        <BatchId>2345</BatchId>
        <StockReservationKey/>
    </Product>
    
    <Product>
        <ProductText>Item_2</ProductText>
        <ExtProductId>0358523</ExtProductId>   --(Concat key = 0358523 222)
        <Quantity>100.000</Quantity>
        <BatchId>222</BatchId>
        <StockReservationKey/>
    </Product>
    <Product>
        <ProductText>Item_2</ProductText>
        <ExtProductId>0358523</ExtProductId>   --(Concat key = 0358523 333)
        <Quantity>100.000</Quantity>
        <BatchId>333</BatchId>
        <StockReservationKey/>
    </Product>
    
    <Product>
        <ProductText>Item_3</ProductText>
        <ExtProductId>0358500</ExtProductId>   --(Concat key = 0358500)
        <Quantity>50.000</Quantity>
        <BatchId/>
        <StockReservationKey/>
    </Product>
</BalanceList>

So fare I got to this point, but can't figure out the last steps to make the Sum correct and next product to show.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" indent="yes"/>
    <xsl:key name="ProductGroup" match="BalanceList/Products/Product" use="ExtProductId"/>
    <xsl:key name="ProductBatchGroup" match="BalanceList/Products/Product/Batches/Batch" use="BatchId"/>
    <xsl:key name="ProductLocationGroup" match="BalanceList/Products/Product/Batches/Batch/ProductLocations/ProductLocation" use="StockReservationKey"/>
    <xsl:template match="/">
        <BalanceList>
            <Products>
                <xsl:for-each select="BalanceList/Products/Product[generate-id() = generate-id(key('ProductGroup', ExtProductId)[1])]">
                    <xsl:for-each select="Batches/Batch[generate-id() = generate-id(key('ProductBatchGroup', BatchId)[1])]">
                        <xsl:for-each select="ProductLocations/ProductLocation[generate-id() = generate-id(key('ProductLocationGroup', StockReservationKey)[1])]">
                        <xsl:variable name="Reservation-group" select="ProductLocations/ProductLocation[StockReservationKey = current()/StockReservationKey]"/>
                            <Product>
                                <ProductText>
                                    <xsl:value-of select="../../../../ProductText"/>
                                </ProductText>
                                <ExtProductId>
                                    <xsl:value-of select="../../../../ExtProductId"/>
                                </ExtProductId>
                                <BatchId>
                                    <xsl:value-of select="../../BatchId"/>
                                </BatchId>
                                <StockReservationKey>
                                    <xsl:value-of select="StockReservationKey"/>
                                </StockReservationKey>
                                <Quantity>
                                    <xsl:value-of select="sum(Quantity)"/>
                                </Quantity>
                            </Product>
                        </xsl:for-each>
                    </xsl:for-each>
                </xsl:for-each>
            </Products>
        </BalanceList>
    </xsl:template>
</xsl:stylesheet>

CodePudding user response：

AFAICT, the following stylesheet does what you want:

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:key name="k1" match="ProductLocation" use="concat(ancestor::Product/ExtProductId, '|', ancestor::Batch/BatchId, '|', StockReservationKey)"/>

<xsl:template match="/BalanceList">
    <xsl:copy>
        <xsl:copy-of select="TransactionId"/>
        <xsl:for-each select="Products/Product/Batches/Batch/ProductLocations/ProductLocation[generate-id() = generate-id(key('k1', concat(ancestor::Product/ExtProductId, '|', ancestor::Batch/BatchId, '|', StockReservationKey))[1])]">
            <Product>
                <xsl:copy-of select="ancestor::Product/ProductText"/>
                <xsl:copy-of select="ancestor::Product/ExtProductId"/>
                <xsl:copy-of select="ancestor::Batch/BatchId"/>
                <xsl:copy-of select="StockReservationKey"/>
                <Quantity>
                    <xsl:value-of select="sum(key('k1', concat(ancestor::Product/ExtProductId, '|', ancestor::Batch/BatchId, '|', StockReservationKey))/Quantity)"/>
                </Quantity>
            </Product>
        </xsl:for-each>
    </xsl:copy>                    
</xsl:template>

</xsl:stylesheet>