locked
How to grouping elements and sum values in XSLT 1.0 RRS feed

  • Question

  • Hi TEam,

    Currently i have write a XSLT map for a multiple parent charge Codes groups and sum their values on same group , buts its not working so Could you please support to get a expected output. For your reference here with attached Sample input, Current XSLT and expected output.

    Current input-.

    <invoice>
        <Invoice_No>VS910009873</Invoice_No>
        <Charge_Details>
            <Charge_Detail_Item>
                <House_Bill>VCMB91012282</House_Bill>
                <Master_Bill>TEST-M-000002</Master_Bill>
                <Container_No />
                <Charge_Code_List>
                    <Charge_Code_Item>
                        <Charge_Code>FRT</Charge_Code>
                        <OS_Cost_Amt>13.06</OS_Cost_Amt>
                    </Charge_Code_Item>
                    <Charge_Code_Item>
                        <Charge_Code>OHANDL</Charge_Code>
                        <OS_Cost_Amt>71.83</OS_Cost_Amt>
                    </Charge_Code_Item>
                </Charge_Code_List>
            </Charge_Detail_Item>
            <Charge_Detail_Item>
                <House_Bill>VCMB91012302</House_Bill>
                <Master_Bill>TEST-M-000002</Master_Bill>
                <Container_No />
                <Charge_Code_List>
                    <Charge_Code_Item>
                        <Charge_Code>FRT</Charge_Code>
                        <OS_Cost_Amt>3.62</OS_Cost_Amt>
                    </Charge_Code_Item>
                </Charge_Code_List>
            </Charge_Detail_Item>
            
            </Charge_Detail_Item>
            <Charge_Detail_Item>
                <House_Bill>VCMB91012654</House_Bill>
                <Master_Bill>TEST-M-000002</Master_Bill>
                <Container_No />
                <Charge_Code_List>
                    <Charge_Code_Item>
                        <Charge_Code>FRT</Charge_Code>
                        <OS_Cost_Amt>2.12</OS_Cost_Amt>
                    </Charge_Code_Item>
                    <Charge_Code_Item>
                        <Charge_Code>OHANDL</Charge_Code>
                        <OS_Cost_Amt>11.66</OS_Cost_Amt>
                    </Charge_Code_Item>
                </Charge_Code_List>
            </Charge_Detail_Item>
        </Charge_Details>
        <Total>192.72</Total>
        <Cost_Tax_ID>NOTREPORT</Cost_Tax_ID>
        <Cost>USD</Cost>
        <Filename>e0524090-c658-11ea-ac69-cd5fa95b605b_20200715050601.pdf</Filename>
        <Status>processed</Status>
        <Scan_Time>2020-07-15T05:06:02.000Z</Scan_Time>
        <Agent_Code>VINSHIHKG</Agent_Code>
        <Master_Bill />
        <OCR_ID>OCR_462</OCR_ID>
        <Operator_Action_Code>2</Operator_Action_Code>
    </invoice>

    Current XSLT map-

    <!-- <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"  xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:csharp="urn:my-scripts" exclude-result-prefixes="msxsl csharp"> -->
    
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    	<xsl:output indent="yes" method="xml"/>
    	<!-- <xsl:param name="testkey"/> -->
    	<xsl:template match="/">
    		<UniversalShipment xmlns="http://www.ce.com/Schemas/Universal/2011/11" version="1.1">
    
    			<xsl:for-each select="/invoice">
    				<xsl:choose>	
    					<xsl:when test="not(Operator_Action_Code[.= '']) and Operator_Action_Code = '2' and count(//Charge_Detail_Item) > 1"> 
    
    						<ConsolCosts> 
    							<ConsolCostLineCollection> 
    								<xsl:for-each select="Charge_Details">     
    									<ConsolCostLine> 
    										<ChargeCode>         
    											<Code>
    												<xsl:value-of select="Charge_Detail_Item/Charge_Code_List/Charge_Code_Item/Charge_Code"/>
    											</Code>       
    											<Description/>   
    										</ChargeCode>                     					
    
    
    										<CostLocalAmount>
    											<!-- <xsl:value-of select="OS_Cost_Amt"/> -->
    											<xsl:value-of select="sum(Charge_Detail_Item/Charge_Code_List/Charge_Code_Item/OS_Cost_Amt)"/>
    										</CostLocalAmount>
    
    										<CostOSCurrency>         
    											<Code>USD</Code>       
    											<Description/>  
    										</CostOSCurrency>       
    										<Creditor>       
    											<Type>Organization</Type>    
    											<Key>
    												<xsl:value-of select="/invoice/Agent_Code"/>
    											</Key>
    											<!-- LYNINTMAC -->
    										</Creditor>     
    									</ConsolCostLine>
    								</xsl:for-each>				
    							</ConsolCostLineCollection>  
    						</ConsolCosts> 
    					</xsl:when>		
    
    					<xsl:otherwise>
    						<!-- No Order element -->
    					</xsl:otherwise>
    				</xsl:choose>
    			</xsl:for-each>	
    		</UniversalShipment>
    	</xsl:template>
    
    </xsl:stylesheet>

    Expected output-

    <UniversalShipment xmlns="http://www.ce.com/Schemas/Universal/2011/11" version="1.1">
       <Shipment>      
          <ConsolCosts>
             <ConsolCostLineCollection>
                <ConsolCostLine>
                   <ImportMetaData>
                      <Instruction>Insert</Instruction>
                   </ImportMetaData>
                   <ApportionmentMethod>SHP</ApportionmentMethod>
                   <ApportionToSubShipments>False</ApportionToSubShipments>
                   <ChargeCode>
                      <Code>FRT</Code>
                      <Description/>
                   </ChargeCode>
                   <ChargeCodeGroup>
                      <Code/>
                      <Description/>
                   </ChargeCodeGroup>
                   <CostExchangeRate>1</CostExchangeRate>
                   <CostLocalAmount>18.8</CostLocalAmount>
                   <CostOSAmount>18.8</CostOSAmount>
                   <CostOSCurrency>
                      <Code>USD</Code>
                      <Description/>
                   </CostOSCurrency>
                   <Creditor>
                      <Type>Organization</Type>
                      <Key>VINSHIHKG</Key>
                   </Creditor>
                </ConsolCostLine>
                <ConsolCostLine>
                   <ImportMetaData>
                      <Instruction>Insert</Instruction>
                   </ImportMetaData>
                   <ApportionmentMethod>SHP</ApportionmentMethod>
                   <ApportionToSubShipments>False</ApportionToSubShipments>
                   <ChargeCode>
                      <Code>OHANDL</Code>
                      <Description/>
                   </ChargeCode>
                   <ChargeCodeGroup>
                      <Code/>
                      <Description/>
                   </ChargeCodeGroup>
                   <CostExchangeRate>1</CostExchangeRate>
                   <CostLocalAmount>83.49</CostLocalAmount>
                   <CostOSAmount>83.49</CostOSAmount>
                   <CostOSCurrency>
                      <Code>USD</Code>
                      <Description/>
                   </CostOSCurrency>
                   <Creditor>
                      <Type>Organization</Type>
                      <Key>VINSHIHKG</Key>
                   </Creditor>
                </ConsolCostLine>
                
             </ConsolCostLineCollection>
          </ConsolCosts>
         
       </Shipment>
    </UniversalShipment>

    Monday, July 20, 2020 6:15 PM

