Xml file to CSV output

Question

I have xml file, which is in format as below, and I want to transform it in csv otput as shown below. Unfortunatly I'm not allowed to install xmlstarlet or some other xml parser (I have only xmllint). How can I do this, for example with, awk, sed....

     <xn:VsDataContainer id="site00881">
                            <es:listOfNe>SubNetwork=NL1_R,SubNetwork=AHPTUR14,MeContext=rbs008811,ManagedElement=1</es:listOfNe>
                            <es:listOfNe>SubNetwork=NL1_R,SubNetwork=AHPTUR14,MeContext=rbs008819,ManagedElement=1</es:listOfNe>
                </xn:VsDataContainer>
                <xn:VsDataContainer id="site00882">
                            <es:listOfNe>SubNetwork=NL1_R,SubNetwork=AHPTUR14,MeContext=rbs008821,ManagedElement=1</es:listOfNe>

<es:listOfNe>SubNetwork=NL1_R,SubNetwork=AHPTUR14,MeContext=rbs008829,ManagedElement=1</es:listOfNe>
                </xn:VsDataContainer>
                <xn:VsDataContainer id="site00883">
                            <es:listOfNe>SubNetwork=NL1_R,SubNetwork=ASDTUR13,MeContext=rbs008831,ManagedElement=1</es:listOfNe>
                            <es:listOfNe>SubNetwork=NL1_R,SubNetwork=ASDTUR_SIU,MeContext=siu008832,ManagedElement=siu008832</es:listOfNe>
                </xn:VsDataContainer>
                <xn:VsDataContainer id="site00884">
                            <es:listOfNe>SubNetwork=NL1_R,SubNetwork=AHPTUR14,MeContext=rbs008841,ManagedElement=1</es:listOfNe>
                            <es:listOfNe>SubNetwork=NL1_R,SubNetwork=AHPTUR14,MeContext=rbs008849,ManagedElement=1</es:listOfNe>
                </xn:VsDataContainer>

output should be in csv format

 rbs008811,site00881
 rbs008819,site00881
 rbs008821,site00882
 rbs008829,site00882
 rbs008831,site00883
 siu008832,site00883
 rbs008841,site00884
 rbs008849,site00884

I'm not sure that you can. Attempting to parse XML without an XML parser is a great way to drive yourself insane. Incidentally, why are you "not allowed to install" the tools you need for the job you're doing? — GoBusto
– GoBusto, Commented Feb 10, 2015 at 16:20

Tiago Lopo · Accepted Answer · 2015-02-10 16:39:53Z

2

I would help you with xmllint, but your xml file don't seen to be valid.

Anyway here's a quick and dirty solution, which you should probably avoid:

grep -Po "(rbs|site)\d+" file.xml | awk '/site/{site=$1} /rbs/{print $1","site}' 
rbs008811,site00881
rbs008819,site00881
rbs008821,site00882
rbs008829,site00882
rbs008831,site00883
rbs008841,site00884
rbs008849,site00884

answered Feb 10, 2015 at 16:39

Tiago Lopo

8,0291 gold badge39 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3319356 Over a year ago

it's ok, but I don't have grep with option -oP, it si not installed unfortunatly...supported is -hblcnsviw.

Tiago Lopo Over a year ago

Ok, then replace the grep command by this: perl -lne '/((rbs|site)\d+)/ && print "$1"'

user3319356 Over a year ago

yes it is ok, only needs to be in csv. format. Is it possible to convert it as above?

Tiago Lopo Over a year ago

You need to pipe it to awk to get the csv: perl -lne '/((rbs|site)\d+)/ && print "$1"' file.xml | awk '/site/{site=$1} /rbs/{print $1","site}'

glenn jackman · Accepted Answer · 2015-02-10 21:23:26Z

0

With the usual reservations about parsing XML:

gawk -v OFS=, '
    match($0, /VsDataContainer id="([^"]+)/, m) {container = m[1]} 
    match($0, /MeContext=([^,]+)/, m)           {print m[1], container}
' file

If you don't have GNU awk:

awk -v OFS=, '
    /VsDataContainer id="/ {
        sub(/.*id="/, "")
        sub(/".*/, "")
        container = $0
    } 
    /MeContext=/ {
        sub(/.*MeContext=/, "")
        sub(/,.*/, "")
        print $0, container
    }
' file

edited Feb 10, 2015 at 21:23

answered Feb 10, 2015 at 21:18

glenn jackman

249k42 gold badges233 silver badges362 bronze badges

Collectives™ on Stack Overflow

Xml file to CSV output

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related