Parsing XML file

ms2001 · May 22, 2014, 12:32pm

I want to parse xml file
sample file.......

<name locale="en">my_name<>/name><lastChanged>somedate</lastChanged><some more code here>
<name locale="en">tablename1<>/name><lastChanged>somedate</lastChanged>
 <definition><dbquery><sources><sql type="cognos">select * from tablename1</sql><lastChanged>somedate</lastChanged><somemorecode here><name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><lastChanged>somedate</lastChanged><abc>bbbgbssx</<abc>
<name locale="en">tablename2<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename2</sql><lastChanged>somedate</lastChanged><somemorecode here>
<name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><abc>bbbgbssx</<abc>
<name locale="en">tablename3<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename1</sql><somemorecode here><name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><name locale="en">col3<>/name><abc>bbbgbssx</<abc><usage>attribute</usage><datatype>char</datatype><collectionSequenceName>en</collectionSequenceName><collectionSequenceLevel>1</collectionSequenceLevel><querySubject status="sometext"><name locale="en">This is important</name><lastChanged>somedate</lastChanged><definition><modelQuery><sql type=cognos">select 
 tablename1.col1 as colname,
 tablename1.col2 as colname1,
 tablename2.col1 as colname2,
coalesce(tablename3.col1,0) as colname3
 from 
tablename1 join
tablename2
join
tablename3</sql></modelQuery></definition><lastChanged>somedate</lastChanged><name locale="en">tablename4<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename1</sql><lastChanged>somedate</lastChanged><somemorecode here><name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><lastChanged>somedate</lastChanged><abc>bbbgbssx</<abc><name locale="en">tablename5<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename2</sql><lastChanged>somedate</lastChanged><somemorecode here>
 <name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><abc>bbbgbssx</<abc>
 <name locale="en">tablename6<>/name><lastChanged>somedate</lastChanged>
 <definition><dbquery><sources><sql type="cognos">select * from tablename4</sql><somemorecode here>
 <name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><name locale="en">col3<>/name><abc>bbbgbssx</<abc><usage>attribute</usage><datatype>char</datatype><collectionSequenceName>en</collectionSequenceName><collectionSequenceLevel>1</collectionSequenceLevel><querySubject status="sometext"><name locale="en">This is also important</name><lastChanged>somedate</lastChanged><definition><modelQuery><sql type=cognos">select 
 tablename4.col1 as colname,
 tablename4.col2 as colname1,
 tablename5.col1 as colname2,
 (tablename5.col1*10) as colname3
 from 
tablename4 join
tablename4
</sql></modelQuery></definition><lastChanged>somedate</lastChanged>
<some more here similar to this>
<some more here similar to this>
<some more here similar to this>
<some more here similar to this>
<some more here similar to this>

there are N number of similar blocks within the same pattern in a single xml file with some other unnecessary text.
So first I have to extract these blocks into a single file and the finally

I need output like below into a file redirected

 This is important,tablename1,col1,colname,
This is important,tablename1,col2,colname1,
This is important,tablename2,col1,colname2,
This is important,tablename3,col1,colname3,
This is also important,tablename4,col1,colname,
This is also important,tablename4,col2,colname1,
This is also important,tablename5,col1,colname2,
This is also important,tablename5,col1,colname3

----like this

hope I have explained the whole scenario, please help

Thanks in advance

Akshay_Hegde · May 22, 2014, 1:36pm

Welcome to forums, Sorry your post is not clear, please modify.

Don_Cragun · May 22, 2014, 2:34pm

I agree with Akshay that your requirements are not clear.

It looks like you're saying that you have a file that contains blocks text where the first line in a block is:

- - - - -

and the last two lines in a block are:

 - - - - - 
 - - - -

For output you seem to want a list of comma separated lines using the data from the x XML tag in that block and lines where the 3rd field is "as" for each input block and you want the output blocks separated by the input block terminator with the leading spaces removed.

These are both pretty strange formats for an XML input file and for a CSV output file.

Please give us a clear description of your input file format and your output file format. (And use CODE tags so we can tell where leading and trailing spaces are present and where multiple adjacent spaces or tabs appear in your input and output.)

If you show us code that you have written to try to solve your problem it will help us understand what you're trying to do.

pilnet101 · May 22, 2014, 4:15pm

Making the assumption that the '-----' characters are simply there to indicate where other text might be...try this one liner:

