MartyConnelly
martyconnelly at shaw.ca
Tue Dec 23 14:00:17 CST 2003
You want the attribute value CURRENCY not the element PRICE so
//RealData/ITEM/PRICE[@CURRENCY="EUR"]
There are some testing programs that might help with this like XMLSPY 30
day trial
or this free one, a little difficult for there is no help and blows up
if xml invalid
Also XML file must be saved as UTF-8 or Unicode not text. Text blows
away the BOM marker.
http://www.develop.com/uk/technology/resourcedetail.aspx?id=a5b18ce9-0972-413d-ae10-795f9aebcebf
Hmm I just noticed another possible XPath problem because of "," in currency
sum(//RealData/ITEM/PRICE) gives you NaN
but
count(//RealData/ITEM/PRICE) is okay
Also see this for error checking
Client side validation of xml. Just translate the vbs to vba
http://www.15seconds.com/issue/020606.htm
Erwin Craps - IT Helps wrote:
>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
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada