Fastest way to generate an xml file with million records in c#

Question

I have created a Windows application in VS2008 and my requirement is to generate an xml file from a table that fetches a million records. Any pointers on the best possible and fastest approach ?

What is the best possible approach for the same ?

Option 1 : Create a clob in Oracle and fetch it in code.
Oracle SP :

CREATE OR REPLACE PROCEDURE GetBP (BP OUT XMLTYPE) AS
BEGIN

SELECT XMLRoot(
     XMLElement("MASTER_TABLE",
       XMLAgg(XMLElement("PARTNER",

           XMLForest(PARTNER_NO  as "PARTNER_NO"
                    ,'0001' as "ID" )))
   ), VERSION '1.0', STANDALONE YES) AS "RESULT"
INTO BP
from TABLE1
where YEAR LIKE '%2011-2012%';

END GetBP ;
/

C# code :

private void CreateXML() 
    {
        string oradb = "Data Source=(DESCRIPTION="
        + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XPS)(PORT=1226)))"
        + "(CONNECT_DATA=(SERVICE_NAME=DBP0)));"
        + "User Id=scoot;Password=tiger;";

        OracleConnection conn = new OracleConnection(); 
        conn.ConnectionString = oradb;
        conn.Open();

        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        OracleDataAdapter da = new OracleDataAdapter();
        cmd.Parameters.Add("BP", OracleDbType.XmlType).Direction = ParameterDirection.Output;
        cmd.CommandText = "GetBP";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
        string doc = ((Oracle.DataAccess.Types.OracleXmlType)(cmd.Parameters["BP"].Value)).Value;
        File.WriteAllText("C:/Test/BookInfo.xml", doc);
        cmd.Dispose();
        conn.Dispose();
    }

Option 2 : Fetch records first and build the xml in c# using XML Writer Option 3 : Fetch records in batches and build the xml My table T1

PARTNER_NO NAME
00001 Archie
00034 Williams
00046 Mark
00052 Betty

This is how my XML looks :
enter image description here

Please give me examples for the best possible approach. Im trying to search for examples in a similar approach. Not able to find one. Option 1 is taking quite sometime to fetch the records.

Edited

After trying Option 2, I am getting 'ContextSwitchDeadlock' Exception.
I have also tried to change IDataReader to OracleDataReader to increase the fetchsize but it doesn't help. Any pointers please. Below is my code:

cmd.CommandText = "SELECT PARTNER_NO FROM T1 WHERE YEAR LIKE '%2011-2012%'";  
XmlWriter myWriter = XmlWriter.Create("C:/Test/BookInfo.xml")  
myWriter.WriteStartDocument(true); 
using(OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))  
  {  
    reader.FetchSize = reader.RowSize * 5000;  
    myWriter.WriteStartElement("master_table");
    while(reader.Read())
      {  
         myWriter.WriteStartElement("partner");  
         myWriter.WriteElementString("partner_no", reader[0].ToString());  
         myWriter.WriteElementString("id","0008");  
         myWriter.WriteEndElement();  
      }  
 }  

 myWriter.WriteEndDocument();  
 myWriter.Flush();  
 myWriter.Close();  
 cmd.Dispose()

I would expert, Expert the data from oracle, using an oracle tool into a XML file (hopefully), if the oracle expect can still only do CSV, then use a tool to convert the CSV into XML. — Ian Ringrose, Feb 07 '14 at 18:21
FYI, your connection, command, and data adapter should all be in `using` blocks to ensure they are Disposed, even if an exception occurs. — John Saunders, Feb 07 '14 at 18:29
Thanks John, Never thought about that. Will implement that in my code. — SDR, Feb 09 '14 at 18:34

score 0 · Accepted Answer · answered Feb 07 '14 at 18:30

0

You could try the most intuitive approach, iterate over the IDataReader and write to an XmlWriter as you go.

I don't know if that's the fastest way to do it, but it should be the most memory effective, and you wouldn't need to create batches of any sort:

using (IDataReader reader = simpleSelectCommand.ExecuteReader())
{
    while (reader.Read())
    {
        myWriter.WriteStartElement("PARTNER");
        // write subnodes for this record
        myWriter.WriteEndElement();
    }
}

This shouldn't take you alot of time to make, and may just be fast enough for you.

answered Feb 07 '14 at 18:30

C.Evenhuis

25,996
2
58
72

This works well when I fetch 100000 records. But when I try fetching all records I am getting ContextSwitchDeadlock exception. What must I do? – SDR Feb 09 '14 at 18:32
See http://stackoverflow.com/questions/578357/visual-studio-contextswitchdeadlock - this may just have to do with the process taking alot of time. – C.Evenhuis Feb 09 '14 at 22:16
Thanks Evenhuis. This was a probolem due to connectivity over VPN. Works fine over network. – SDR Feb 11 '14 at 14:18
In visual studio hit ctrl+alt+e, it'll bring up exception setings, find the one that says 'Managed Debug Settings, and uncheck ContextSwitchDeadlock. That is there to let you know that something COULD be wrong, but in your case it's just a process that takes a long time. – Bryan Mudge Apr 18 '16 at 20:41

score 0 · Answer 2 · answered Feb 07 '14 at 18:32

XML consumes always a lot of space and thus much time to be transferred from server to client. So, I assume the fastest way is to select the data by simple SELECT (or as Ref-Cursor from function) and build the XML structure in your client. This would be your Option 2.

Fastest way to generate an xml file with million records in c#

2 Answers2