You were creating new xml file inside loop, which was deleting old xml file
Below is updated code.
public void drtoXML()
{
SqlConnection Conn = new SqlConnection("Data Source=;Initial Catalog=Employee;");
string sqlText = "select * from EMP FOR XML AUTO";
SqlCommand dataCmd = new SqlCommand(sqlText, Conn);
XmlWriter writer = null;
Conn.Open();
XmlReader dataReader = dataCmd.ExecuteXmlReader();
using (writer = XmlWriter.Create(@"D:\Manas\Prod.xml"))
{
while (dataReader.Read())
{
writer.WriteNode(dataReader, true);
}
}
dataReader.Close();
Conn.Close();
}