none
Sum of similar tag elements in XSLT RRS feed

  • Question

  • I'm  already have write a XSLT for xml mapping for slimier tag, according to that out put coming with same tag repeating and few tagging, i want to summarize that similar tag including values sum.Can you please help on this.

    Current XSLT Mapping 

    <InvoiceType><xsl:value-of select="//cw:TransactionInfo/cw:ShipmentCollection/cw:Shipment/cw:SubShipmentCollection/cw:SubShipment/cw:JobCosting/cw:ChargeLineCollection/cw:ChargeLine/cw:SellInvoiceType"/></InvoiceType>


    <xsl:for-each select = "//cw:TransactionInfo/cw:PostingJournalCollection"> 

                     <xsl:if test = "cw:PostingJournal/cw:ChargeCode/cw:Code[text()='DO']"> 

                 <DOC> <xsl:value-of select="//cw:PostingJournalCollection/cw:PostingJournal/cw:ChargeCode/cw:Code[text()='DO']/ancestor::cw:PostingJournal/cw:ChargeTotalAmount"/> </DOC>

                      </xsl:if> 

    <xsl:if test = "cw:PostingJournal/cw:ChargeCode/cw:Code[text()='DDOCU']"> 

                 <DOC> <xsl:value-of select="//cw:PostingJournalCollection/cw:PostingJournal/cw:ChargeCode/cw:Code[text()='DDOCU']/ancestor::cw:PostingJournal/cw:ChargeTotalAmount"/> </DOC>

                      </xsl:if>

                     <xsl:if test = "cw:PostingJournal/cw:ChargeCode/cw:Code[text()='IOUDDOCUM']"> 

                 <DOC> <xsl:value-of select="//cw:PostingJournalCollection/cw:PostingJournal/cw:ChargeCode/cw:Code[text()='IOUDDOCUM']/ancestor::cw:PostingJournal/cw:ChargeTotalAmount"/> </DOC>

                      </xsl:if>

                    <xsl:if test = "cw:PostingJournal/cw:ChargeCode/cw:Code[text()='CONGESC']"> 

                <FREIGHT> <xsl:value-of select="//cw:PostingJournalCollection/cw:PostingJournal/cw:ChargeCode/cw:Code[text()='CONGESC']/ancestor::cw:PostingJournal/cw:ChargeTotalAmount"/> </FREIGHT>

                      </xsl:if>

                     <xsl:if test = "cw:PostingJournal/cw:ChargeCode/cw:Code[text()='FRT']"> 

                <FREIGHT> <xsl:value-of select="//cw:PostingJournalCollection/cw:PostingJournal/cw:ChargeCode/cw:Code[text()='FRT']/ancestor::cw:PostingJournal/cw:ChargeTotalAmount"/> </FREIGHT>

                      </xsl:if>

                     <xsl:if test = "cw:PostingJournal/cw:ChargeCode/cw:Code[text()='GRI']"> 

                <FREIGHT> <xsl:value-of select="//cw:PostingJournalCollection/cw:PostingJournal/cw:ChargeCode/cw:Code[text()='GRI']/ancestor::cw:PostingJournal/cw:ChargeTotalAmount"/> </FREIGHT>

                      </xsl:if>

    Current Out put

    <InvoiceNo>AR INV EFL-CM1-00026395</InvoiceNo>
    <InvoiceType>
    </InvoiceType>
    <DOC>1000.0000</DOC>
    <DOC>1000.0000</DOC>
    <DOC>1000.0000</DOC>
    <FREIGHT>1000.0000</FREIGHT>
    <FREIGHT>1000.0000</FREIGHT>
    <FREIGHT>1000.0000</FREIGHT>
    <FREIGHT>1000.0000</FREIGHT>
    <EXW>1000.0000</EXW>
    <EXW>1000.0000</EXW>
    <EXW>1000.0000</EXW>

    Expected Result'

    <InvoiceNo>AR INV EFL-CM1-00026395</InvoiceNo>
    <InvoiceType>
    </InvoiceType>
    <DOC>3000.0000</DOC>
    <FREIGHT>4000.0000</FREIGHT>
    <EXW>3000.0000</EXW>

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
    Wednesday, February 13, 2019 8:06 AM