awk '/<sql>/{f++};/^from/{f && f--}f' xmlfile|awk -F"[<>]" '{a=$0};{if (a ~ "select") {c=$3}};{gsub(/[\.]/,",");gsub(/ as /,",");gsub(/,$/,"");{if ($0~",") print c","$0}}'

ms2001 · May 29, 2014, 1:34pm

<name locale="en">my_name<>/name><lastChanged>somedate</lastChanged><some more code here>
<name locale="en">tablename1<>/name><lastChanged>somedate</lastChanged>
<definition><dbquery><sources><sql type="cognos">select * from tablename1</sql><lastChanged>somedate</lastChanged><somemorecode here><name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><lastChanged>somedate</lastChanged><abc>bbbgbssx</<abc>
<name locale="en">tablename2<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename2</sql><lastChanged>somedate</lastChanged><somemorecode here>
<name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><abc>bbbgbssx</<abc>
<name locale="en">tablename3<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename1</sql><somemorecode here><name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><name locale="en">col3<>/name><abc>bbbgbssx</<abc><usage>attribute</usage><datatype>char</datatype><collectionSequenceName>en</collectionSequenceName><collectionSequenceLevel>1</collectionSequenceLevel><querySubject status="sometext"><name locale="en">This is important</name><lastChanged>somedate</lastChanged><definition><modelQuery><sql type=cognos">select 
tablename1.col1 as colname,
tablename1.col2 as colname1,
tablename2.col1 as colname2,
coalesce(tablename3.col1,0) as colname3
from 
tablename1 join
tablename2
join
tablename3</sql></modelQuery></definition><lastChanged>somedate</lastChanged><name locale="en">tablename4<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename1</sql><lastChanged>somedate</lastChanged><somemorecode here><name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><lastChanged>somedate</lastChanged><abc>bbbgbssx</<abc><name locale="en">tablename5<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename2</sql><lastChanged>somedate</lastChanged><somemorecode here>
<name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><abc>bbbgbssx</<abc>
<name locale="en">tablename6<>/name><lastChanged>somedate</lastChanged>
<definition><dbquery><sources><sql type="cognos">select * from tablename4</sql><somemorecode here>
<name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><name locale="en">col3<>/name><abc>bbbgbssx</<abc><usage>attribute</usage><datatype>char</datatype><collectionSequenceName>en</collectionSequenceName><collectionSequenceLevel>1</collectionSequenceLevel><querySubject status="sometext"><name locale="en">This is also important</name><lastChanged>somedate</lastChanged><definition><modelQuery><sql type=cognos">select 
tablename4.col1 as colname,
tablename4.col2 as colname1,
tablename5.col1 as colname2,
(tablename5.col1*10) as colname3
from 
tablename4 join
tablename4
</sql></modelQuery></definition><lastChanged>somedate</lastChanged>
<some more here similar to this>
<some more here similar to this>
<some more here similar to this>
<some more here similar to this>
<some more here similar to this>

Akshay_Hegde · June 1, 2014, 1:59pm

@ms2001 : if you just post sample data , what we have to think ? don't think that others will guess and answer you., from your last 2 sample it looks like you did not read Don Cragun's answer., without clear description we can't help.

pilnet101 · June 1, 2014, 3:14pm

You changed your sample data after I posted a potential solution. Try this below it should work on the new sample data:

awk '/>select[[:space:]]*$/{f++};/^from/{f && f--}f' xmlfile|awk -F"[<>]" '{a=$0};{if (a ~ "select") {c=$(NF-12)","}};{gsub(/[\.]/,",");gsub(/ as /,",");gsub(/,$/,"");{gsub(/.*\(/,"");gsub(/[[:punct:]][0-9]*\)/,"")};{if ($0~",") print c$0}}'

ms2001 · June 3, 2014, 10:44am

there are xml files similar to the sample code. I am trying to parse these files one by one to get certain content in a tabular format. If you look at the xml sample, all these below information are there in the sample along with some others. hope I have clarified my requirement.

The output should be as below

 This is important,tablename1,col1,colname,
This is important,tablename1,col2,colname1,
This is important,tablename2,col1,colname2,
This is important,tablename3,col1,colname3,
This is also important,tablename4,col1,colname,
This is also important,tablename4,col2,colname1,
This is also important,tablename5,col1,colname2,
This is also important,tablename5,col1,colname3

---------- Post updated at 09:44 AM ---------- Previous update was at 09:39 AM ----------

this piece if code is almost working except there are lots of spaces coming in the output and also there are rows where there is just vale in 1st column but rest columns are blanks.

