RSS feed data to MS SQL 2005 db

Looking for code that I can integrate in with an ASP.NET vb.net page where I pull text from a news page like cnnmoney on a daily basis and upload the text to a ms sql 2005 db...

not sure how to do this...if you have existing code...links

LVL 10
GlobaLevelProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JayFromPepCommented:
This should get you started....

    Dim strXmlSrc  As String = "http://www.mysite.com/rss/myrssfeed.xml"

    Dim myXmlDoc As XmlDocument = New XmlDocument()
    myXmlDoc.Load(strXmlSrc)

    Dim myStringBuilder As StringBuilder = New StringBuilder()
    Dim myStringWriter  As StringWriter  = New StringWriter(myStringBuilder)

That will pull the rss feed into an xml document that you can then iterate through and pump the articles into a db....

The feed will come in with several items...
<rss version="2.0">
  <channel>
    <title></title>
    <link></link>
    <description></description>
      <item>
        <title></title>
        <link></link>
        <pubDate></pubDate>
      </item>
      <item>
        <title></title>
        <description></description>
        <link></link>
        <pubDate></pubDate>
      </item>
  </channel>
</rss>

There could be more, but using an xml reader will get the items, then by looping through the items, you can do whatever you want to with them...
GlobaLevelProgrammerAuthor Commented:
does one need to subscribe?  How does one get access to the news agency data like rueters, new york times and all...?
JayFromPepCommented:
Most times, you just get the address and you can pull it.  Most RSS feeds are publicly consumable.  For example, the RSS feed for the new york times is....

www.nytimes.com/services/xml/rss

Rueters....

www.reuters.com/tools/rss
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

GlobaLevelProgrammerAuthor Commented:
would this go into an asp.net page..it needs to run twice a day..so may be this or vb.net on a task sceduler...

Dim strXmlSrc  As String = "http://www.mysite.com/rss/myrssfeed.xml"

    Dim myXmlDoc As XmlDocument = New XmlDocument()
    myXmlDoc.Load(strXmlSrc)

    Dim myStringBuilder As StringBuilder = New StringBuilder()
    Dim myStringWriter  As StringWriter  = New StringWriter(myStringBuilder)


----

how does that xml get returned.....? does it open in a browser? after the above code is ran?
JayFromPepCommented:
If I understand what you want to do, twice a day, you want your site to go out and collect this data and put it into your database.

You need to read this link....http://msdn.microsoft.com/en-us/magazine/cc163821.aspx

So you create a class that handles the xml file that will get returned.  the code above was just a starter kit.  What happens is the xml file is returned to the calling application (in this case, your website) and is read into memory, then processed.

I will post something in a few minutes that should give you a better jump start.  In the mean time, check out that post above.
GlobaLevelProgrammerAuthor Commented:
If I understand what you want to do, twice a day, you want your site to go out and collect this data and put it into your database.
---yes to the above...tricky part is capturing that XML as it comes in...
JayFromPepCommented:
Ok, here is the code....

Now a couple of things to note....

    1.  This does not check for the type of feed.  It assumes the items it is looking for are there in the right place.  If you need to check for the type of feed, you will need to use some sort of feed parsing library, of which there are a number of them on the net.

    2.  You still have to create the data access piece to add the items to the db, but it should work if you can get that done.

BTW, I took this code from Scott Guthrie's blog and modified it.  You can find his blog here....
http://weblogs.asp.net/scottgu/


Public Shared Sub ProcessRSS(ByVal rssURL As String, ByVal feed As String) As String
    'Create some holders and request/response stuff to connect to the feed
    Dim request As WebRequest = WebRequest.Create(rssURL)
    Dim response As WebResponse = request.GetResponse()
    Dim rssStream As Stream = response.GetResponseStream()
    Dim rssDoc As New XmlDocument()
    rssDoc.Load(rssStream)
    Dim rssItems As XmlNodeList = rssDoc.SelectNodes("rss/channel/item")

    'Create the holders for the items in the rss feed.  Note that this does not take into account the different
    'types of rss feeds, but rather assumes that each feed will contain certain information.
    Dim title As String = ""
    Dim link As String = ""
    Dim description as String = "" 
    Dim upperlimit As Integer = rssItems.Count

    If upperlimit > 0 Then     'If there is not at least one, then quit the process
        Dim i As Integer = 0
        While i < upperlimit
            Dim rssDetail As XmlNode     'Set up an empty node to deal with

            'Start selecting the items you want to add to your db.....
            rssDetail = rssItems.Item(i).SelectSingleNode("title")
                If rssDetail <> Nothing Then
                    title = rssDetail.InnerText
                Else
                    title = ""
                End If

            rssDetail = rssItems.Item(i).SelectSingleNode("link")
                If rssDetail <> Nothing Then
                    link = rssDetail.InnerText
                Else
                    link = ""
                End If
            rssDetail = rssItems.Item(i).SelectSingleNode("description")
                If rssDetail <> Nothing Then
                    description = rssDetail.InnerText
                Else
                    description = ""
                End If

            'Continue to add items that you want to put into the db

            'this is where you create the data access piece to add the stuff to your db.
            'you can use a hard coded query, stored procedure, etc.

            i+=1
        End While
    End If
