[dba-VB] RSS Feed database

MartyConnelly martyconnelly at shaw.ca
Wed Aug 24 14:34:26 CDT 2005


Well you can do with a short subroutine to put rss xml in table , a lot 
of debug statement
just to keep track of what I was doing. Uses XPath statement to grab 
appropriate nodes from DOM.

'LoadXML "http://msdn.microsoft.com/rss.xml","Just Published"
'LoadXML "http://msdn.microsoft.com/webservices/rss.xml","Web Services"
'LoadXML "http://msdn.microsoft.com/vstudio/rss.xml","Visual Studio"
'LoadXML "http://msdn.microsoft.com/netframework/rss.xml","Net Framework"
'LoadXML "http://msdn.microsoft.com/vcsharp/rss.xml","VC Sharp"
'LoadXML "http://msdn.microsoft.com/visualc/rss.xml", "VisualC"
'LoadXML "http://msdn.microsoft.com/security/rss.xml", "Security"
'Loadxml "http://www.kbalertz.com/rss/acc.xml","accesskbalert"

Public Sub LoadXML(ByRef AdviserXML As String, strFeed As String)
'second parameter just provides a feed name field in general table
   ' On Error GoTo ErrorHandler

    Const ACTION_NAME As String = "LoadXML"

    Dim oDOMDocument As MSXML2.DOMDocument40
    Dim oNodeList As IXMLDOMNodeList
    Dim oAdviserDetailsNode As IXMLDOMNode
    Dim oLowestLevelNode As IXMLDOMElement
    Dim objXMLDOMNamedNodeMap As IXMLDOMNamedNodeMap

    Dim sTempValue As String
    Dim msFPTAdviserID  As Object
    'temporary database
Dim MyDb As Database
Dim MyRs As Recordset
Dim irec As Long
Dim strTitleLink As String
   
    Set oDOMDocument = New MSXML2.DOMDocument40

    oDOMDocument.async = False
    oDOMDocument.validateOnParse = False 'very trusting
    oDOMDocument.resolveExternals = False
    oDOMDocument.preserveWhiteSpace = True

    If Not oDOMDocument.Load(AdviserXML) Then
        MsgBox Err.number & Err.Description
       
        'Call Err.Raise(ERR_UNABLE_TO_LOAD_ADVISER_XML, , 
ERRDESC_UNABLE_TO_LOAD_ADVISER_XML)
        Exit Sub
    End If
    Debug.Print "grabbed box"
    Set oAdviserDetailsNode = oDOMDocument.documentElement

    Set objXMLDOMNamedNodeMap = oAdviserDetailsNode.Attributes

    'msFPTAdviserID = objXMLDOMNamedNodeMap.getNamedItem("title").nodeValue
    Debug.Print "Set"
    Debug.Print "msFPT"
    Set oNodeList = oAdviserDetailsNode.selectNodes("//item/*")
Set MyDb = CurrentDb
Set MyRs = MyDb.OpenRecordset("RSSFeed")
irec = 0
 MyRs.AddNew
    For Each oLowestLevelNode In oNodeList
   
        sTempValue = oLowestLevelNode.Text
     ' Debug.Print oLowestLevelNode.nodeName & "-" & vbCrLf & 
oLowestLevelNode.Text
 
        Select Case oLowestLevelNode.nodeName
            Case "title"
              
              Debug.Print sTempValue
              MyRs!Title = sTempValue
              strTitleLink = sTempValue
            Case "PubDate"
              Debug.Print sTempValue
              MyRs!PubDate = sTempValue
            Case "description"
               Debug.Print sTempValue
               MyRs!fdescription = sTempValue
            Case "link"
              Debug.Print sTempValue
              'MyRs!link = sTempValue
              'need # format for clickable link
              MyRs!link = sTempValue & "#" & sTempValue & "#"
        End Select
        irec = irec + 1
        If irec = 4 Then
        'check if duplicate also check if " in string
        Debug.Print DLookup("ID", "RSSFeed", "Title=" & Chr(34) & 
strTitleLink & Chr(34))
        If IsNull(DLookup("Title", "RSSFeed", "Title=" & Chr(34) & 
strTitleLink & Chr(34))) Then
             MyRs!feed = strFeed
             MyRs.Update
            
        End If
        MyRs.AddNew
        irec = 0
        End If
       
    Next
    Set MyRs = Nothing
    Set MyDb = Nothing
    Set oDOMDocument = Nothing
    Set oAdviserDetailsNode = Nothing
    Set objXMLDOMNamedNodeMap = Nothing
    Exit Sub

ErrorHandler:

 '   Call NewError.Raise(Err.Number, Err.Source, Err.Description,
'MODULE_NAME, ACTION_NAME, Erl)

End Sub

John W. Colby wrote:

>Marty,
>
>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
>www.ColbyConsulting.com 
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>-----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://news.bbc.co.uk/rss/newsonline_uk_edition/technology/rss091.xml")
> '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 "192.168.0.1/st_devic.html"
>'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 
>Feed</title></head>"
>'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>"
>'objDocument.Write()
>objDocument.Close
>'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
>>'WebNewsSource'
>>-- based on the Primary Key.
>>-- Gets: @ID int
>>---------------------------------------------------------------------------
>>    
>>
>-
>  
>
>>-----
>>CREATE PROCEDURE [dbo].[upWebNewsSource_SelectOne]
>>	@ID int
>>AS
>>SET NOCOUNT ON
>>-- SELECT an existing row from the table.
>>SELECT
>>	[ID],
>>	[Source],
>>	[RSSURL],
>>	[RSSSelect],
>>	[RSSTitle],
>>	[RSSLink],
>>	[RSSDescription]
>>    
>>
>>FROM [dbo].[WebNewsSource]
>  
>
>>WHERE
>>	[ID] = @ID
>>
>>
>>GO
>>
>>How do I go about just running the SP passing in an ID and viewing the 
>>results?
>>
>>John W. Colby
>>www.ColbyConsulting.com
>>
>>Contribute your unused CPU cycles to a good cause: 
>>http://folding.stanford.edu/
>>
>>
>>_______________________________________________
>>dba-VB mailing list
>>dba-VB at databaseadvisors.com 
>>http://databaseadvisors.com/mailman/listinfo/dba-vb
>>http://www.databaseadvisors.com
>>
>>
>>
>> 
>>
>>    
>>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada





More information about the dba-VB mailing list