Answers

  • Hi Mahesh,

    Here is a solution for you by using the Muenchian Method.

    The XSLT below handles a group for each Charge_Code_Item XML fragment based on the Charge_Code element value.

    XML:

    <?xml version="1.0"?>
    <invoice>
    	<Invoice_No>VS910009873</Invoice_No>
    	<Charge_Details>
    		<Charge_Detail_Item>
    			<House_Bill>VCMB91012282</House_Bill>
    			<Master_Bill>TEST-M-000002</Master_Bill>
    			<Container_No/>
    			<Charge_Code_List>
    				<Charge_Code_Item>
    					<Charge_Code>FRT</Charge_Code>
    					<OS_Cost_Amt>13.06</OS_Cost_Amt>
    				</Charge_Code_Item>
    				<Charge_Code_Item>
    					<Charge_Code>OHANDL</Charge_Code>
    					<OS_Cost_Amt>71.83</OS_Cost_Amt>
    				</Charge_Code_Item>
    			</Charge_Code_List>
    		</Charge_Detail_Item>
    		<Charge_Detail_Item>
    			<House_Bill>VCMB91012302</House_Bill>
    			<Master_Bill>TEST-M-000002</Master_Bill>
    			<Container_No/>
    			<Charge_Code_List>
    				<Charge_Code_Item>
    					<Charge_Code>FRT</Charge_Code>
    					<OS_Cost_Amt>3.62</OS_Cost_Amt>
    				</Charge_Code_Item>
    			</Charge_Code_List>
    		</Charge_Detail_Item>
    		<Charge_Detail_Item>
    			<House_Bill>VCMB91012654</House_Bill>
    			<Master_Bill>TEST-M-000002</Master_Bill>
    			<Container_No/>
    			<Charge_Code_List>
    				<Charge_Code_Item>
    					<Charge_Code>FRT</Charge_Code>
    					<OS_Cost_Amt>2.12</OS_Cost_Amt>
    				</Charge_Code_Item>
    				<Charge_Code_Item>
    					<Charge_Code>OHANDL</Charge_Code>
    					<OS_Cost_Amt>11.66</OS_Cost_Amt>
    				</Charge_Code_Item>
    			</Charge_Code_List>
    		</Charge_Detail_Item>
    	</Charge_Details>
    	<Total>192.72</Total>
    	<Cost_Tax_ID>NOTREPORT</Cost_Tax_ID>
    	<Cost>USD</Cost>
    	<Filename>e0524090-c658-11ea-ac69-cd5fa95b605b_20200715050601.pdf</Filename>
    	<Status>processed</Status>
    	<Scan_Time>2020-07-15T05:06:02.000Z</Scan_Time>
    	<Agent_Code>VINSHIHKG</Agent_Code>
    	<Master_Bill/>
    	<OCR_ID>OCR_462</OCR_ID>
    	<Operator_Action_Code>2</Operator_Action_Code>
    </invoice>

    XSLT:

    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    	<xsl:output indent="yes" method="xml"/>
    
    	<xsl:key name="KeyCodeItem" match="Charge_Code_Item" use="Charge_Code"/>
    
    	<xsl:template match="/invoice">
    		<UniversalShipment xmlns="http://www.ce.com/Schemas/Universal/2011/11" version="1.1">
    			<xsl:choose>
    				<xsl:when test="not(Operator_Action_Code[.= '']) and Operator_Action_Code = '2' and count(//Charge_Detail_Item) &gt; 1">
    					<Shipment>
    						<ConsolCosts>
    							<ConsolCostLineCollection>
    								<xsl:for-each select="Charge_Details/Charge_Detail_Item/Charge_Code_List/Charge_Code_Item[generate-id(.) = generate-id(key('KeyCodeItem', Charge_Code)[1])]">
    									<ConsolCostLine>
    										<ImportMetaData>
    											<Instruction>Insert</Instruction>
    										</ImportMetaData>
    										<ApportionmentMethod>SHP</ApportionmentMethod>
    										<ApportionToSubShipments>False</ApportionToSubShipments>
    										<ChargeCode>
    											<Code>
    												<xsl:value-of select="key('KeyCodeItem', Charge_Code)/Charge_Code"/>
    											</Code>
    											<Description/>
    										</ChargeCode>
    										<CostExchangeRate>1</CostExchangeRate>
    										<CostLocalAmount>
    											<xsl:value-of select="sum(key('KeyCodeItem', Charge_Code)/OS_Cost_Amt)"/>
    										</CostLocalAmount>
    										<CostOSCurrency>
    											<Code>USD</Code>
    											<Description/>
    										</CostOSCurrency>
    										<Creditor>
    											<Type>Organization</Type>
    											<Key>
    												<xsl:value-of select="/invoice/Agent_Code"/>
    											</Key>
    											<!-- LYNINTMAC -->
    										</Creditor>
    									</ConsolCostLine>
    								</xsl:for-each>
    							</ConsolCostLineCollection>
    						</ConsolCosts>
    					</Shipment>
    				</xsl:when>
    				<xsl:otherwise>No Order element</xsl:otherwise>
    			</xsl:choose>
    		</UniversalShipment>
    	</xsl:template>
    </xsl:stylesheet>

    Output:

    <?xml version='1.0' ?>
    <UniversalShipment xmlns="http://www.ce.com/Schemas/Universal/2011/11" version="1.1">
      <Shipment>
        <ConsolCosts>
          <ConsolCostLineCollection>
            <ConsolCostLine>
              <ImportMetaData>
                <Instruction>Insert</Instruction>
              </ImportMetaData>
              <ApportionmentMethod>SHP</ApportionmentMethod>
              <ApportionToSubShipments>False</ApportionToSubShipments>
              <ChargeCode>
                <Code>FRT</Code>
                <Description/>
              </ChargeCode>
              <CostExchangeRate>1</CostExchangeRate>
              <CostLocalAmount>18.8</CostLocalAmount>
              <CostOSCurrency>
                <Code>USD</Code>
                <Description/>
              </CostOSCurrency>
              <Creditor>
                <Type>Organization</Type>
                <Key>VINSHIHKG</Key>
              </Creditor>
            </ConsolCostLine>
            <ConsolCostLine>
              <ImportMetaData>
                <Instruction>Insert</Instruction>
              </ImportMetaData>
              <ApportionmentMethod>SHP</ApportionmentMethod>
              <ApportionToSubShipments>False</ApportionToSubShipments>
              <ChargeCode>
                <Code>OHANDL</Code>
                <Description/>
              </ChargeCode>
              <CostExchangeRate>1</CostExchangeRate>
              <CostLocalAmount>83.49</CostLocalAmount>
              <CostOSCurrency>
                <Code>USD</Code>
                <Description/>
              </CostOSCurrency>
              <Creditor>
                <Type>Organization</Type>
                <Key>VINSHIHKG</Key>
              </Creditor>
            </ConsolCostLine>
          </ConsolCostLineCollection>
        </ConsolCosts>
      </Shipment>
    </UniversalShipment>



    Monday, July 20, 2020 9:51 PM

