[AccessD] XML Http request from Access 2K2 or 2K3

Erwin Craps - IT Helps Erwin.Craps at ithelps.be
Tue Dec 23 02:45:44 CST 2003


Ok I'm getting close now.

Only two, probably small issues for you.
This is the XML result I get

  <?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>



Q1: How do I retrieve the MSG ID value (0 in this case) and text (ok in
this case)?

Q2: How do I retrieve the CURRENCY value (EUR in this case).
.responseXML.SelectSingleNode("//ITEM/PRICE").Text 
Results in the price but 
.responseXML.SelectSingleNode("//ITEM/PRICE CURRENCY").Text
Or 
.responseXML.SelectSingleNode("//ITEM/CURRENCY").Text
Don't work.

Thx Erwin

 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
Sent: Tuesday, December 23, 2003 12:25 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] XML Http request from Access 2K2 or 2K3

What I was doing was reading the xml file directly into the xmldom
parser, then selecting the nodes from the dom that I was interested in
via an xpath statement

like "//item/*". There are a couple of ways of doing this; one other is
to use an xslt transform.

You might want to try Severxmlhttp, it is newer, uses a different stack
and might be faster.
Dim xmlServerHttp As New Msxml2.ServerXMLHTTP40  Dim oNodeList As
IXMLDOMNodeList  Dim onewsElement As IXMLDOMElement xmlServerHttp.open
"GET", "http://localhost/sample.xml", False xmlServerHttp.send ' also
need to check xmlserverhttp.parseerror MsgBox xmlServerHttp.Status,
xmlServerHttp.Statustext '200 is OK MsgBox xmlServerHttp.responseXML.xml
onewsElement = srvXmlHttp.responseXML.selectSingleNode("//news/story1")



Erwin Craps - IT Helps wrote:

>Marty
>
>Your code is not exactly the solution for the problem I was confronted 
>with, but you pointed me in the right direction.
>Is your code a, so called, XML parser?
>To convert the raw data into useable fields/values?
>
>Below code is what I created thanks to you (slow but it works).
>
>Dim objSrvHTTP As ServerXMLHTTP
>Set objSrvHTTP = New ServerXMLHTTP50
>With objSrvHTTP
>	.Open "GET",
>"http://www.dummywebsite.com/xml/xmlonl.asp?custid=DummyCustID&prodid=7
>8
>3662", False
>    	.Send
>     	Debug.Print .responseXML.XML
>End With
>Set objSrvHTTP = Nothing
>
>Great stuff XML....
>Pretty easyer than analysing a returned HTML page (been there, done
>that)
>
>Erwin
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Erwin Craps 
>- IT Helps
>Sent: Monday, December 22, 2003 9:07 PM
>To: Access Developers discussion and problem solving
>Subject: RE: [AccessD] XML Http request from Access 2K2 or 2K3
>
>OK thx, I'll try to implement this. 
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
>MartyConnelly
>Sent: Monday, December 22, 2003 8:46 PM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] XML Http request from Access 2K2 or 2K3
>
>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=7836
>>6
>>2 (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
>
>
>
>_______________________________________________
>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



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list