Replacing the last record in xml with different tags

I have special requirement, my system provided the xml file as below(available xml file) and I need to convert it as below desired xml file.
is it possible thru shell scripts or awk?

What I need is :
my available xml contains number of records with tags <RevenueAmounts>, the last of record is the summary record.
I need to replace the tags for the last record as shown in below sample. I need to provide the count of the record also,
desired changes are marked in different colors

 
/* avaialable xml file _start*/
<Report>
<RevenueAmounts element="1">        
                <GrossAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </GrossAccount>
                <DiscountAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>0</Debit>
                </DiscountAccount>
                <NetAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </NetAccount>
               </RevenueAmounts>
<RevenueAmounts element="2">        
                <GrossAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </GrossAccount>
                <DiscountAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>0</Debit>
                </DiscountAccount>
                <NetAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </NetAccount>
                </RevenueAmounts>
<RevenueAmounts element="3">        
                <GrossAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>14.90000</Debit>
                </GrossAccount>
                <DiscountAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>0</Debit>
                </DiscountAccount>
                <NetAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>14.90000</Debit>
                </NetAccount>
               </RevenueAmounts>
</Report>
/*Available xml file _end*/

 
/*desired xml file */
<Report>
<RevenueAmounts element="1">        
                <GrossAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </GrossAccount>
                <DiscountAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>0</Debit>
                </DiscountAccount>
                <NetAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </NetAccount>
                </RevenueAmounts>
<RevenueAmounts element="2">        
                <GrossAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </GrossAccount>
                <DiscountAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>0</Debit>
                </DiscountAccount>
                <NetAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </NetAccount>
                </RevenueAmounts>
<ReportTrailer>
<RevenueAmounts_total element_total="2">        
               <GrossAccount_total>
                       <Credit>0</Credit>
                       <Debit>14.90000</Debit>
               </GrossAccount_total>
               <DiscountAccount_total>
                       <Credit>0</Credit>
                       <Debit>0</Debit>
               </DiscountAccount_total>
               <NetAccount_total>
                       <Credit>0</Credit>
                       <Debit>14.90000</Debit>
               </NetAccount_total>
               </RevenueAmounts>
</ReportTrailer>
</Report>

to conclude,

<RevenueAmounts element="3">        
                <GrossAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>14.90000</Debit>
                </GrossAccount>
                <DiscountAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>0</Debit>
                </DiscountAccount>
                <NetAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>14.90000</Debit>
                </NetAccount>
               </RevenueAmounts>

I need to replace the above record with below:

 
<ReportTrailer>
<RevenueAmounts_total element_total="2">        
                <GrossAccount_total>
                        <Credit>0</Credit>
                        <Debit>14.90000</Debit>
                </GrossAccount_total>
                <DiscountAccount_total>
                        <Credit>0</Credit>
                        <Debit>0</Debit>
                </DiscountAccount_total>
                <NetAccount_total>
                        <Credit>0</Credit>
                        <Debit>14.90000</Debit>
                </NetAccount_total>
                </RevenueAmounts_total>
</ReportTrailer>

Any suggestion on how to do this very much appriciated.

Thank you very much

You can use a small perl script like this:

#!/usr/bin/perl -pi

if ( s/(<RevenueAmounts)(\s+)(element)=\"3\">/\1_total\2\3_total=\"2\">/ ) { 
   print "<ReportTrailer>\n"; 
   $c=1;
   }

