Help with parsing xml file

Shell Programming and Scripting
1

Hi,
Need help with parsing xml data in unix and place it in a csv file. My xml file looks like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<iwgroups>
    <nextid value="128">
    </nextid>
    <iwgroup name="RXapproval" id="124" display-name="RXapproval" description="group for RX approval ">
        <user name="iwov">
        </user>
        <user name="m161595">
        </user>
        <user name="m594670">
        </user>
        <work name="iwov">
        </work>
    </iwgroup>
    <iwgroup name="TEXT_EMAIL" id="113" display-name="" description="TEXT email group with permission to start text pool workflow">
        <user name="iwov">
        </user>
        <user name="m123053">
        </user>
        <user name="m270857">
        </user>
        <user name="m363836">
       </user>

I would need to place the output in a csv file in below format:

                 Groupname,          Description,                                                                                Users
RXapproval,        group for RX approval,                                                         iwov m161595 m594670
TEXT_EMAIL,  TEXT email group with permission to start text pool workflow,    iwov m123053 m270857 m363836

Please let me know on what is the best way to do this.

Thanks
Jay

2

You have asked for our help fourteen times. What have you tried to solve this problem?

An XML file usually has matching open and close tags. The sample input you have provided has one opening iwgroups tag and no matching closing tag. The sample input you have provided has two opening iwgroup tags, but only one closing tag. It would seem that the closing iwgroup tag would be the trigger that should terminate processing of an XML input segment and produce an output line in your desired CSV file. How can we be expected to suggest code if you can't give us consistent sample input and output?

A CSV file (with comma as the character separating values) doesn't usually have LOTS of leading and/or trailing spaces in the data. Why are there dozens of seemingly extraneous spaces in your desired sample output?

A quoted field in an XML file usually specifies that the data between the quotes should be the contents of the output field you want in your CSV file. The first description field in your XML file is:

description="group for RX approval "

(note the trailing <space> after approval ), but the output you say you want for that field is:

        group for RX approval

(note the eight leading <space> characters that are not present in the XML file and the missing trailing space after approval ).

If you want our help on this you need to very clearly describe the output format you are trying to produce; not just show us a desired CSV file that does not seem to match the sample XML file you have provided.

Please help us help you!

3

Hi Don,
Sorry for the confusion. Please find my input below:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<iwgroups>
    <nextid value="128">
    </nextid>
    <iwgroup name="AEapproval" id="124" display-name="AEapproval" description="group for AE approval">
        <user name="iwov">
        </user>
        <user name="m161595">
        </user>
        <user name="m594670">
        </user>
        <user name="m803051">
        </user>
    </iwgroup>
    <iwgroup name="OES_EMAIL" id="113" display-name="" description="OES email group with permission to start oes pool workflow">
        <user name="iwov">
        </user>
        <user name="m123053">
        </user>
        <user name="m270857">
        </user>
        <user name="m363836">
        </user>
     </iwgroup>
</iwgroups>

I just need a report with the following fields:

Groupname1:AEapproval
Description: group for AE approval
Users: iwov, m161595, m594670,m803051

Groupname2:OES_EMAIL
Description: OES email group with permission to start oes pool workflow
Users: iwov,m123053,m270857,m363836

Output may not be in a csv file, a text file will also do in the above format. Hope I am clear this time.

Thanks
Ajay

4

A CSV file output is easy to do; it just doesn't look anything like you said you wanted in your original post.

awk '
BEGIN {
	print "Groupname,Description,Users"
	OFS = ","
}
/<iwgroup / {
	groupname = description = $0
	sub(/^.* name="/, "", groupname)
	sub(/".*/, "", groupname)
	sub(/^.* description="/, "", description)
	sub(/".*/, "", description)
}
/<user / {
	gsub(/^.[^"]*"|"[^"]*$/, "")
	users = users == "" ? $0 : users " " $0
}
/<\/iwgroup>/ {
	print groupname, description, users
	users = ""
}' file

producing:

Groupname,Description,Users
AEapproval,group for AE approval,iwov m161595 m594670 m803051
OES_EMAIL,OES email group with permission to start yes pool workflow,iwov m123053 m270857 m363836

with your latest sample input.

If you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk .

5

Thanks Don. It works exactly as per my requirement.

Thanks
Ajay

6

If you would like to have it formatted as post #3, the following is a possible Perl solution:

#!/usr/bin/env perl
use strict;
use warnings;

my @group = ();
my $groupnum = 0;

while(<>){
    if(/<iwgroup name/) {
        my ($name, $description) = /\bname="([^"]*).+\bdescription="([^"]*)/;
        $groupnum++;
        my $meta = "Groupname$groupnum: $name\nDescription: $description\n";
        push @group, [$meta];
        next;
    }
    if(exists $group[0]){
        if(/<user name="([^"]*)/){
            push @{$group[1]}, $1;
        }
    }
    if(/<\/iwgroup>/){
        print "\n" unless $groupnum == 1;
        print "$group[0]->[0]Users: ", join(", ", @{$group[1]}), "\n";
        @group = ();
    }
}

Save as report.pl
Run as perl report.pl ajayakunuri.file > ajayakunuri.report

perl report.pl ajayakunuri.file
Groupname1: AEapproval
Description: group for AE approval
Users: iwov, m161595, m594670, m803051

Groupname2: OES_EMAIL
Description: OES email group with permission to start oes pool workflow
Users: iwov, m123053, m270857, m363836
7

Thanks Aia for your help.

Thanks
Ajay

8

Hi Aia. Can you explain this portion of your script to me please?

print "$group[0]->[0]Users: ", join(", ", @{$group[1]}), "\n";

Specifically this portion:

print "$group[0]->[0]Users: ",

Thanks

9

Hi, craigsky

$group[0]->[0] is a dereference to the first element of a referenced array.
The Users: part has nothing to do with the variable, it is that close to it because of the concatenation and it will display as the beginning of another line since $group[0]->[0] will return a string with a newline already.

Perhaps a display of the whole data structure through the loop, might help you:

$VAR1 = [
          [
            'Groupname1: AEapproval
Description: group for AE approval
'
          ],
          [
            'iwov',
            'm161595',
            'm594670',
            'm803051'
          ]
        ];
$VAR1 = [
          [
            'Groupname2: OES_EMAIL
Description: OES email group with permission to start oes pool workflow
'
          ],
          [
            'iwov',
            'm123053',
            'm270857',
            'm363836'
          ]
        ];

If you're struggling to modify this code to your need in another situation, please, open another thread and post what you have done and we will take a look at it.

10

Son of a gun, thanks Aia!
Could the following been used as well for this?

print "$$group[0]"
11

Nope. That would would imply the following:

my $group = [
            'Groupname1: AEapproval
Description: group for AE approval
'
          ],
          [
            'iwov',
            'm161595',
            'm594670',
            'm803051'
          ];

However what we have is:

my @group = (
          [
            'Groupname1: AEapproval
Description: group for AE approval
'
          ],
          [
            'iwov',
            'm161595',
            'm594670',
            'm803051'
          ]
        );

Or in other terms:

$VAR1 = [
          [
            'Groupname1: AEapproval
Description: group for AE approval
'
          ],
          [
            'iwov',
            'm161595',
            'm594670',
            'm803051'
          ]
        ];
12

Okay. Thanks for pointing that out to me. Appreciate it!