Answers

  • Hi Maneshma,

    Here is advanced version of the XSLT.

    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:cw="http://www.cargowise.com/Schemas/Universal/2011/11" exclude-result-prefixes="cw">
    	<xsl:output indent="yes" method="xml"/>
    
    	<xsl:template match="/cw:UniversalInterchange">
    		<tblFreight_Excel_Upload>
    			<InvoiceNo>
    				<xsl:value-of select="cw:Body/cw:UniversalTransaction/cw:TransactionInfo/cw:DataContext/cw:DataSourceCollection/cw:DataSource/cw:Key"/>
    			</InvoiceNo>
    			<DOC>
    				<!--<xsl:value-of select="format-number(sum(cw:Body/cw:UniversalTransaction/cw:TransactionInfo/cw:PostingJournalCollection/cw:PostingJournal[cw:ChargeCode/cw:Code='DO' or cw:ChargeCode/cw:Code='DDOCU' or cw:ChargeCode/cw:Code='IOUDDOCUM']/cw:ChargeTotalAmount), '#.0000')"/>-->
    				<xsl:value-of select="format-number(sum(cw:Body/cw:UniversalTransaction/cw:TransactionInfo/cw:PostingJournalCollection/cw:PostingJournal[contains('_DO_DDOCU_IOUDDOCUM_', concat('_',cw:ChargeCode/cw:Code,'_'))]/cw:ChargeTotalAmount), '#.0000')"/>
    			</DOC>
    			<FREIGHT>
    				<!--<xsl:value-of select="format-number(sum(cw:Body/cw:UniversalTransaction/cw:TransactionInfo/cw:PostingJournalCollection/cw:PostingJournal[cw:ChargeCode/cw:Code='CONGESC' or cw:ChargeCode/cw:Code='FRT' or cw:ChargeCode/cw:Code='GRI' or cw:ChargeCode/cw:Code='PIER']/cw:ChargeTotalAmount), '#.0000')"/>-->
    				<xsl:value-of select="format-number(sum(cw:Body/cw:UniversalTransaction/cw:TransactionInfo/cw:PostingJournalCollection/cw:PostingJournal[contains('_CONGESC_FRT_GRI_PIER_', concat('_',cw:ChargeCode/cw:Code,'_'))]/cw:ChargeTotalAmount), '#.0000')"/>
    			</FREIGHT>
    		</tblFreight_Excel_Upload>
    	</xsl:template>
    </xsl:stylesheet>

    • Marked as answer by Maheshma Tuesday, February 19, 2019 3:06 PM
    Tuesday, February 19, 2019 7:26 AM

