awk and or sed command to sum the value in repeating tags in a XML

Shell Programming and Scripting
1

I have a XML in which <Amt Ccy="EUR">3.1</Amt> tag repeats. This is under another tag <Main>. I need to sum all the values of <Amt Ccy=""> (Ccy may vary) coming under <Main> using awk and or sed command.

can some help?

Sample looks like below

<root>
    <Main>
            <someothertag>..</someothertag>
        <Amt Ccy="EUR">3.1</Amt>
    </Main>
                .
                .
                .
                some other tags
    <Main>
          <someothertag>..</someothertag>
             <Amt Ccy="SGD">51</Amt>
    </Main>
    <another>
      <Amt Ccy="EUR">10</Amt>
     </another>
</root>
2

Your example data has different tags than what you specified in your instructions.

<amount>3.1</amount>

vs.

<Amt Ccy="EUR">3.1</Amt>
3

Edited the post

4

This will do the task for the sample file you posted:

awk '/Amt Ccy/ {sum+=$3} END {print sum}' FS="[<>]" file

It relies on the amount line NOT spanning several lines; there are solutions for that case in these forums.

And, be advised that summing up different currencies will almost certainly run you into trouble with the financial guys - awk offers ways to sum up into different curr. arrays. This is left for your exercise.

5

@RudiC: The User is looking to sum up the values which comes between <Main> tags
Yours will include the Amt in <another> tag, can you please re-edit it.

---------- Post updated at 03:36 AM ---------- Previous update was at 03:23 AM ----------

<root>
    <Main>
            <someothertag>..</someothertag>
        <Amt Ccy="EUR">3.1</Amt>
    </Main>
                .
                .
                .
                some other tags
    <Main>
          <someothertag>..</someothertag>
             <Amt Ccy="SGD">51</Amt>
    </Main>
    <another>
      <Amt Ccy="EUR">10</Amt>
     </another>
</root>

# sed -n "/\<Main\>/,/\/Main>/p" file | grep Amt |  sed -e "s/<[^>]*>//g" -e "s/ //g" | awk '{ sum+=$1; } END { print sum }'
54.1
6

Got some idea , after going through Rudic and Satyaonunix post. I could come up with this oneliner

[chidori@test tmp]$ awk '/\<Main\>/,/\/Main>/{if($0~/Amt/){gsub(/[^0-9.]/,"");sum+=$0}}END{ print sum }' infile
54.1
7

Unfortunately neither awk or sed are optimal tools for use with XML documents. You really need to use a stylesheet transformation language such as XSLT.

Suppose our input document is:

<root>
    <main>
        <amount currency="EUR">3.1</amount>
    </main>
    <main>
        <amount currency="SGD">51</amount>
    </main>
    <main>
        <amount currency="USD">72.50</amount>
    </main>
    <main>
        <amount currency="SGD">101</amount>
    </main>
    <main>
        <amount currency="EUR">10</amount>
    </main>
</root>

The following stylesheet

<?xml version="1.0" encoding="iso-8859-1"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="1.0">

    <xsl:output indent="yes"/>
    <xsl:variable name="currencies" select="//amount"/>

    <xsl:template match="/">
        <currency-sums>
            <xsl:for-each select="$currencies">
                <xsl:if test="generate-id(.)=
                              generate-id( $currencies[ @currency =
                              current()/@currency ] )">
                    <sum currency="{@currency}">
                         <xsl:value-of select="sum( $currencies[@currency=
                                               current()/@currency] )"/>
                    </sum>
                </xsl:if>
             </xsl:for-each>
         </currency-sums>
     </xsl:template>

</xsl:stylesheet>

will produce

<?xml version="1.0"?>
<currency-sums>
  <sum currency="EUR">13.1</sum>
  <sum currency="SGD">152</sum>
  <sum currency="USD">72.5</sum>
</currency-sums>

The stylesheet can easily be modified just to produce two columns instead of a XML document

<?xml version="1.0" encoding="iso-8859-1"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="1.0">

    <xsl:output method="text" indent="yes"/>
    <xsl:variable name="currencies" select="//amount"/>

    <xsl:template match="/">
        <currency-sums>
            <xsl:for-each select="$currencies">
                <xsl:if test="generate-id(.)=
                              generate-id( $currencies[ @currency =
                              current()/@currency ] )">
                    <xsl:value-of select="@currency"/><xsl:text>  </xsl:text>
                    <sum currency="{@currency}">
                        <xsl:value-of select="sum( $currencies[@currency=
                                               current()/@currency] )"/>
                    </sum>
                         <xsl:text>
</xsl:text>
                </xsl:if>
             </xsl:for-each>
         </currency-sums>
     </xsl:template>

</xsl:stylesheet>

will produce:

EUR  13.1
SGD  152
USD  72.5