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