How to group by with two node and count the value in XSLT transformation

Question

I have a requirement with xml file: i have to group by value of two nodes, and count value also.But i'm a new guys in XSLT, please help me.

Input Xml:

    <?xml version="1.0" encoding="utf-8"?>
    <Document>
        <TRU_SALESFORCEONHAND>
            <INVENTDIMID>#0000000150000BBB</INVENTDIMID>
            <INVENTLOCATIONID>W2472-Mezz</INVENTLOCATIONID>
            <INVENTSITEID>DC</INVENTSITEID>
            <ITEMID>000003</ITEMID>
            <ONHANDQTY>11.000000</ONHANDQTY>
        </TRU_SALESFORCEONHAND>
        <TRU_SALESFORCEONHAND>
            <INVENTDIMID>#0000000150000BBC</INVENTDIMID>
            <INVENTLOCATIONID>W2472-Mezz</INVENTLOCATIONID>
            <INVENTSITEID>DC</INVENTSITEID>
            <ITEMID>000003</ITEMID>
            <ONHANDQTY>3.000000</ONHANDQTY>
        </TRU_SALESFORCEONHAND>
        <TRU_SALESFORCEONHAND>
            <INVENTDIMID>#000000015000119A</INVENTDIMID>
            <INVENTLOCATIONID>W3501</INVENTLOCATIONID>
            <INVENTSITEID>DC</INVENTSITEID>
            <ITEMID>000004</ITEMID>
            <ONHANDQTY>23.000000</ONHANDQTY>
        </TRU_SALESFORCEONHAND>
        <TRU_SALESFORCEONHAND>
            <INVENTDIMID>#00000001500011AD</INVENTDIMID>
            <INVENTLOCATIONID>W3508</INVENTLOCATIONID>
            <INVENTSITEID>Site-ON</INVENTSITEID>
            <ITEMID>000003</ITEMID>
            <ONHANDQTY>1.000000</ONHANDQTY>
        </TRU_SALESFORCEONHAND>
        <TRU_SALESFORCEONHAND>
            <INVENTDIMID>#00000001500011AE</INVENTDIMID>
            <INVENTLOCATIONID>W3508</INVENTLOCATIONID>
            <INVENTSITEID>Site-ON</INVENTSITEID>
            <ITEMID>000004</ITEMID>
            <ONHANDQTY>8.000000</ONHANDQTY>
        </TRU_SALESFORCEONHAND>
        <TRU_SALESFORCEONHAND>
            <INVENTDIMID>#0000000150001A73</INVENTDIMID>
            <INVENTLOCATIONID>In_Transit</INVENTLOCATIONID>
            <INVENTSITEID>DC</INVENTSITEID>
            <ITEMID>000004</ITEMID>
            <ONHANDQTY>23.000000</ONHANDQTY>

</TRU_SALESFORCEONHAND>

    </Document>

Output XML:

<?xml version="1.0" encoding="UTF-8"?>
<inventory
    xmlns="http://www.demandware.com/xml/impex/inventory/2007-05-31">
    <inventory-list>
        <header list-id="DC">
            <timestamp>2021-02-01T14:37:15.494Z</timestamp>
        </header>
        <records>
            <record product-id="000003">
                <allocation>14.000000</allocation>
                <allocation-timestamp>2021-02-01T14:37:15.494Z</allocation-timestamp>
            </record>
            <record product-id="000004">
                <allocation>23.000000</allocation>
                <allocation-timestamp>2021-02-01T14:37:15.494Z</allocation-timestamp>
            </record>
        </records>
    </inventory-list>
    <inventory-list>
        <header list-id="Site-ON">
            <timestamp>2021-02-01T14:37:15.494Z</timestamp>
        </header>
        <records>
            <record product-id="000003">
                <allocation>1.000000</allocation>
                <allocation-timestamp>2021-02-01T14:37:15.494Z</allocation-timestamp>
            </record>
            <record product-id="000004">
                <allocation>31.000000</allocation>
                <allocation-timestamp>2021-02-01T14:37:15.494Z</allocation-timestamp>
            </record>
        </records>
    </inventory-list>
</inventory>

With timestamp is current date time. I need to group by InventSiteId for header records is all ITEMID belong to that InventSiteId. allocation is sum of ONHANDQTY for that ITEMID.

I try many time but can't find a way to do that. Please help me, thanks you guys!

Answer 1

It sounds as if you want to nest two for-each-group instructions, as in

<xsl:template match="/Document">
    <inventory>
      <xsl:for-each-group select="TRU_SALESFORCEONHAND" group-by="INVENTSITEID">
          <inventory-list>
              <header list-id="{current-grouping-key()}">
                  <timestamp>
                      <xsl:value-of select="current-dateTime()"/>
                  </timestamp>
                  <records>
                      <xsl:for-each-group select="current-group()" group-by="ITEMID">
                          <record product-id="{current-grouping-key()}">
                              <allocation>
                                  <xsl:value-of select="sum(current-group()/ONHANDQTY)"/>
                              </allocation>
                          </record>
                      </xsl:for-each-group>                      
                  </records>
              </header>
          </inventory-list>
      </xsl:for-each-group>
    </inventory>
</xsl:template>

I don't get the sums you have shown, however, so I might have misunderstood your request.