Generate xml file from sql server in asp.net

NET India

Hello Friends, I'm generating a xml file from sql table in asp.net(c#). Here is my code given below using (SqlConnection mCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString())) { mCon.Open(); string strsql = "Select * From MakeTable Where Active=1 For XML Auto,ELEMENTS"; //string strsql = "Select * From MakeTable Where Active=1 "; SqlCommand mDataCom = new SqlCommand(); mDataCom.Connection = mCon; mDataCom.CommandText = strsql; mDataCom.CommandTimeout = 15; DataSet ds = new DataSet(); XmlReader xmlReader = null; xmlReader = mDataCom.ExecuteXmlReader(); ds.ReadXml(xmlReader); ds.DataSetName = "Halo"; ds.WriteXml("F://Manoj//Autoneed/Rough//XMLFile2.xml"); } but it's giving me output as shown here <MakeTable> <MakeID>10</MakeID> <MakeNameID>36</MakeNameID> <MakeYear>2009</MakeYear> <Active>1</Active> </MakeTable> but if i run my sql command in sql then it's giving me following records as shown below <MakeTable> <MakeID>1</MakeID> <MakeNameID>17</MakeNameID> <MakeYear>2009</MakeYear> <Active>1</Active> </MakeTable> <MakeTable> <MakeID>3</MakeID> <MakeNameID>17</MakeNameID> <MakeYear>2006</MakeYear> <Active>1</Active> </MakeTable> <MakeTable> <MakeID>4</MakeID> <MakeNameID>17</MakeNameID> <MakeYear>2007</MakeYear> <Active>1</Active> </MakeTable> <MakeTable> <MakeID>5</MakeID> <MakeNameID>17</MakeNameID> <MakeYear>2005</MakeYear> <Active>1</Active> </MakeTable> <MakeTable> <MakeID>7</MakeID> <MakeNameID>17</MakeNameID> <MakeYear>2004</MakeYear> <Active>1</Active> </MakeTable> can anybody let me know how can i get this output or where and what change i should make in my code or sqlcommand

compninja25

What happens if you try this?

while (xmlReader.Read())
{
ds.ReadXml(xmlReader);
ds.DataSetName = "Halo";
ds.WriteXml("F://Manoj//Autoneed/Rough//XMLFile2.xml");
}

Knowledge is not power, however, the acquisition and appropriate application of knowledge can make you a very powerful individual.

compninja25

it didn't dawn on me at first glance that you had a dataset in there...so I don't think the above would work. Since it appears to be coming back from the SQL server correctly, what if you avoided the DataSet all together and just saved it straight to a file?

  // create a writer and open the file
  TextWriter tw = new StreamWriter("output.xml");


  while (datareader.read())
  {
  // write a line of text to the file
  tw.WriteLine(datareader.ToString());
  }

  // close the stream
  tw.Close();

Knowledge is not power, however, the acquisition and appropriate application of knowledge can make you a very powerful individual.

www Developerof NET

Is it mandatory for you to write down the file to the server????? why don`t you try

ds.GetXml();

But remember to fill you dataset with the data. which actually returns you a XmlDocument. Debug your code and see what results you get in the document object.

When you fail to plan, you are planning to fail.