MartyConnelly
martyconnelly at shaw.ca
Mon Dec 22 13:46:11 CST 2003
Here is how I grab an xml RSS news feed file from Microsoft and stick
xml elements into a table
It is a little lite on error handling and needs reference to XML 4.0, I
run from Access97.
If your files are large above a couple of megabytes, you may have to use
the SAX parser.
'LoadXMLJP "http://msdn.microsoft.com/vbasic/rss.xml","vbasic"
'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"
Public Sub LoadXMLJP(ByRef AdviserXML As String, strFeed As String)
' 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 'assumes previous check for xml
validity
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 quote " 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
Erwin Craps - IT Helps wrote:
>Euh, I don't think so...
>I explain more detailed.
>
>My supplier allows me to constuct a url which returns a XML file in a
>browser.
>
>For example
>http://www.websitedummy.com/xml/xmlonl.asp?custid=MyCustID&prodid=783662
>(Real Example but I replaced the website and cust id for obvious
>reasons)
>
>As a (real) result I get an XML file looking like this in my browser.
> <?xml version="1.0" ?>
>- <RealData>
> <MSG ID="0">ok</MSG>
>- <ITEM>
> <ID>783662</ID>
> <DESC>LCD 1501-BK 15" TFT 0.297mm 60kHz 1024 x 768 TCO99 Black</DESC>
> <PRICE CURRENCY="EUR">266,71</PRICE>
> <QTY>0</QTY>
> <DELDATE>07012004</DELDATE>
> </ITEM>
> </RealData>
>
>
>I want to know if I can do this straight away from Access 2K2 or 2K3,
>knowing that Access suports XML.
>Isn't this what XML is all about?
>Connecting anything to anything???
>
>Or am I to positive thinking?
>
>Well, I could resolve this with my http Active-X control I have, but I
>always prefere to use native Access functionality above purchased
>controls.
>
>The reason why I wanna do this is to startup my E-commerce website next
>year.
>I want to be able to update my article database automaticaly with stock
>and purchase information, etc...
>This database is used for my internal application and both E-commerce
>website.
>
>
>
>Erwin
>
>
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
>Foust
>Sent: Monday, December 22, 2003 4:54 PM
>To: Access Developers discussion and problem solving
>Subject: RE: [AccessD] XML Http request from Access 2K2 or 2K3
>
>You can open a recordset on an xml file, if that's what you're looking
>for.
>
>Charlotte Foust
>
>-----Original Message-----
>From: Erwin Craps - IT Helps [mailto:Erwin.Craps at ithelps.be]
>Sent: Monday, December 22, 2003 1:47 AM
>To: Access Developers discussion and problem solving
>Subject: [AccessD] XML Http request from Access 2K2 or 2K3
>
>
>I never looked in to the XML support for Access.
>Apparantly there is XML support, but the only thing I find is a
>import/export filter.
>
>So I'm wundering if I can do a specific http xml request straight from a
>form, in an easy way?
>
>
>
>Erwin Craps
>
>Zaakvoerder
>
>www.ithelps.be/jonathan
>
>
>
>This E-mail is confidential, may be legally privileged, and is for the
>intended recipient only. Access, disclosure, copying, distribution, or
>reliance on any of it by anyone else is prohibited and may be a criminal
>offence. Please delete if obtained in error and E-mail confirmation to
>the sender.
>
>IT Helps - I.T. Help Center *** Box Office Belgium & Luxembourg
>
>www.ithelps.be <http://www.ithelps.be/> * www.boxoffice.be
><http://www.boxoffice.be/> * www.stadleuven.be
><http://www.stadleuven.be/>
>
>IT Helps bvba* ** Mercatorpad 3 ** 3000 Leuven
>
>IT Helps * Phone: +32 16 296 404 * Fax: +32 16 296 405 E-mail:
>Info at ithelps.be
>
>Box Office ** Fax: +32 16 296 406 ** Box Office E-mail:
>Staff at boxoffice.be <mailto:figures at boxoffice.be>
>
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada