sum substring of multiple node elements with same ID in xslt 1.0

Question

I have the following source document

<TransportOrder>
    <Orders>
        <Order>
            <OrderSpecification>
                <Stops>
                    <Stop SeqNo="0" StopType="1" StopOrder="0">
                        <AddressDetails>
                            <Name>john doe</Name>
                            <AddressInfos>
                                <AddressInfo />
                            </AddressInfos>
                            <Street>street</Street>
                            <StreetNo>1</StreetNo>
                        </AddressDetails>
                    </Stop>
                </Stops>
                <OrderText>
                    <CarrierInstruction>, , , , 1.052</CarrierInstruction>
                </OrderText>
            </OrderSpecification>
        </Order>
        <Order>
            <OrderSpecification>
                <Stops>
                    <Stop SeqNo="0" StopType="1" StopOrder="0">
                        <AddressDetails>
                            <Name>john doe</Name>
                            <AddressInfos>
                                <AddressInfo />
                            </AddressInfos>
                            <Street>street</Street>
                            <StreetNo>1</StreetNo>
                        </AddressDetails>
                    </Stop>
                </Stops>
                <OrderText>
                    <CarrierInstruction>, +, 1, , 1.052</CarrierInstruction>
                </OrderText>
            </OrderSpecification>
        </Order>
        <Order>
            <OrderSpecification>
                <Stops>
                    <Stop SeqNo="0" StopType="1" StopOrder="0">
                        <AddressDetails>
                            <Name>john smith</Name>
                            <AddressInfos>
                                <AddressInfo />
                            </AddressInfos>
                            <Street>street</Street>
                            <StreetNo>2</StreetNo>
                        </AddressDetails>
                    </Stop>
                </Stops>
                <OrderText>
                    <CarrierInstruction>, , , , 1.055</CarrierInstruction>
                </OrderText>
            </OrderSpecification>
        </Order>
        <Order>
            <OrderSpecification>
                <Stops>
                    <Stop SeqNo="0" StopType="1" StopOrder="0">
                        <AddressDetails>
                            <Name>john smith</Name>
                            <AddressInfos>
                                <AddressInfo />
                            </AddressInfos>
                            <Street>street</Street>
                            <StreetNo>2</StreetNo>
                        </AddressDetails>
                    </Stop>
                </Stops>
                <OrderText>
                    <CarrierInstruction>, , , , 1.055</CarrierInstruction>
                </OrderText>
            </OrderSpecification>
        </Order>
    </Orders>
</TransportOrder>

and the following stylesheet

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt">
    <xsl:output indent="yes"/>
    <xsl:template match="TransportOrder">
    <transfer>
    <xsl:for-each select="Orders/Order">
    
    <xsl:variable name="mergeId">
        <xsl:value-of select="OrderSpecification/Stops/Stop[@StopType='1']/AddressDetails/Street"/>-<xsl:value-of select="OrderSpecification/Stops/Stop[@StopType='1']/AddressDetails/StreetNo"/>-<xsl:value-of select="OrderSpecification/Stops/Stop[@StopType='1']/AddressDetails/Name"/>
    </xsl:variable>
    
    <order>
       <message>
           <messageType>merge</messageType>
       </message> 
      <identification>
          <orderNumber><xsl:value-of select="$mergeId"/></orderNumber>
      </identification>
    </order>
    </xsl:for-each>
    
    <xsl:for-each select="Orders/Order[$mergeId=$mergeId]">
        <xsl:variable name="weight">
            <xsl:value-of select="normalize-space(substring-after(substring-after(substring-after(substring-after(OrderSpecification/OrderText,','),','),','),','))"/>
        </xsl:variable>
        
        <xsl:variable name="vehicle">
            <xsl:choose>
                <xsl:when test="sum($weight[$mergeId=$mergeId])&lt;= 50">truck</xsl:when>
                <xsl:otherwise>car</xsl:otherwise>
            </xsl:choose>
        </xsl:variable>
        
        <order>
            <message>
                <messageType>update</messageType>
            </message>
            <identification>
              <orderNumber><xsl:value-of select="$mergeId"/></orderNumber>
            </identification>

            <vehicle>
                <vehicleCode><xsl:value-of select="$vehicle"/></vehicleCode>
            </vehicle>
        </order>
    </xsl:for-each>
    </transfer>
</xsl:template>
</xsl:stylesheet>

What im trying to accomplish is to take the sum of the weight on each Order from OrderText/carrierInstruction(the substring after the 4 commas, data will be between the commas but removed for privacy and relevancy reasons) where the $mergeId matches and by looking at the weight set different vehicleCodes.

Preferably i would only want to print out the order[messageType='update'] part for the unique mergeId's as well. PS: the 4 Order segments with messagetype merge are correct and needs to be there. the stylesheet is currently broken because the sum must evaluate to a nodeset and im a bit unsure on how to accomplish this. if it is even possible in XLST 1.0(i can only use 1.0 since the software im implementing this into only support 1.0)

my wanted result would be something like this,

<?xml version="1.0"?>
<transfer>
    <order>
        <message>
            <messageType>merge</messageType>
        </message>
        <identification>
            <orderNumber>street-1-john doe</orderNumber>
        </identification>
    </order>
    
    <order>
        <message>
            <messageType>merge</messageType>
        </message>
        <identification>
            <orderNumber>street-1-john doe</orderNumber>
        </identification>
    </order>
    
    <order>
        <message>
            <messageType>merge</messageType>
        </message>
        <identification>
            <orderNumber>street-2-john smith</orderNumber>
        </identification>
    </order>

    <order>
        <message>
            <messageType>merge</messageType>
        </message>
        <identification>
            <orderNumber>street-2-john doe</orderNumber>
        </identification>
    </order>
    
    <order>
        <message>
            <messageType>update</messageType>
        </message>
      <identification>
          <orderNumber>street-2-john smith</orderNumber>
      </identification>
        <vehicle>
            <vehicleCode>car</vehicleCode>
        </vehicle>
    </order>
     <order>
        <message>
            <messageType>update</messageType>
        </message>
      <identification>
          <orderNumber>street-1-john doe</orderNumber>
      </identification>
        <vehicle>
            <vehicleCode>car</vehicleCode>
        </vehicle>
    </order>

</transfer>

it would be acceptable to have 4 order segments with the messagetype update as well the most important part is checking the weight and setting the proper vehicleCode.

