title: Fastest way to generate an xml file with million records in c#
body: <p>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 ?</p>
  
  <p>What is the best possible approach for the same ?</p>
  
  <p><strong>Option 1 :</strong>   Create a clob in Oracle and fetch it in code.<br>
  <strong>Oracle SP :</strong></p>
  
  <pre><code>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 ;
  /
  </code></pre>
  
  <p><strong>C# code :</strong></p>
  
  <pre><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();
      }
  </code></pre>
  
  <p><strong>Option 2 :</strong> Fetch records first and build the xml in c# using XML Writer
  <strong>Option 3 :</strong> Fetch records in batches and build the xml
  My table T1     </p>
  
  <blockquote>
    <p>PARTNER_NO  NAME<br>
    00001       Archie<br>
    00034       Williams<br>
    00046       Mark<br>
    00052       Betty  </p>
  </blockquote>
  
  <p>This is how my XML looks :<br>
  <img src="https://i.stack.imgur.com/1CXMK.jpg" alt="enter image description here"> </p>
  
  <p>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.  </p>
  
  <p><strong>Edited</strong>  </p>
  
  <p>After trying Option 2, I am getting 'ContextSwitchDeadlock' Exception.<br>
  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:  </p>
  
  <pre><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()  
  </code></pre>