Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Replacing the last record in xml with different tags

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replacing the last record in xml with different tags
# 1  
Old 07-08-2010
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
Code:
 
/* 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*/

Code:
 
/*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,
Code:
<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:
Code:
 
<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
# 2  
Old 07-08-2010
You can use a small perl script like this:
Code:
#!/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:
Code:
script.pl xmlfile

# 3  
Old 07-08-2010
A solution with AWK :
Code:
awk '

function print_RevenueAmounts() {
   if ( count ) {
      for (i=1; i<=count; i++)
         print Ra[i];
   }
   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[i] !~ /<\/?(Credit|Debit)/ )
         gsub(/<\/?[[:alpha:]_]+/,"&_total", Ra[i]);
   }
   print_RevenueAmounts()
   print "</ReportTrailer>";
   print;
   next;
}

1

' file.xml

Jean-Pierre.
# 4  
Old 07-09-2010
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
Code:
<RevenueAmounts_total element_total=”2”>  </RevenueAmounts>

This could have been
Code:
</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:
Code:
<Resource>Money</Resource>
  <Ref_Id>0</Ref_Id>

These tags also converted as
Code:
<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”.
Code:
 
/*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.
# 5  
Old 07-09-2010
Replace :
Code:
      if ( Ra[i] !~ /<\/?(Credit|Debit)/ )

with
Code:
      if ( Ra[i] !~ /<\/?(Credit|Debit|Resource|Ref_Id)/ )

Jean-Pierre.
This User Gave Thanks to aigles For This Post:
LinuxLearner
# 6  
Old 07-09-2010
You can also use an XSLT stylesheet to transform your document
Code:
<?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.
# 7  
Old 07-19-2010
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?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Replacing tab value in xml file

Hi I am working on xml file. I have to make sure below lines containing values within quotes are replaced by some character like "-". Below are different lines in xml file. Pattern 1: <Connector port=.... ..... keystoremyPass="xxx" /> Pattern 2: myword="xxxxx" ... (11 Replies)
Discussion started by: krsnadasa
11 Replies

2. Shell Programming and Scripting

Extract timestamp from first record in xml file and it checks if not it will replace first record

I have test.xml <emp><id>101</id><name>AAA</name><date>06/06/14 1811</date></emp> <Join><id>101</id><city>london</city><date>06/06/14 2011</date></join> <Join><id>101</id><city>new york</city><date>06/06/14 1811</date></join> <Join><id>101</id><city>sydney</city><date>06/06/14... (2 Replies)
Discussion started by: vsraju
2 Replies

3. Shell Programming and Scripting

Replacing a value in all xml's

Hi Folks, Could you please advise what will be the unix command to replace the character in all xml's under a particular directory for example let say I rite now at the following below location $ cd /opt/apr/rt/conf now under conf there are so many xml's and in those xml's i want to... (2 Replies)
Discussion started by: punpun66
2 Replies

4. Shell Programming and Scripting

Shell Command to compare two xml lines while ignoring xml tags

I've got two different files and want to compare them. File 1 : HTML Code: <response ticketId="944" type="getQueryResults"><status>COMPLETE</status><description>Query results fetched successfully</description><recordSet totalCount="1" type="sms_records"><record... (1 Reply)
Discussion started by: Shaishav Shah
1 Replies

5. Shell Programming and Scripting

How to add the multiple lines of xml tags before a particular xml tag in a file

Hi All, I'm stuck with adding multiple lines(irrespective of line number) to a file before a particular xml tag. Please help me. <A>testing_Location</A> <value>LA</value> <zone>US</zone> <B>Region</B> <value>Russia</value> <zone>Washington</zone> <C>Country</C>... (0 Replies)
Discussion started by: mjavalkar
0 Replies

6. Shell Programming and Scripting

Reading XML and replacing a string

Hi All, My thanks in advance for you guys reading this and for any posts. I'm having 100 XML files, I need script which replace a specific string. It must be incrementing in 100 xml files.. Sample XML files: <hwIPHostName type="attrib">DEMO1</hwIPHostName> I need Demo1 to be... (4 Replies)
Discussion started by: karty2129
4 Replies

7. Shell Programming and Scripting

Replacing part of XML code inside comment tags

Hello! I'd like to modify custom values in a XML config file between comment tags using bash script. <feature> <keyboardshortcut>C-m</keyboardshortcut> <option1>disabled</option2> <option2>enabled</option2> </feature> <!-- bash script features START --> <feature> ... (2 Replies)
Discussion started by: prism1
2 Replies

8. Shell Programming and Scripting

Replacing number between xml tags with ksh shell script

Hallo, im basically a complete noob on shell scripting and im trying to replace or rather add 1 to a number between xml tags. The xml basically has a tag somewhere that looks like this: <tag>12345678901234</tag> Now i want to replace the number between the tags. And i want the file to... (6 Replies)
Discussion started by: Demoric
6 Replies

9. UNIX for Advanced & Expert Users

How to read an Xml record contained in a file--urgent

Hi I have an xml file which has multiple xml records.. I don't know how to read those records and pipe them to another shell command the file is like <abc>z<def>y<ghi>x........</ghi></def></abc> (1st record) <jkl>z<mno>y<pqr>x........</pqr></mno></jkl> (2nd record) Each record end... (4 Replies)
Discussion started by: aixjadoo
4 Replies

10. Shell Programming and Scripting

replacing string in an XML file

Hi all, I need to replace string in XML file..XML file like <dependency> <groupId>fr.orange.portail.ear</groupId> <artifactId>_AdminServicesEAR</artifactId> <version>1.0.0-20080521.085352-1</version> <type>ear</type> </dependency> <dependency> ... (2 Replies)
Discussion started by: subin_bala
2 Replies
Login or Register to Ask a Question