using System;

using System.Data.SqlClient;

using System.Xml;

using System.IO;

​

using (SqlConnection connection = new SqlConnection("Server=localhost;Database=employeedetails;Integrated Security=SSPI"))

{

    connection.Open();

    int rowCount = 0;

    int fileIndex = 1;

    int currentOffset = 0;

    int batchSize = 1000;

    using (SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM [lb_wallet].[account];", connection))

    {

        rowCount = (int)command.ExecuteScalar();

    }

​

    while (currentOffset < rowCount)

    {

        using (SqlCommand command = new SqlCommand("WITH account_cte AS (SELECT *, ROW_NUMBER() OVER (ORDER BY id) as row_num FROM [lb_wallet].[account]) SELECT * FROM account_cte WHERE row_num > @CurrentOffset AND row_num <= @CurrentOffset + @BatchSize FOR XML PATH(''), ROOT('report');", connection))

        {

            command.Parameters.AddWithValue("@CurrentOffset", currentOffset);

            command.Parameters.AddWithValue("@BatchSize", batchSize);

            using (XmlReader reader = command.ExecuteXmlReader())

            {

                reader.Read();

                string fileName = "C:\\Users\\didula\\Desktop\\XML SP Files\\report_" + fileIndex + ".xml";

                using (StreamWriter writer = new StreamWriter(fileName))

                {

                    using (XmlReader xml = reader.ReadSubtree())

                    {

                        writer.WriteLine(xml.ReadOuterXml());

                    }

                }

​

                currentOffset += batchSize;

                fileIndex++;

            }

        }

    }

}