End Sub

Open in new window

GlobaLevelProgrammerAuthor Commented:
Wow this is great...

This does not check for the type of feed.
>>I have the concept of RSS feeds...but im still new and not sure what you mean by this...

You still have to create the data access piece to add the items to the db, but it should work if you can get that done.

>>I can handle this piece...

this can be placed in a asp.net page...outside of task scheduler is there a way to automate with .net?
JayFromPepCommented:
Thanks!

The article I included above (http://msdn.microsoft.com/en-us/magazine/cc163821.aspx) talks about just that thing.  I have never done it before, but I would start there.

As to the types of RSS feeds, there is RSS 1.0 and ATOM.  The issue is how you iterate through the XML.  The problem is that many people do not use a standard approach, so it can be difficult sometimes.  There are 4 main sections to RSS.

The 4 main sections are

Channel Element - contains metadata that describe the channel itself, like what its about and who created it, etc.
Image Section - This is optional, but usually is used to include a link to a logo or some other pic
Items Section (Entry if the feed is ATOM) - This is the key part, and is the most important part of  the feed.
Text Section - Optional, usually html and it allows some response to the RSS feed.

Now, within the Items/Entry section

RSS
*********
title
Link
Description
Author
Category
Comments
Enclosure
GUID
PubDate
Source

So an RSS XML File would something like this...

<?xml version="1.0 encoding="UTF-8" ?>
<rss version="2.0">
  <channel>
    <title>How Cool Am I?</title>
    <link>http://www.yoursite.com</link>
    <description>The latest info on how to be cool</description>
    <image>http://www.yoursite.com/images/mypic.gif</image>
    <item>
      <title>It Aint Easy Being Cheesy</title>
      <link>http://www.yoursite.com/beingcheesy.aspx</link>
      <description>blah, blah, blah</description>
      <pubDate>Mon, 22 Aug 2010 12:34:43 GMT</pubdate>
    </item>
  <channel>
  <channel>
    add channel stuff here
  </channel>
etc.....

ATOM has some different names and constructs, but same rules apply.....

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GlobaLevelProgrammerAuthor Commented:
is this the namespace for RSS?

<%@ Import Namespace="System.Net.RSS"%>
JayFromPepCommented:
I dont think RSS has its own namespace in .net.

Not sure where you found that, but it probably came from a custom built class.
GlobaLevelProgrammerAuthor Commented:
I am getting that alot of these are not defined...


 Dim request As WebRequest = WebRequest.Create(rssURL)
        Dim response As WebResponse = request.GetResponse()
        Dim rssStream As Stream = response.GetResponseStream()
        Dim rssDoc As New XmlDocument()
JayFromPepCommented:
The following items do exist in the framework

   WebRequest
    Stream
    XmlDocument

You can find them in the following namespaces....

    System.Net.WebRequest
    System.IO.Stream
    System.Xml.XmlDocument

So to use the namespaces you would have an include in the code behind.
GlobaLevelProgrammerAuthor Commented:
I dont get it I still get an error, that says ...web request..xmlnode not defined...
<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@Import Namespace="System.Net.WebRequest"%>

<%@Import Namespace="System.IO.Stream"%>

<%@Import Namespace="System.Xml.XmlDocument"%>


<script runat="server">
Public Shared Sub ProcessRSS(ByVal rssURL As String, ByVal feed As String) As String
    'Create some holders and request/response stuff to connect to the feed
    Dim request As WebRequest = WebRequest.Create(rssURL)
    Dim response As WebResponse = request.GetResponse()
    Dim rssStream As Stream = response.GetResponseStream()
    Dim rssDoc As New XmlDocument()
    rssDoc.Load(rssStream)
    Dim rssItems As XmlNodeList = rssDoc.SelectNodes("rss/channel/item")

    'Create the holders for the items in the rss feed.  Note that this does not take into account the different
    'types of rss feeds, but rather assumes that each feed will contain certain information.
    Dim title As String = ""
    Dim link As String = ""
    Dim description as String = "" 
    Dim upperlimit As Integer = rssItems.Count

    If upperlimit > 0 Then     'If there is not at least one, then quit the process
        Dim i As Integer = 0
        While i < upperlimit
            Dim rssDetail As XmlNode     'Set up an empty node to deal with

            'Start selecting the items you want to add to your db.....
            rssDetail = rssItems.Item(i).SelectSingleNode("title")
                If rssDetail <> Nothing Then
                    title = rssDetail.InnerText
                Else
                    title = ""
                End If

            rssDetail = rssItems.Item(i).SelectSingleNode("link")
                If rssDetail <> Nothing Then
                    link = rssDetail.InnerText
                Else
                    link = ""
                End If
            rssDetail = rssItems.Item(i).SelectSingleNode("description")
                If rssDetail <> Nothing Then
                    description = rssDetail.InnerText
                Else
                    description = ""
                End If

            'Continue to add items that you want to put into the db

            'this is where you create the data access piece to add the stuff to your db.
            'you can use a hard coded query, stored procedure, etc.

            i+=1
        End While
    End If
End Sub

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
    </form>
</body>
</html>

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.