c# application to read xml files, parsing them, storing locally in data structure and writing to csv

Question

I have to develop a c# application that will read large xml files. XML files will have data about computer systems (i.e. Hardware, software, network, bios information). There will be separate xml file for each category i.e. hardware xml file, software xml file. A computer id is common attribute in all these xml files. It might be possible that a computer id present in one xml file but not in other. After the reading part is done, and storing data locally, it needs to be written in different csv i.e Hardware.csv, software.csv. Only those id's need to be written that are present in all xml files. I am thinking of the below approach to do this,

1) Read each xml file, store its computer id in data structure like Dictionary (with key being its computer id and value is Arraylist>). Each object will have associated attributes. For Example - Hardware xml will be read and stored in dictionary as <1, hwarraylist>. Each hw object will have attributes as hwmodel, hwmanufacturer etc

2) While reading the second xml file i.e software xml file, if the id already exist in dictionary then a swarraylist will be appended to its value. Otherwise a new computerid is added to dictionary.

3) After all reading is done, I'll write arraylist of only those id's which have number of arraylist = number of xml files. Separate csv for separate arraylist.

Is this approach correct ? Will it be feasible for millions of rows in each xml file ?

One more thing to add, the xml file is as per the below format:

    <result_sets>
      <result_set>
<cs>
  <c>
    <wh>123</wh>
    <dn>Computer ID</dn>
    <rt>1</rt>
  </c>
  <c>
    <wh>112</wh>
    <dn>Name</dn>
    <rt>1</rt>
  </c>
  <c>
    <wh>124</wh>
    <dn>System</dn>
    <rt>1</rt>
  </c>
</cs>
<rs>
  <r>
    <id>8820</id>
    <cid>230</cid>
    <c>
      <v>230</v>
    </c>
    <c>
      <v>Windows</v>
    </c>
    <c>
      <v>system32</v>
    </c>
  </r>
</rs>

Is it possible to parse them using XMLReader or I should treat then as text files and do the processing. Please advise

Answer 1

millions of rows - you have to understand the memory requirements of each row stored, if you have 1 million rows and each row is 1k (including object overheads) then that's a gig of RAM used. Strings can get big, so you might run into memory constraints quite easily.

What you are trying to do here is a classic merge problem (you should write it in COBOL - the classic COBOL program is one that takes a list of employees, and another list of work and merges them together to run payroll!) that works best if the input files are sorted.

If they were sorted in id order, then you can open each and read an entry from each, 1 at a time, if the IDs match write out a row to the output file, then repeat until all input is exhausted. Memory usage is minimal, efficiency is great (as you do not need to store and remember all IDs, they will come to you in order)

So I would work on a sorter for the xml inputs first.

Answer 2

Is this approach correct ?

If you mean "does it match the problem description" - yes, seems to me.

If you mean - "did you understand the requirements correctly" - who knows but you.

If you mean - is there a better approach - there is always a better approach, but maybe what you wrote is sufficient for your case, you will probably find out by trying it out.

What I probably would not do is just appending your category lists to an ArrayList . If you are not very careful, you will end up not knowing which of the different categories is at which index in your list. Using a Dictionary<string,CategoryList> can fix this - where the key is the category name, and the value is a supertype of your different category lists.

Will it be feasible for millions of rows in each xml file ?

That depends on the correct implementation and your available hardware/main memory. If your XML files are very big, you should avoid to read them completely into an XmlDocument, because that it very memory expensive. Better use something like an XmlReader, which processes the file sequentially. Nethertheless you have to test by yourself if holding all the CategoryLists in memory will work in your case.

Answer 3

Millions of rows !! Think performance first :-)

1) You can probably think of creating a WCF service or Windows service to process the XML parsing & CSV generation steps.

2) Use SSIS package to parse the huge XML file into data to be stored into the database. This will help you to churn the massive large XML file into records. Then you can use to generate the CSV file from .net code. Recently, i used SSIS package to process huge record set and it took very less time to churn it.

Since there will be million of records in the XML file, i think it will add plenty of constraint on the memory while parsing the XML into object form.