how can i save XmL data from a URL into my database

Question

I know how to load XML data into a string but I'm unable to store its content(content in the nodes of XML file) into my database. I have already tried many solutions but none of them are working in my case. XML link is:-

http://timesofindia.indiatimes.com/rssfeedstopstories.cms

I just want to save the data that exist in the nodes of the XML file and the XML file is from a URL.

using (var client = new WebClient()) { string xmlData = new System.Net.WebClient().DownloadString("http://timesofindia.indiatimes.com/rssfeedstopstories.cms"); XmlDocument xml = new XmlDocument(); XmlNodeList nodeList = xml.SelectNodes("/rss/channel/item");

            foreach (XmlNode node in nodeList)
            {
                var title = node["title"].InnerText;
                var description = node["description"].InnerText;
                var link = node["link"].InnerText;
                var guid = node["guid"].InnerText;
                var pubdate = node["pubDate"].InnerText;
                SqlConnection cnn = new SqlConnection(@"Data Source=vishal-pc\mssqlserver2012;Initial Catalog=aditya;User ID=sa;password=1234");
                SqlCommand cmd = new SqlCommand("insert into XMLdata (title,description,link,guid,pubdate) VALUES (@xmlData,@xmlData1,@xmlData2,@xmlData3,@xmlData4)", cnn);
                cnn.Open();

                //string sqlForInsert = "INSERT INTO XMLdata (title,description,link,guid,pubdate) VALUES (@xmlData,@xmlData1,@xmlData2,@xmlData3,@xmlData4);";
                int rowsAffected = 0;
                //try
                //{
                //    using (SqlConnection con = new SqlConnection(@"Data Source=vishal-pc\mssqlserver2012;Initial Catalog=aditya;User ID=sa;password=1234"))
                //    using (SqlCommand cmd = new SqlCommand(sqlForInsert, con))
                    {
                        cmd.Parameters.Add(new SqlParameter("@xmlData", title));
                        cmd.Parameters.Add(new SqlParameter("@xmlData1", description));
                        cmd.Parameters.Add(new SqlParameter("@xmlData2", link));
                        cmd.Parameters.Add(new SqlParameter("@xmlData3", guid));
                        cmd.Parameters.Add(new SqlParameter("@xmlData4", pubdate));

                        rowsAffected = cmd.ExecuteNonQuery();
                        cnn.Close();
                    }

Answer 1

Debug your code.
Print exception message.
Check value of rowsAffected.
Check data type of xmlDataField column and the one in the SqlParameter.

Answer 2

Try this all you have to do:

1. Change the connection string
2. Change the insert query as your requirment

3. Add other parameters

   string xmlData = new System.Net.WebClient().DownloadString("http://timesofindia.indiatimes.com/rssfeedstopstories.cms");
   string sqlForInsert = "INSERT INTO TableName (xmlDataField) VALUES (@xmlData);";
   int rowsAffected = 0;
   try
   {
       using (SqlConnection con = new SqlConnection("<ConnectionString>"))
       using (SqlCommand cmd = new SqlCommand(sqlForInsert, con))
       {
           cmd.Parameters.Add(new SqlParameter("xmlData", xmlData));
           con.Open();
           rowsAffected = cmd.ExecuteNonQuery();
       }
   }
   catch (Exception ex) {
       //Exception Handling
   }
   if (rowsAffected > 0)
   {
       //Successful
   }
   else {
       //unsuccesfull
   }
   

Answer 3

enter code here

             using (var client = new WebClient())
             {
            string xmlData = new System.Net.WebClient().DownloadString("http://timesofindia.indiatimes.com/rssfeedstopstories.cms");
              XmlDocument xml = new XmlDocument();
                 xml.LoadXml(xmlData);

            XmlNodeList nodeList = xml.SelectNodes("/rss/channel/item");

            foreach (XmlNode node in nodeList)
            {
                var title = node["title"].InnerText;
                var description = node["description"].InnerText;
                var link = node["link"].InnerText;
                var guid = node["guid"].InnerText;
                var pubdate = node["pubDate"].InnerText;
                SqlConnection cnn = new SqlConnection(@"Data Source=vishal-pc\mssqlserver2012;Initial Catalog=aditya;User ID=sa;password=1234");
                SqlCommand cmd = new SqlCommand("insert into XMLdata (title,description,link,guid,pubdate) VALUES (@xmlData,@xmlData1,@xmlData2,@xmlData3,@xmlData4)", cnn);


                //string sqlForInsert = "INSERT INTO XMLdata (title,description,link,guid,pubdate) VALUES (@xmlData,@xmlData1,@xmlData2,@xmlData3,@xmlData4);";
                int rowsAffected = 0;
                //try
                //{
                //    using (SqlConnection con = new SqlConnection(@"Data Source=vishal-pc\mssqlserver2012;Initial Catalog=aditya;User ID=sa;password=1234"))
                //    using (SqlCommand cmd = new SqlCommand(sqlForInsert, con))
                    {
                        cmd.Parameters.Add(new SqlParameter("@xmlData", title));
                        cmd.Parameters.Add(new SqlParameter("@xmlData1", description));
                        cmd.Parameters.Add(new SqlParameter("@xmlData2", link));
                        cmd.Parameters.Add(new SqlParameter("@xmlData3", guid));
                        cmd.Parameters.Add(new SqlParameter("@xmlData4", pubdate));
                            cnn.Open();

                        rowsAffected = cmd.ExecuteNonQuery();

                        cnn.Close();
                    }

Answer 4

        XElement rssFeeds = null;
        using (var client = new WebClient())
        {
            try
            {
                string rssString = new System.Net.WebClient().DownloadString("http://timesofindia.indiatimes.com/rssfeedstopstories.cms");
                rssFeeds = XElement.Parse(rssString);
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
            }
        }
        if (rssFeeds != null)
        {
            using (SqlConnection con = new SqlConnection(@"Data Source=vishal-pc\mssqlserver2012;Initial Catalog=aditya;User ID=sa;password=1234"))
            {
                con.Open();
                var tran = con.BeginTransaction();
                try
                {
                    rssFeeds.Descendants("item").ToList().ForEach(e =>
                    {
                        string insertSql = @"insert into XMLdata (title,description,link,guid,pubdate) VALUES (@title,@description,@link,@guid,pubdate)";
                        SqlCommand cmd = new SqlCommand(insertSql, con, tran);
                        cmd.Parameters.Add(new SqlParameter("title", e.Element("title").Value));
                        cmd.Parameters.Add(new SqlParameter("description", e.Element("description").Value));
                        cmd.Parameters.Add(new SqlParameter("link", e.Element("link").Value));
                        cmd.Parameters.Add(new SqlParameter("guid", e.Element("guid").Value));
                        cmd.Parameters.Add(new SqlParameter("pubdate", e.Element("pubDate").Value));
                        cmd.ExecuteNonQuery();
                    });
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    Debug.WriteLine(ex.Message);
                }

            }
        }