All replies

  • I would suggest posting this in the XML forum.  https://social.msdn.microsoft.com/Forums/en-US/home?forum=xmlandnetfx&filter=alllanguages

    There you are likely to find people more skilled in XSLT


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, July 20, 2020 8:50 PM
  • Hi Mahesh,

    Here is a solution for you by using the Muenchian Method.

    The XSLT below handles a group for each Charge_Code_Item XML fragment based on the Charge_Code element value.

    XML:

    <?xml version="1.0"?>
    <invoice>
    	<Invoice_No>VS910009873</Invoice_No>
    	<Charge_Details>
    		<Charge_Detail_Item>
    			<House_Bill>VCMB91012282</House_Bill>
    			<Master_Bill>TEST-M-000002</Master_Bill>
    			<Container_No/>
    			<Charge_Code_List>
    				<Charge_Code_Item>
    					<Charge_Code>FRT</Charge_Code>
    					<OS_Cost_Amt>13.06</OS_Cost_Amt>
    				</Charge_Code_Item>
    				<Charge_Code_Item>
    					<Charge_Code>OHANDL</Charge_Code>
    					<OS_Cost_Amt>71.83</OS_Cost_Amt>
    				</Charge_Code_Item>
    			</Charge_Code_List>
    		</Charge_Detail_Item>
    		<Charge_Detail_Item>
    			<House_Bill>VCMB91012302</House_Bill>
    			<Master_Bill>TEST-M-000002</Master_Bill>
    			<Container_No/>
    			<Charge_Code_List>
    				<Charge_Code_Item>
    					<Charge_Code>FRT</Charge_Code>
    					<OS_Cost_Amt>3.62</OS_Cost_Amt>
    				</Charge_Code_Item>
    			</Charge_Code_List>
    		</Charge_Detail_Item>
    		<Charge_Detail_Item>
    			<House_Bill>VCMB91012654</House_Bill>
    			<Master_Bill>TEST-M-000002</Master_Bill>
    			<Container_No/>
    			<Charge_Code_List>
    				<Charge_Code_Item>
    					<Charge_Code>FRT</Charge_Code>
    					<OS_Cost_Amt>2.12</OS_Cost_Amt>
    				</Charge_Code_Item>
    				<Charge_Code_Item>
    					<Charge_Code>OHANDL</Charge_Code>
    					<OS_Cost_Amt>11.66</OS_Cost_Amt>
    				</Charge_Code_Item>
    			</Charge_Code_List>
    		</Charge_Detail_Item>
    	</Charge_Details>
    	<Total>192.72</Total>
    	<Cost_Tax_ID>NOTREPORT</Cost_Tax_ID>
    	<Cost>USD</Cost>
    	<Filename>e0524090-c658-11ea-ac69-cd5fa95b605b_20200715050601.pdf</Filename>
    	<Status>processed</Status>
    	<Scan_Time>2020-07-15T05:06:02.000Z</Scan_Time>
    	<Agent_Code>VINSHIHKG</Agent_Code>
    	<Master_Bill/>
    	<OCR_ID>OCR_462</OCR_ID>
    	<Operator_Action_Code>2</Operator_Action_Code>
    </invoice>

    XSLT:

    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    	<xsl:output indent="yes" method="xml"/>
    
    	<xsl:key name="KeyCodeItem" match="Charge_Code_Item" use="Charge_Code"/>
    
    	<xsl:template match="/invoice">
    		<UniversalShipment xmlns="http://www.ce.com/Schemas/Universal/2011/11" version="1.1">
    			<xsl:choose>
    				<xsl:when test="not(Operator_Action_Code[.= '']) and Operator_Action_Code = '2' and count(//Charge_Detail_Item) &gt; 1">
    					<Shipment>
    						<ConsolCosts>
    							<ConsolCostLineCollection>
    								<xsl:for-each select="Charge_Details/Charge_Detail_Item/Charge_Code_List/Charge_Code_Item[generate-id(.) = generate-id(key('KeyCodeItem', Charge_Code)[1])]">
    									<ConsolCostLine>
    										<ImportMetaData>
    											<Instruction>Insert</Instruction>
    										</ImportMetaData>
    										<ApportionmentMethod>SHP</ApportionmentMethod>
    										<ApportionToSubShipments>False</ApportionToSubShipments>
    										<ChargeCode>
    											<Code>
    												<xsl:value-of select="key('KeyCodeItem', Charge_Code)/Charge_Code"/>
    											</Code>
    											<Description/>
    										</ChargeCode>
    										<CostExchangeRate>1</CostExchangeRate>
    										<CostLocalAmount>
    											<xsl:value-of select="sum(key('KeyCodeItem', Charge_Code)/OS_Cost_Amt)"/>
    										</CostLocalAmount>
    										<CostOSCurrency>
    											<Code>USD</Code>
    											<Description/>
    										</CostOSCurrency>
    										<Creditor>
    											<Type>Organization</Type>
    											<Key>
    												<xsl:value-of select="/invoice/Agent_Code"/>
    											</Key>
    											<!-- LYNINTMAC -->
    										</Creditor>
    									</ConsolCostLine>
    								</xsl:for-each>
    							</ConsolCostLineCollection>
    						</ConsolCosts>
    					</Shipment>
    				</xsl:when>
    				<xsl:otherwise>No Order element</xsl:otherwise>
    			</xsl:choose>
    		</UniversalShipment>
    	</xsl:template>
    </xsl:stylesheet>

    Output:

    <?xml version='1.0' ?>
    <UniversalShipment xmlns="http://www.ce.com/Schemas/Universal/2011/11" version="1.1">
      <Shipment>
        <ConsolCosts>
          <ConsolCostLineCollection>
            <ConsolCostLine>
              <ImportMetaData>
                <Instruction>Insert</Instruction>
              </ImportMetaData>
              <ApportionmentMethod>SHP</ApportionmentMethod>
              <ApportionToSubShipments>False</ApportionToSubShipments>
              <ChargeCode>
                <Code>FRT</Code>
                <Description/>
              </ChargeCode>
              <CostExchangeRate>1</CostExchangeRate>
              <CostLocalAmount>18.8</CostLocalAmount>
              <CostOSCurrency>
                <Code>USD</Code>
                <Description/>
              </CostOSCurrency>
              <Creditor>
                <Type>Organization</Type>
                <Key>VINSHIHKG</Key>
              </Creditor>
            </ConsolCostLine>
            <ConsolCostLine>
              <ImportMetaData>
                <Instruction>Insert</Instruction>
              </ImportMetaData>
              <ApportionmentMethod>SHP</ApportionmentMethod>
              <ApportionToSubShipments>False</ApportionToSubShipments>
              <ChargeCode>
                <Code>OHANDL</Code>
                <Description/>
              </ChargeCode>
              <CostExchangeRate>1</CostExchangeRate>
              <CostLocalAmount>83.49</CostLocalAmount>
              <CostOSCurrency>
                <Code>USD</Code>
                <Description/>
              </CostOSCurrency>
              <Creditor>
                <Type>Organization</Type>
                <Key>VINSHIHKG</Key>
              </Creditor>
            </ConsolCostLine>
          </ConsolCostLineCollection>
        </ConsolCosts>
      </Shipment>
    </UniversalShipment>



    Monday, July 20, 2020 9:51 PM
  • Hi Yitzhak,

    Thank you very much, your solution worked, 

    Thanks

    Tuesday, July 21, 2020 5:06 PM