if ( $c ) {
   /<(\/*\w+)[^<]*>/;
  $d=0;
  for ( $1 )  {
     /Credit|Debit|Revenue/ and do {last;} ;
     /^\/Report$/  and do { print "<\/ReportTrailer>\n";last; } ;
     /.*/  and do { $d=1;last; } ;
     }
  s/(<\/*\w+)[^<]*>/\1_total>/ unless ( ! $d );
  }

Usage:

script.pl xmlfile

A solution with AWK :

awk '

function print_RevenueAmounts() {
   if ( count ) {
      for (i=1; i<=count; i++)
         print Ra;
   }
   count = 0;
}

/^[[:space:]]*<RevenueAmounts/ {
   print_RevenueAmounts();
   elementTotal++;
}

/^[[:space:]]*<RevenueAmounts/,/^[[:space:]]*<\/RevenueAmounts/ {
   Ra[++count] = $0;
   next;
}

/^[[:space:]]*<\/Report>/ {
   print "<ReportTrailer>";
   sub(/element="[0-9]*"/, "element_total=\"" elementTotal-1 "\"", Ra[1]);
   for (i=1; i<=count; i++) {
      if ( Ra !~ /<\/?(Credit|Debit)/ )
         gsub(/<\/?[[:alpha:]_]+/,"&_total", Ra);
   }
   print_RevenueAmounts()
   print "</ReportTrailer>";
   print;
   next;
}

1

' file.xml

Jean-Pierre.

I have tested both the script.
Thank you very much Klashxx and Aigles(Jean-Pierre).

Klashxx, your script is good and works for given sample file.
If I have more records its repeating the Trailer record.
Also in the trailer record its printing with the tags

<RevenueAmounts_total element_total=�2�>  </RevenueAmounts>

This could have been

</RevenueAmounts_total>

This is may be due to typo in my initial post.

Aigels(Jean-Pierre ),
You script is awesome, its works for more number of records that means its generic.
I made a mistake in my initial post for available xml file, I missed couple of tags in the last record, as below:

<Resource>Money</Resource>
  <Ref_Id>0</Ref_Id> 

These tags also converted as

<Resource_total>Money</Resource_total>
                <Ref_Id_total>0</Ref_Id_total>

Actually, these tags are exceptional, not to be displayed as totals. But as it is. I know Its my mistake I didn't mentioned it earlier.
I tried to change the script but could not understand the script as its complex for me.
I am pasting the new �available xml with changes�.

 
/*available xml start*/
<Report>
<RevenueAmounts element="0">
                <GrossAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </GrossAccount>
                <DiscountAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>0</Debit>
                </DiscountAccount>
                <NetAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </NetAccount>
               </RevenueAmounts>
<RevenueAmounts element="1">
                <GrossAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </GrossAccount>
                <DiscountAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>0</Debit>
                </DiscountAccount>
                <NetAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </NetAccount>
                </RevenueAmounts>
<RevenueAmounts element="3">
                <GrossAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </GrossAccount>
                <DiscountAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>0</Debit>
                </DiscountAccount>
                <NetAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>7.45000</Debit>
                </NetAccount>
                </RevenueAmounts>
<RevenueAmounts element="4">
                <Resource>Money</Resource>
                <Ref_Id>0</Ref_Id>
                <GrossAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>21.90000</Debit>
                </GrossAccount>
                <DiscountAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>0</Debit>
                </DiscountAccount>
                <NetAccount name="monthly">
                        <Credit>0</Credit>
                        <Debit>21.90000</Debit>
                </NetAccount>
               </RevenueAmounts>
</Report>
/*available xml end*/

Thanks you very much.

Replace :

      if ( Ra !~ /<\/?(Credit|Debit)/ )

with

      if ( Ra !~ /<\/?(Credit|Debit|Resource|Ref_Id)/ )

Jean-Pierre.

You can also use an XSLT stylesheet to transform your document

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="2.0" 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

   <xsl:output method="xml" indent="yes"/>

   <xsl:template match="*">
       <xsl:copy><xsl:apply-templates /></xsl:copy>
   </xsl:template>

   <xsl:template match="RevenueAmounts[position() = last()]">
    <xsl:variable name="count" select="count(//RevenueAmounts) - 1"/>
    <xsl:text>     </xsl:text><ReportTrailer><xsl:text>
         </xsl:text><RevenueAmounts_total element_total="{$count}"><xsl:text>
             </xsl:text><GrossAccount_total><xsl:text>
                  </xsl:text><Credit><xsl:value-of select="./GrossAccount/Credit"/></Credit><xsl:text>
                  </xsl:text><Debit><xsl:value-of select="./GrossAccount/Debit"/></Debit><xsl:text>
              </xsl:text></GrossAccount_total><xsl:text>
              </xsl:text><DiscountAccount_total><xsl:text>
                  </xsl:text><Credit><xsl:value-of select="./DiscountAccount/Credit"/></Credit><xsl:text>
                  </xsl:text><Debit><xsl:value-of select="./DiscountAccount/Debit"/></Debit><xsl:text>
              </xsl:text></DiscountAccount_total><xsl:text>
              </xsl:text><NetAccount_total><xsl:text>
                  </xsl:text><Credit><xsl:value-of select="./NetAccount/Credit"/></Credit><xsl:text>
                  </xsl:text><Debit><xsl:value-of select="./NetAccount/Debit"/></Debit><xsl:text>
              </xsl:text></NetAccount_total><xsl:text>
         </xsl:text></RevenueAmounts_total><xsl:text>
    </xsl:text></ReportTrailer>
   </xsl:template>

</xsl:stylesheet>

All the <xsl:text> elements are in the stylesheet to output white-space in the resultant document. Remember an XSL processor is not a formatting engine. If layout is not an issue, just remove them.

The stylesheet can handle any number of RevenueAmount nodes with the last RevenueAmount node being converted to a ReportTrailer node.

Hi fpmurphy,
thanks for the different solution,
is it possible to do XSLT stylesheet transformation in unix environment, I need the resultant fil e to be available on unix system.
Any utility available to transform on unix, could you please provide any link please?