Click here to Skip to main content
15,939,779 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I need help to export data from sql database to a xml doucment.

My database content is
SQL
SELECT      newsbee_ID, newsBee, link, title
FROM          tbl_newsBee


and i need to export the dat in that to an xml file and my xml formate is
XML
<newses>
  <news link="URL 1" des="Description 1">Name 1</news>
  <news link="URL 2" des="Description 2">Name 2</news>
  <news link="URL 3" des="Description 3">Name 3</news>
  </newses>


Here des = newsBee and Name = title and link =link

How can I do it in asp.net using c#?

Any help will be a great hand for me.
Posted
Updated 16-Jan-11 21:30pm
v2
Comments
Dalek Dave 17-Jan-11 3:30am    
Edited for Grammar and Readability.

This[^] might help you.
 
Share this answer
 
Comments
Dalek Dave 17-Jan-11 3:30am    
Good Link
As of bijeesh06 suggested if you will write XML using WriteXML method of datatable then you will not get data in a specific format I guess.

For retrieving data in As per format you want you need to go with FOR XML EXPLICIT provided by SQL Server.

You can get more detail into it HERE[^].
 
Share this answer
 
private void WriteXmlMethod()
        {
            string constring = ConfigurationManager.ConnectionStrings["NorthwindConnectionString1"].ToString();
            SqlConnection con = new SqlConnection(constring);
            con.Open();
            string query = "select * from tbl_newsBee";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            XDocument doc = new XDocument();
            XDeclaration declaration = new XDeclaration("1.0","utf-8","yes");
            doc.Add(declaration);
            XElement element1 = new XElement("Newses");
            XElement element2 = null;
            while (dr.Read())
            {
                element2 = new XElement("News",new XAttribute("link", dr[2].ToString()),new XAttribute("des",dr[1].ToString()));
                element2.Add(dr[3].ToString());
                element1.Add(element2);
            }
            con.Close();
            string filename = Server.MapPath("YourFolder/myXmlDoc.xml");
            doc.Save(filename);
        }


Another Answer

private void WriteXmlMethod()
        {
           
            string constring = ConfigurationManager.ConnectionStrings["ConnectionStringNews"].ToString();
            SqlConnection con = new SqlConnection(constring);
            con.Open();
            string query = "select * from tbl_newsBee";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            XmlDocument myXml = new XmlDocument();
            XmlElement el;
            XmlElement root = myXml.CreateElement("newses");
            myXml.AppendChild(root);
            while (dr.Read())
            {
                el = myXml.CreateElement("news");
                el.SetAttribute("link", dr[2].ToString());
                el.SetAttribute("des", dr[1].ToString());
                el.InnerText = dr[3].ToString();
                root.AppendChild(el);
            }
            con.Close();
            myXml.Save(favXml);
        }
 
Share this answer
 
v2
C#
private void WriteXmlMethod()
        {
            string constring = ConfigurationManager.ConnectionStrings["YourConnectionString"].ToString();
            SqlConnection con = new SqlConnection(constring);
            con.Open();
            string query = "SELECT newsbee_ID, newsBee, title FROM  tbl_newsBee";
            SqlCommand cmd=new SqlCommand(query,con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            string filename =Server.MapPath("YourFolder/myXmlDoc.xml");
           
            System.IO.FileStream myFileStream = new System.IO.FileStream
            (filename, System.IO.FileMode.Create);
            System.Xml.XmlTextWriter myXmlWriter =new System.Xml.XmlTextWriter(myFileStream, System.Text.Encoding.Unicode);
            ds.WriteXml(myXmlWriter);
            myXmlWriter.Close();
        }
 
Share this answer
 
v2
Comments
Dalek Dave 17-Jan-11 3:30am    
Good Answer.
the above answer of bijeesh06 didn't full filled my xml document creation. i need the xml in same format..

thanks
 
Share this answer
 
Comments
Estys 17-Jan-11 9:23am    
Why don't you use the "FOR XML EXPLICIT" solution as suggested? No SQLSERVER?
nikhulion 17-Jan-11 13:00pm    
i like to add "FOR XML EXPLICIT". But the thing is i don't know. i'm just learning .net c# by doing my own idea in project.
Mr bijeesh i just changed some codes and formated as this

public string favXml = "E:/My Webs/NIBS Website/www.microsquirrel.com/www.microsquirrel.com/News/news.xml";


private void WriteXmlMethod()
    {
        string constring = ConfigurationManager.ConnectionStrings["ConnectionStringNews"].ToString();
        SqlConnection con = new SqlConnection(constring);
        con.Open();
        string query = "select * from tbl_newsBee";
        SqlCommand cmd = new SqlCommand(query, con);
        SqlDataReader dr = cmd.ExecuteReader();
        
        XmlDocument myXml = new XmlDocument();
        XmlElement el = myXml.CreateElement("news");
while(dr.read())
{
        el.SetAttribute("link", dr[2].ToString());
        el.SetAttribute("des", dr[1].ToString());
        el.InnerText = dr[3].ToString();
        if (!File.Exists(favXml))
        {
            XmlElement root = myXml.CreateElement("newses");
            myXml.AppendChild(root);
            root.AppendChild(el);
            LabelMessageDel.Text = "Item Created";
        }
        else
        {
            myXml.RemoveAll();
            XmlElement root = myXml.CreateElement("newses");
            myXml.AppendChild(root);
            root.AppendChild(el);
            LabelMessageDel.Text = "Item Created";
        }
}
        con.Close();
        myXml.Save(favXml); 
}


Here i only get one data written in xml doc, where i have more than one data in my database. how to get write all data at once

How it happen,and one more thing i'm using asp.net 2.0 so LINQ doesn't support.

Thank you.
 
Share this answer
 
v2
private void WriteXmlMethod()
{
string constring = ConfigurationManager.ConnectionStrings["ConnectionStringNews"].ToString();
SqlConnection con = new SqlConnection(constring);
con.Open();
string query = "select * from tbl_newsBee";
SqlCommand cmd = new SqlCommand(query, con);
SqlDataReader dr = cmd.ExecuteReader();
XmlDocument myXml = new XmlDocument();
XmlElement el;
XmlElement root = myXml.CreateElement("newses");
myXml.AppendChild(root);
while (dr.Read())
{
el = myXml.CreateElement("news");
el.SetAttribute("link", dr[2].ToString());
el.SetAttribute("des", dr[1].ToString());
el.InnerText = dr[3].ToString();
root.AppendChild(el);
}
con.Close();
myXml.Save(favXml);
}
 
Share this answer
 
Comments
becket_c 22-Feb-12 10:49am    
Hi, I am looking for something similar but I'm a beginner in C#. Considering the solution, whuch references will I use in the program and how would I provide the credentials to the sql backend in the code?

Please assist?
thank you bijeesh for helping...
 
Share this answer
 
Comments
becket_c 22-Feb-12 10:49am    
Hi, I am looking for something similar but I'm a beginner in C#. Considering the solution, whuch references will I use in the program and how would I provide the credentials to the sql backend in the code?

Please assist?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900