Consider simplifying the question to a single problem. Right now you seem to be contending with (1) grouping nodes by a common value (or rather concatenation of values); (2) summing the results of a calculation performed for each member of a node-set (the group in this case). For (1), learn about [Muenchian grouping](http://www.jenitennison.com/xslt/grouping/muenchian.html). For (2), you need to do this in two passes. — michael.hor257k, Jan 06 '21 at 17:57
P.S. You would do well to [find out which processor your software implements](https://stackoverflow.com/a/25245033/3016153), as you might benefit from the extension functions it supports (if any). — michael.hor257k, Jan 06 '21 at 17:57
im mostly concerned about the (2) what i need is to sum the weight where the $mergeId matches and then set the correct value in vehicle for the ones that end up having the same mergeId so 1.052+1.052 is less than 50 so set vehicleCode to car for each street-2-john smith "order", the processor just says microsoft — ricegnat, Jan 06 '21 at 18:21
*"sum the weight where the $mergeId matches"* If the weight is already a number, you can sum it using an expression along the lines of `sum(key('grp', concat(...))/weight)`. But you have a long way to go before you get to the point where you can use this. — michael.hor257k, Jan 06 '21 at 18:32

michael.hor257k · Accepted Answer · 2021-01-06T21:15:44.800

Here is a greatly simplified example you can use as your starting point. It uses the Muenchian method to group orders at the same address, and a recursive named template to extract and sum the weights of each group.

XML

<Orders>
    <Order id="1">
        <Address>Alpha</Address>
        <CarrierInstruction>a,b,c,d, 1.052</CarrierInstruction>
    </Order>
    <Order id="2">
        <Address>Alpha</Address>
        <CarrierInstruction>a,b,c,d, 2.025</CarrierInstruction>
    </Order>
    <Order id="3">
        <Address>Alpha</Address>
        <CarrierInstruction>a,b,c,d, 3.071</CarrierInstruction>
    </Order>
    <Order id="4">
        <Address>Bravo</Address>
        <CarrierInstruction>a,b,c,d, 1.452</CarrierInstruction>
    </Order>
    <Order id="5">
        <Address>Bravo</Address>
        <CarrierInstruction>a,b,c,d, 2.507</CarrierInstruction>
    </Order>
</Orders>

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="grp" match="Order" use="Address"/>

<xsl:template match="/Orders">
    <xsl:copy>
        <!-- for each distinct address -->
        <xsl:for-each select="Order[count(. | key('grp', Address)[1]) = 1]">
            <!-- get the total weight of orders with this address -->
            <xsl:variable name="group-weight">
                <xsl:call-template name="total">
                    <xsl:with-param name="nodes" select="key('grp', Address)/CarrierInstruction"/>
                </xsl:call-template>
            </xsl:variable>
            <!-- output current group -->
            <xsl:for-each select="key('grp', Address)">
                <xsl:copy>
                    <xsl:copy-of select="@* | node()"/>
                    <Group-Weight>
                        <xsl:value-of select="$group-weight"/>
                    </Group-Weight>
                </xsl:copy>
            </xsl:for-each>
        </xsl:for-each>
    </xsl:copy>
</xsl:template>

<xsl:template name="total">
    <xsl:param name="nodes"/>
    <xsl:param name="sum" select="0"/>
    <xsl:choose>
        <xsl:when test="count($nodes)">
            <!-- extract the number from the first node -->
            <xsl:variable name="num" select="substring-after(substring-after(substring-after(substring-after($nodes[1],','),','),','),',')"/>
            <!-- recursive call with the rest of the nodes -->
            <xsl:call-template name="total">
                <xsl:with-param name="nodes" select="$nodes[position() > 1]"/>
                <xsl:with-param name="sum" select="$sum + $num"/>
            </xsl:call-template>
        </xsl:when>
        <xsl:otherwise>
            <xsl:value-of select="$sum"/>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>

</xsl:stylesheet>

Result

<?xml version="1.0" encoding="UTF-8"?>
<Orders>
  <Order id="1">
    <Address>Alpha</Address>
    <CarrierInstruction>a,b,c,d, 1.052</CarrierInstruction>
    <Group-Weight>6.148</Group-Weight>
  </Order>
  <Order id="2">
    <Address>Alpha</Address>
    <CarrierInstruction>a,b,c,d, 2.025</CarrierInstruction>
    <Group-Weight>6.148</Group-Weight>
  </Order>
  <Order id="3">
    <Address>Alpha</Address>
    <CarrierInstruction>a,b,c,d, 3.071</CarrierInstruction>
    <Group-Weight>6.148</Group-Weight>
  </Order>
  <Order id="4">
    <Address>Bravo</Address>
    <CarrierInstruction>a,b,c,d, 1.452</CarrierInstruction>
    <Group-Weight>3.959</Group-Weight>
  </Order>
  <Order id="5">
    <Address>Bravo</Address>
    <CarrierInstruction>a,b,c,d, 2.507</CarrierInstruction>
    <Group-Weight>3.959</Group-Weight>
  </Order>
</Orders>

Many thanks! the solution helped me alot to get on the right track on how to get it the way i wanted thanks yet again !:) — ricegnat, Jan 06 '21 at 22:24

sum substring of multiple node elements with same ID in xslt 1.0

1 Answers1