[dba-VB] RSS Feed database

John W. Colby jwcolby at colbyconsulting.com
Wed Aug 24 12:22:47 CDT 2005


My purpose isn't to see a web page but rather to actually parse the xml file
and grab the data into a table.  Specifically I just got an account with
NetFlix.  NetFilx has an RSS feed to show you what is in your queue, but
more importantly to show what you have rented.  I want to capture a database
of what I have rented so that I don't rent it again (or know that I am doing
so), but also to get a table with the url back to the movie description.

Thus I really do want to open the feed, read it in, and grab the specific
fields and write them into a table.

In fact I have managed to get around the SQL Server issue by using the class
I posted yesterday for opening a reader (in Access).  Access it trivial to
get to and get the data into and out of so I just rewrote the first part to
use my little class.

As my momma used to say...

"There's more ways to kill a cat than choking him to death on butter"

Luckily the part that opens the RSS feed just works.  I am now working on
replacing the write of the data back out to SQL server with code to use my
class - modified a bit.

I wish I knew more about SQL Server but I have to do what works and Access
works ATM.

John W. Colby

Contribute your unused CPU cycles to a good cause:

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
Sent: Wednesday, August 24, 2005 12:47 PM
To: dba-vb at databaseadvisors.com
Subject: Re: [dba-VB] RSS Feed database

I have a rough mdb that reads various MS programming (VB.Net C# Net) RSS 
feeds and placed in tables, just did it as a proof of concept. I just didn't
feel like running it daily to keep it uptodate. never 
found a way tokeep it uptodate or if Ms had an archive.
I did this a year ago maybe there is something now.

Never fully fleshed it out. If you want a copy, I'll have to check it 
still works.

I have also done things like this that can be done better now through 
RSS Viewers.
' grabs BBC Tech News feed transforms RSS xml via XSLT in XSL file and 
displays in IE window
'But it doesn't require much code.
If you do use this,snaffle the xsl file off my website and use locally 
it will be quicker.
Save the XSL in notepad with UTF-8 format.

Sub BBCRSSFeed()
Dim srcTree As Object
Dim xsltTree As Object
Dim strHTML As String
 Set srcTree = CreateObject("Msxml.DOMDocument")
        srcTree.async = False
 'srcTree.Load ("http://services.devx.com/outgoing/recentTipsFeed.xml")
 Set xsltTree = CreateObject("Msxml.DOMDOCUMENT")
        xsltTree.async = False
        'this xsl transform file can also be loaded via a local disk file
        xsltTree.Load ("http://www5.brinkster.com/mconnelly/newsfeed.xsl")

        strHTML = srcTree.transformNode(xsltTree)
        Debug.Print strHTML
        ' Display transformed xml rss news feed in html via IE 6.0 window
        testIE (strHTML)
End Sub
Sub testIE(strpassHTML As String)

Dim objExplorer As Object
Dim objDocument As Object
Dim strComputer As String

Dim strReturn As String

Set objExplorer = CreateObject("InternetExplorer.Application")

objExplorer.Navigate "about:blank"
objExplorer.Toolbar = 0
objExplorer.StatusBar = 0
objExplorer.Width = 800
objExplorer.Height = 570
objExplorer.Left = 0
objExplorer.Top = 0
objExplorer.Visible = 1
'dont't need to navigate to file or http site like below
'just drop html string created from xslt into the IE document object
'objExplorer.Navigate "file://c:\temp\test.html" 'objExplorer.Navigate
"http://www.Viescas.com/" 'objExplorer.Navigate ""
'objExplorer.Navigate "http://checkip.dyndns.org/" Do While
(objExplorer.Busy) Loop Dim webtx As String Dim strHTML As String Set
objDocument = objExplorer.Document objDocument.Open objDocument.Writeln
"<html><head><title>My BBC Technology RSS 
'A little bit of CSS code to make it funky
objDocument.Writeln "<body bgcolor='red';" & _
    " {   background-attachment: fixed; background-position: 97% bottom; 
" & _
      "  background-image= 'url(logoBackGround.gif)';background-repeat: 
no-repeat;" & _
       " padding: 5px;font-family='Georgia';font-size: 80%;} >"
objDocument.Writeln "<table width='100%'>"
objDocument.Writeln "<tr>"
objDocument.Writeln "<td width='20%'><b>BBC Feed</b></td>"
objDocument.Writeln "</tr>" objDocument.Writeln "</table>"
objDocument.Writeln strpassHTML objDocument.Writeln "</body></html>"
'MsgBox "finished"
Set objExplorer = Nothing
Set objDocument = Nothing

End Sub

John W. Colby wrote:

>Do I have no takers helping me get this thing working?  The end result 
>will be a system for logging an RSS feed into data tables for analysis 
>or use. Could be niiiice.  ;-)
>I have made good progress, I have a database set up in SQL Server 
>specifically for this project.  I have the tables built per the script 
>included in the C# zip.  There are two records in the WebNewsSource 
>table. I am logging in to the database.  The issue at this point is 
>that the SP that is supposed to return the rows in the database 
>apparently doesn't.
>The SP looks like:
>-- Stored procedure that will select an existing row from the table
>-- based on the Primary Key.
>-- Gets: @ID int
>CREATE PROCEDURE [dbo].[upWebNewsSource_SelectOne]
>	@ID int
>-- SELECT an existing row from the table.
>	[ID],
>	[Source],
>	[RSSSelect],
>	[RSSTitle],
>	[RSSLink],
>	[RSSDescription]
>FROM [dbo].[WebNewsSource]
>	[ID] = @ID
>How do I go about just running the SP passing in an ID and viewing the 
>John W. Colby
>Contribute your unused CPU cycles to a good cause: 
>dba-VB mailing list
>dba-VB at databaseadvisors.com 

Marty Connelly
Victoria, B.C.

dba-VB mailing list
dba-VB at databaseadvisors.com

More information about the dba-VB mailing list