All replies

  • Check a simplified example. It uses the sum function.

    XML:

    <?xml version="1.0" encoding="utf-8" ?> 
    <MyData>
      <PostingJournalCollection>
        <PostingJournal>
          <ChargeCode>
            <Code>DO</Code>
          </ChargeCode>
          <ChargeTotalAmount>1000.00</ChargeTotalAmount>
        </PostingJournal>
        <PostingJournal>
          <ChargeCode>
            <Code>DDOCU</Code>
          </ChargeCode>
          <ChargeTotalAmount>1000.00</ChargeTotalAmount>
        </PostingJournal>
      </PostingJournalCollection>
    </MyData>

     


    XSLT fragment:

    <DOC>
      <xsl:value-of select="sum(//PostingJournalCollection/PostingJournal[ChargeCode/Code='DO' or ChargeCode/Code='DDOCU']/ChargeTotalAmount)"/>
    </DOC>





    • Edited by Viorel_MVP Wednesday, February 13, 2019 9:19 AM
    Wednesday, February 13, 2019 9:16 AM
  • Hi Team,

    I tried this method but its not worked, Can you please Support to sort out this.

    tried XSLT-

                                                                                           

    <xsl:for-each select = "//cw:TransactionInfo/cw:PostingJournalCollection"> 

                     <xsl:if test = "cw:PostingJournal/cw:ChargeCode/cw:Code[text()='DO' and text()='DDOCU']"> 

    <DOC><xsl:value-of select="sum(//PostingJournalCollection/PostingJournal[ChargeCode/Code='DO' or ChargeCode/Code='DDOCU']/ancestor::cw:PostingJournal/cw:ChargeTotalAmount)"/></DOC>

    </xsl:if>

    </xsl:for-each>

    Sunday, February 17, 2019 12:42 PM
  • Hi Maneshma,

    Please provide your input XML.

    It looks like it has some namespaces and it is affecting the XSLT.


    Sunday, February 17, 2019 1:08 PM
  • Hi Yitzhak,

    You mean issue was 'cw:' ?

    Can you tell me if not all are correct ?

    Thanks

    Mahesh

    Sunday, February 17, 2019 1:31 PM
  • Hi Maneshma,

    Yes, the 'cw:' namespace is definitely affecting the XSLT.

    I cannot answer the second question without seeing the input XML.

    Overall, you don't need a loop, just sum().

    When working with XSLT, there is always a need to know 3 things:

    1. Input XML.
    2. XSLT.
    3. Desired output.




    Sunday, February 17, 2019 1:38 PM
  • Hi Yitzhak,

    noted and thank you, If i share required details Can you support to sort out my issue.  XSLT and Desired output. already in the forum loop.

    1. Input XML.
    2. XSLT.
    3. Desired output.

    Thanks

    Mahesh

    Sunday, February 17, 2019 4:24 PM
  • Hi Maneshma,

    Sure.

    As I mentioned in my very first reply: "...Please provide your input XML..."

    We need both ##1,2 to produce output #3.




    Sunday, February 17, 2019 4:36 PM
  • Hi Maneshma,

    Here is your XSLT:

    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:cw="http://www.cargowise.com/Schemas/Universal/2011/11" exclude-result-prefixes="cw">
    	<xsl:output indent="yes" method="xml"/>
    
    	<xsl:template match="/cw:UniversalInterchange">
    		<root>
    			<InvoiceNo>
    				<xsl:value-of select="cw:Body/cw:UniversalTransaction/cw:TransactionInfo/cw:DataContext/cw:DataSourceCollection/cw:DataSource/cw:Key"/>
    			</InvoiceNo>
    			<DOC>
    				<xsl:value-of select="format-number(sum(cw:Body/cw:UniversalTransaction/cw:TransactionInfo/cw:PostingJournalCollection/cw:PostingJournal[cw:ChargeCode/cw:Code='DO' or cw:ChargeCode/cw:Code='DDOCU' or cw:ChargeCode/cw:Code='IOUDDOCUM']/cw:ChargeTotalAmount), '#.0000')"/>
    			</DOC>
    			<FREIGHT>
    				<xsl:value-of select="format-number(sum(cw:Body/cw:UniversalTransaction/cw:TransactionInfo/cw:PostingJournalCollection/cw:PostingJournal[cw:ChargeCode/cw:Code='CONGESC' or cw:ChargeCode/cw:Code='FRT' or cw:ChargeCode/cw:Code='GRI']/cw:ChargeTotalAmount), '#.0000')"/>
    			</FREIGHT>
    		</root>
    	</xsl:template>
    </xsl:stylesheet>

    Output:
    <?xml version='1.0' ?>
    <root>
      <InvoiceNo>AR INV EFL-CM1-00026395</InvoiceNo>
      <DOC>3000.0000</DOC>
      <FREIGHT>4000.0000</FREIGHT>
    </root>


    Sunday, February 17, 2019 5:40 PM
  • Hi Maneshma,

    When you will feel comfortable with the XSLT above, I will show you an advance technique how to handle long list of logical OR values more efficiently. It will help with the tags like EXW.


    Sunday, February 17, 2019 8:08 PM
  • Hi Maneshma,

    Here is advanced version of the XSLT.

    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:cw="http://www.cargowise.com/Schemas/Universal/2011/11" exclude-result-prefixes="cw">
    	<xsl:output indent="yes" method="xml"/>
    
    	<xsl:template match="/cw:UniversalInterchange">
    		<tblFreight_Excel_Upload>
    			<InvoiceNo>
    				<xsl:value-of select="cw:Body/cw:UniversalTransaction/cw:TransactionInfo/cw:DataContext/cw:DataSourceCollection/cw:DataSource/cw:Key"/>
    			</InvoiceNo>
    			<DOC>
    				<!--<xsl:value-of select="format-number(sum(cw:Body/cw:UniversalTransaction/cw:TransactionInfo/cw:PostingJournalCollection/cw:PostingJournal[cw:ChargeCode/cw:Code='DO' or cw:ChargeCode/cw:Code='DDOCU' or cw:ChargeCode/cw:Code='IOUDDOCUM']/cw:ChargeTotalAmount), '#.0000')"/>-->
    				<xsl:value-of select="format-number(sum(cw:Body/cw:UniversalTransaction/cw:TransactionInfo/cw:PostingJournalCollection/cw:PostingJournal[contains('_DO_DDOCU_IOUDDOCUM_', concat('_',cw:ChargeCode/cw:Code,'_'))]/cw:ChargeTotalAmount), '#.0000')"/>
    			</DOC>
    			<FREIGHT>
    				<!--<xsl:value-of select="format-number(sum(cw:Body/cw:UniversalTransaction/cw:TransactionInfo/cw:PostingJournalCollection/cw:PostingJournal[cw:ChargeCode/cw:Code='CONGESC' or cw:ChargeCode/cw:Code='FRT' or cw:ChargeCode/cw:Code='GRI' or cw:ChargeCode/cw:Code='PIER']/cw:ChargeTotalAmount), '#.0000')"/>-->
    				<xsl:value-of select="format-number(sum(cw:Body/cw:UniversalTransaction/cw:TransactionInfo/cw:PostingJournalCollection/cw:PostingJournal[contains('_CONGESC_FRT_GRI_PIER_', concat('_',cw:ChargeCode/cw:Code,'_'))]/cw:ChargeTotalAmount), '#.0000')"/>
    			</FREIGHT>
    		</tblFreight_Excel_Upload>
    	</xsl:template>
    </xsl:stylesheet>

    • Marked as answer by Maheshma Tuesday, February 19, 2019 3:06 PM
    Tuesday, February 19, 2019 7:26 AM
  • hi Yitzhak,

    My issue sorted now, Thank you very much for your support and guidance.

    Thanks

    Mahesh

    Tuesday, February 19, 2019 3:09 PM