Exporting data from a table in SQL Server using ASP.Net

Question

I am using Visual Studio 2010 and SQL Server 2008. I need to create a XML file which looks like:

 <cat>
   <name> "Categories"</name>
   <link> "link"</link>
 </cat>

The Categories and link values should be added from the database.

I have tried everything but I cannot get it to work. DO I have to create a XML file in ASP to do this?

These values are in the same table but there are other columns in the table as well.

My code looks something like this:

        SqlCommand sqlcmd = new SqlCommand();
        //sqlcmd.CommandText = "Select * from Categories";
        DataSet ds = new DataSet();

        System.Data.SqlClient.SqlDataAdapter da = new       System.Data.SqlClient.SqlDataAdapter("Select * from Categories", "Data Source=something;Initial Catalog=My Database;Integrated Security=True");
        da.Fill(ds);

      int rows;
      rows = ds.Tables[0].Rows.Count;

      int i;

      for (i = 0; i <= rows - 1; i++)
      {
          string Categories = ds.Tables[0].Rows[i].ItemArray[0].ToString();
          string address = "https://www.something.com/";
          string link = address + ds.Tables[0].Rows[i].ItemArray[1].ToString();
      }

      ds.WriteXml(@"c:\output.xml", XmlWriteMode.WriteSchema);

Can someone please give me a detailed solution to this problem.

Thank you

AnandPhadke · Accepted Answer · 2012-09-17 12:34:33Z

1

SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandText = "Select * from Categories";
DataSet ds = new DataSet();

System.Data.SqlClient.SqlDataAdapter da = new       System.Data.SqlClient.SqlDataAdapter("Select * from Categories", "Data Source=something;Initial Catalog=My Database;Integrated Security=True");
da.Fill(ds);

      int rows;
      rows = ds.Tables[0].Rows.Count;

      int i;
xmlwr.WriteStartElement("Cat");
      for (i = 0; i <= rows - 1; i++)
      {
xmlwr.WriteStartElement("name");
xmlwr.xmlwr.WriteString(ds.Tables[0].Rows[i].ItemArray[0].ToString());
xmlwr.WriteEndElement;

xmlwr.WriteStartElement("link");
xmlwr.xmlwr.WriteString(ds.Tables[0].Rows[i].ItemArray[1].ToString());
xmlwr.WriteEndElement;

      }
xmlwr.WriteEndElement;
      ds.WriteXml(@"c:\output.xml", XmlWriteMode.WriteSchema);

answered Sep 17, 2012 at 12:34

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Lone coder Over a year ago

Thank you. But It displays an error at "xmlwr". Do I need to define something??

Lone coder Over a year ago

and what if I need something like <name = "some name" date = "today's date" time = "current time"> to be displayed at the top of the XML document??

AnandPhadke Over a year ago

Yes you have to define xmlwr. And if you need like what you mentioned in comment,then use WriteAttributeString

Lone coder Over a year ago

Don't you think that da = fill(ds) will fill the table with all the columns from the database before I add my selected values to it??

AnandPhadke Over a year ago

Yes this will ass all the columns from Categories table.If you want specific columns then use "select col1,col2 from categories"

|

Collectives™ on Stack Overflow

Exporting data from a table in SQL Server using ASP.Net

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related