anyway I am trying to remove them

thanks you very much.

Don_Cragun · June 3, 2014, 3:48pm

If you would explicitly state the rules that define the format of the fields you're trying to parse (instead of just giving us a single sample XML file with no description of what you're trying to do), we might be able to help debug your code.

Why is there a space at the start of the 1st line in your output? (It doesn't seem to be present in your XML file.)

Why is there a comma at the end of the output line:

This is important,tablename3,col1,colname3,

when there is no comma there in your XML file?

In addition to lines with the formats:

 tablename2.col1 as colname2,
coalesce(tablename3.col1,0) as colname3
        and
 (tablename5.col1*10) as colname3

what other formats might appear from which you want to extract tablenamedigits , coldigits , and colnamedigitsOptionalPunctuation ?

ms2001 · June 4, 2014, 1:09pm

I have tried to explain the whole scenario again all possible codes etc.
This is my code sample

<name locale="en">my_name<>/name><lastChanged>somedate</lastChanged><some more code here>
<name locale="en">tablename1<>/name><lastChanged>somedate</lastChanged>
<definition><dbquery><sources><sql type="cognos">select * from tablename1</sql><lastChanged>somedate</lastChanged><somemorecode here><name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><lastChanged>somedate</lastChanged><abc>bbbgbssx</<abc>
<name locale="en">tablename2<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename2</sql><lastChanged>somedate</lastChanged><somemorecode here>
<name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><abc>bbbgbssx</<abc>
<name locale="en">tablename3<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename1</sql><somemorecode here><name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><name locale="en">col3<>/name><abc>bbbgbssx</<abc><usage>attribute</usage><datatype>char</datatype><collectionSequenceName>en</collectionSequenceName><collectionSequenceLevel>1</collectionSequenceLevel><querySubject status="sometext"><name locale="en">This is important</name><lastChanged>somedate</lastChanged><definition><modelQuery><sql type=cognos">select 
  tablename1.col1 as colname,
  tablename1.col2 as colname1,
  tablename2.col1 as colname2,
  coalesce(tablename3.col1,0) as colname3
 from 
tablename1 join
tablename2
join
tablename3</sql></modelQuery></definition><lastChanged>somedate</lastChanged><name locale="en">tablename4<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename1</sql><lastChanged>somedate</lastChanged><somemorecode here><name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><lastChanged>somedate</lastChanged><abc>bbbgbssx</<abc><name locale="en">tablename5<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename2</sql><lastChanged>somedate</lastChanged><somemorecode here>
 <name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><abc>bbbgbssx</<abc>
 <name locale="en">tablename6<>/name><lastChanged>somedate</lastChanged>
 <definition><dbquery><sources><sql type="cognos">select * from tablename4</sql><somemorecode here>
 <name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><name locale="en">col3<>/name><abc>bbbgbssx</<abc><usage>attribute</usage><datatype>char</datatype><collectionSequenceName>en</collectionSequenceName><collectionSequenceLevel>1</collectionSequenceLevel><querySubject status="sometext"><name locale="en">This is also important</name><lastChanged>somedate</lastChanged><definition><modelQuery><sql type=cognos">select 
 tablename4.col1 as colname,
 tablename4.col2 as colname1,
 tablename5.col1 as colname2,
 (tablename5.col1*10) as colname3
 from 
tablename4 join
tablename4
</sql></modelQuery></definition><lastChanged>somedate</lastChanged>
<name locale="en">my_name<>/name><lastChanged>somedate</lastChanged><some more code here>
<name locale="en">tablename1<>/name><lastChanged>somedate</lastChanged>
<definition><dbquery><sources><sql type="cognos">select * from tablename1</sql><lastChanged>somedate</lastChanged><somemorecode here><name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><lastChanged>somedate</lastChanged><abc>bbbgbssx</<abc>
<name locale="en">tablename2<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename2</sql><lastChanged>somedate</lastChanged><somemorecode here>
<name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><abc>bbbgbssx</<abc>
<name locale="en">tablename3<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename1</sql><somemorecode here><name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><name locale="en">col3<>/name><abc>bbbgbssx</<abc><usage>attribute</usage><datatype>char</datatype><collectionSequenceName>en</collectionSequenceName><collectionSequenceLevel>1</collectionSequenceLevel><querySubject status="sometext"><name locale="en">This is important</name><lastChanged>somedate</lastChanged><definition><modelQuery><sql type=cognos">select 
  tablename1.col1 as colname,
  tablename1.col2 as colname1,
  tablename2.col1 as colname2,
  coalesce(tablename3.col1,0) as colname3
 from 
tablename1 join
tablename2
join
tablename3</sql></modelQuery></definition><lastChanged>somedate</lastChanged><name locale="en">tablename4<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename1</sql><lastChanged>somedate</lastChanged><somemorecode here><name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><lastChanged>somedate</lastChanged><abc>bbbgbssx</<abc><name locale="en">tablename5<>/name><lastChanged>somedate</lastChanged><definition><dbquery><sources><sql type="cognos">select * from tablename2</sql><lastChanged>somedate</lastChanged><somemorecode here>
 <name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><lastChanged>somedate</lastChanged><name locale="en">col3<>/name><abc>bbbgbssx</<abc>
 <name locale="en">tablename6<>/name><lastChanged>somedate</lastChanged>
 <definition><dbquery><sources><sql type="cognos">select * from tablename4</sql><somemorecode here>
 <name locale="en">col1<>/name><lastChanged>somedate</lastChanged><abc>bbbbssx</<abc><name locale="en">col2<>/name><name locale="en">col3<>/name><abc>bbbgbssx</<abc><usage>attribute</usage><datatype>char</datatype><collectionSequenceName>en</collectionSequenceName><collectionSequenceLevel>1</collectionSequenceLevel><querySubject status="sometext"><name locale="en">This is also important</name><lastChanged>somedate</lastChanged><definition><modelQuery><sql type=cognos">select 
 tablename4.col1 as colname,
 tablename4.col2 as colname1,
 tablename5.col1 as colname2,
 (tablename5.col1*10) as colname3
 from 
tablename4 join
tablename4
</sql></modelQuery></definition><lastChanged>somedate</lastChanged>
<some more here similar to this>
<some more here similar to this>
<some more here similar to this>
<some more here similar to this>
<some more here similar to this>

there are N number of similar lines within the same pattern in a single xml file with some other unnecessary text.
So first I have to extract these blocks into a single file and the finally
I need output like below into a file redirected

This is important,tablename1,col1,colname
This is important,tablename1,col2,colname1
This is important,tablename2,col1,colname2
This is important,tablename3,col1,colname3
This is also important,tablename4,col1,colname
This is also important,tablename4,col2,colname1
This is also important,tablename5,col1,colname2
This is also important,tablename5,col1,colname3

This code posted by pilnet101 is almost working

awk '/>select[[:space:]]*$/{f++};/^from/{f && f--}f' xmlfile|awk -F"[<>]" '{a=$0};{if (a ~ "select") {c=$(NF-12)","}};{gsub(/[\.]/,",");gsub(/ as /,",");gsub(/,$/,"");{gsub(/.*\(/,"");gsub(/[[:punct:]][0-9]*\)/,"")};{if ($0~",") print c$0}}'

except the fact that getting lots of spaces, special characters with output,

Don_Cragun · June 4, 2014, 5:24pm

ms2001:

I have tried to explain the whole scenario again all possible codes etc.
This is my code sample
sample XML code deleted for brevity
there are N number of similar lines within the same pattern in a single xml file with some other unnecessary text.
So first I have to extract these blocks into a single file and the finally
I need output like below into a file redirected
This is important,tablename1,col1,colname
This is important,tablename1,col2,colname1
This is important,tablename2,col1,colname2
This is important,tablename3,col1,colname3
This is also important,tablename4,col1,colname
This is also important,tablename4,col2,colname1
This is also important,tablename5,col1,colname2
This is also important,tablename5,col1,colname3
This code posted by pilnet101 is almost working
awk '/>select[[:space:]]*$/{f++};/^from/{f && f--}f' xmlfile|awk -F"[<>]" '{a=$0};{if (a ~ "select") {c=$(NF-12)","}};{gsub(/[\.]/,",");gsub(/ as /,",");gsub(/,$/,"");{gsub(/.*$/,"");gsub(/[[:punct:]][0-9]*$/,"")};{if ($0~",") print c$0}}'
except the fact that getting lots of spaces, special characters with output,

When I run pilnet101's code with your sample input, I get exactly the output you showed us above.
There are no spaces in that output except the ones between the words This , is , also (in some lines), and important as shown in your desired output.
And the only "special characters" in the output are the <newline> characters that terminate the output lines.

What operating system are you using?

Show us the output you're getting. And, show us the output from piping that output through the command:

od -c

so we can see the extra spaces and special characters.