XML Processing for function

MrHans · Jun 24, 2016

Hi all,

I'm trying to create a function that converts a zipcode with house number to an address.
I found a web service that does this, but I don't get the function to work.
I don't understand how to parse the XML result.

My current code (which returns nothing) is:

Code:

Function findAddress() As String

    Dim xmldoc As MSXML2.DOMDocument60
    Dim xmlNode As MSXML2.IXMLDOMNode
    Dim xmlNodeList As MSXML2.IXMLDOMNodeList
    Dim myNode As MSXML2.IXMLDOMNode

    'Assemble the query string
    Dim strQuery As String
    strQuery = "http://api.postcodes.nl/1.0/address/?"
    strQuery = strQuery & "apikey=key"
    strQuery = strQuery & "&nlzip6=1234AB"
    strQuery = strQuery & "&streetnumber=10"

    'define XML and HTTP components
    Dim xmlService As New MSXML2.XMLHTTP60
    xmlService.Open "GET", strQuery, False
    xmlService.Send

    Set xmldoc = New MSXML2.DOMDocument60
    xmldoc.async = False
    xmldoc.LoadXML (xmlService.responseText)
    Set xmlNodeList = xmldoc.getElementsByTagName("*")
    For Each xmlNode In xmlNodeList
        For Each myNode In xmlNode.ChildNodes
          If myNode.NodeType = NODE_TEXT Then
            Debug.Print xmlNode.nodeName & "=" & xmlNode.Text
          End If
        Next myNode
    Next xmlNode
    Set xmldoc = Nothing

End Function

Possible XML results are:
{"status":"error","errorcode":6,"errormessage":"invalid api-key"}

Or:
{"status":"ok","results":[{"nlzip6":"value","streetname":"value","city":"value","municipality":"value","province":"value","latitude":"value","longitude":"value","phoneareacode":"value"}]}

I would like to understand how to access these results.
I want to read the 'status' value:
- in case of error return the reason specified in 'errormessage'
- in case of ok, return the result, for example the streetname value.

Can anyone help me out with this?

jdraw · Jun 24, 2016

What is the name and url of the site you are going to? Are there instructions for use?
Do you have a valid key for the site?
It seems your output may be JSON??

MrHans · Jun 24, 2016

Hi,

The URL is visible in the strQuery variable.
The URL works and returns the lines mentioned below my code.
And yes, I have a valid key.

It's just the code of my function that is not working.
I'm not sure if it receives the data at all and if it does, how to approach it...

jdraw · Jun 24, 2016

The code you have provided to readers does not have the "real key". So any attempt to help will FAIL on an invalid key.
If you have a legit key, can you get the url/site to return to you a valid address?

Typically, sites that provide a service also have instructions or samples of how to Access the site.
Do you have instructions for using the site?

MrHans · Jun 24, 2016

Yes I understand that, the current URL will work, it will return the Error response. As mentioned, this response I also want to process, it's example result 1.

Yes, the site explains clearly how the webservice works. And it is indeed working fine when I enter the url in de browser.

The working Url (without valid keys obviously) is:
http://api.postcodes.nl/1.0/address/?apikey=key&nlzip6=1234AB&streetnumber=10

This will give a valid (error) response.

jdraw · Jun 24, 2016

Here is a sample of a site that provides a service via a web site. Here is a link to the page with instructions for How to use the site.

We need that sort of info from you in order to provide a focused response.

Here is the code I used to get a 10 day weather forecast for Airport YOW (Ottawa Canada) from the wunderground site. The code may help you with your "Issue".

I have attached a few records from the response.

Code:

'---------------------------------------------------------------------------------------
' Procedure : Wunderground
' Author    : Jack
' Date      : 11/5/2013
' Purpose   : Using Wunderground link, get a 10 day forecast or airport YOW (ottawa, ON, Canada)
'
' Related info at:
' see http://www.access-programmers.co.uk/forums/showthread.php?t=240796
' There was other info on UtterAccess, but uses excel...
' see http://www.utteraccess.com/forum/Pull-Data-Website-t1999204.html
'
'  I got my key May 11 2013  JED
'
'http://www.wunderground.com/weather/api/
'http://www.wunderground.com/weather/api/d/docs
'---------------------------------------------------------------------------------------
'
Sub Wunderground()

    Dim MyKey As String
10  MyKey =XXXXXXXXXX 'KEY is HIDDEN    ' I got this key May 11 2013  JED
    Dim Resp As New MSXML2.DOMDocument60    'DOMDocument
    Dim Req As New MSXML2.ServerXMLHTTP60  'XMLHTTP
    Dim Forecast As IXMLDOMNode
    Dim Weather As IXMLDOMNode
20  Req.Open "GET", "http://api.wunderground.com/api/" _
                    & MyKey _
                    & "/hourly10day/q/CA/Yow.xml", False
30  Req.send

40  While Req.Status <> 200
50      Debug.Print "waiting for response*** info"
60      DoEvents
70  Wend

80  Resp.loadXML Req.responseText
90  Debug.Print Resp.xml
100 For Each Forecast In Resp.getElementsByTagName("forecast")
110     Debug.Print Forecast.SelectNodes("FCTTIME")(0).SelectNodes("pretty")(0).text
        'Debug.Print Forecast.SelectNodes("FCTTIME")(0).SelectNodes("hour")(0).text
120     Debug.Print "Temp C:   " & Forecast.SelectNodes("temp")(0).SelectNodes("metric")(0).text
130     Debug.Print "Condition: " & Forecast.SelectNodes("condition")(0).text
140     Debug.Print
150 Next
End Sub

Partial extract from wunderground response

Code:

...2:00 PM EDT on July 02, 2016
Temp C:   25
Condition: Partly Cloudy

3:00 PM EDT on July 02, 2016
Temp C:   26
Condition: Partly Cloudy

4:00 PM EDT on July 02, 2016
Temp C:   26
Condition: Partly Cloudy

5:00 PM EDT on July 02, 2016
Temp C:   26
Condition: Clear
...

MrHans · Jun 24, 2016

Thank you, that does look interesting indeed.

I did already find one issue.
Apparently the default response is indeed JSON, they have an additional optional parameter that specifies that it should output XML instead...

I will try to fiddle some more with it

pbaldy · Jun 24, 2016

jdraw said:
It seems your output may be JSON??

Not my area of expertise, but I agree. XML would look very different. I just did a project for a guy and one part was passing addresses to a website and getting a response back, which he said was JSON. It looked like:

{"Price":"12.98","Miles":2.6}

I don't know if there's a better way to handle JSON, but I just parsed it out by getting rid of the brackets and quotes and using the Split() function.

pbaldy · Jun 24, 2016

Argg I got distracted and it appears you already know it's JSON. Sorry.

MrHans · Jun 24, 2016

No, thanks for your response, maybe handling it as JSON is much easier... The split function sounds a lot easier then all Node stuff...

jdraw · Jun 24, 2016

I'm not sure JSON or XML is the real issue. I've asked for the url with instructions to use the web service and haven't gotten a response. So I offered some code and instructions for getting info from a webservice via Access and vba. My thinking was the OP might see something in the code that would suggest a change in what he was currently using or an option for how to do something.

The OP's code sample does not show a valid key. That may be an issue/the issue.
I'd like to see the instructions at the site for using the webservice. We could then know what is required to make a request.

Perhaps, as with wunderground, a key is free --so we could try the service.

pbaldy · Jun 24, 2016

I may have misunderstood, but I got the impression that getting the response wasn't the problem, handling it was ("it is indeed working fine when I enter the url in de browser"). I guess the key would be to set a breakpoint or use this to see what xmlService.responseText contains:

http://www.baldyweb.com/ImmediateWindow.htm

Then you know what direction to go in.

MrHans · Jun 24, 2016

Thanks for the help guys.

You're method for selecting the required node helped a lot. I have a working solution now.
It currently only prints to the Direct window, but now I can approach the individual results and finetune the function according to my needs.

For the record, here is a copy of the function.

Code:

Function GetAddress(strZip As String, strHouseNr As String) As String

'Assemble the query string
Dim strQuery As String
strQuery = "http://api.postcodes.nl/1.0/address/?"
strQuery = strQuery & "apikey=your-free-api-key-goes-here"
strQuery = strQuery & "&nlzip6=" & strZip
strQuery = strQuery & "&streetnumber=" & strHouseNr
strQuery = strQuery & "&output=xml"

'define XML and HTTP components
Dim xmlResult As New MSXML2.DOMDocument60
Dim xmlService As New MSXML2.XMLHTTP60
Dim oNodes As MSXML2.IXMLDOMNodeList
Dim oNodes2 As MSXML2.IXMLDOMNodeList
Dim oNode As MSXML2.IXMLDOMNode
Dim oNode2 As MSXML2.IXMLDOMNode

'create HTTP request to query URL - make sure to have
'that last "False" there for synchronous operation
xmlService.Open "GET", strQuery, False
xmlService.send
xmlResult.LoadXML (xmlService.responseText)

Set oNodes = xmlResult.getElementsByTagName("status")

For Each oNode In oNodes
If oNode.nodeName = "status" And oNode.Text = "error" Then
Set oNodes2 = xmlResult.getElementsByTagName("response")
For Each oNode2 In oNodes2
Debug.Print StrConv(oNode2.selectNodes("status")(0).Text, vbProperCase) & " " & oNode2.selectNodes("errorcode")(0).Text & ": " & StrConv(oNode2.selectNodes("errormessage")(0).Text, vbProperCase)
Next oNode2

ElseIf oNode.nodeName = "status" And oNode.Text = "ok" Then
Set oNodes2 = xmlResult.getElementsByTagName("result")
For Each oNode2 In oNodes2
Debug.Print oNode2.selectNodes("streetname")(0).Text & " " & strHouseNr
Debug.Print strZip & " " & oNode2.selectNodes("city")(0).Text
Debug.Print oNode2.selectNodes("province")(0).Text
Next oNode2

End If
Next oNode

End Function

This prints eighter the error with code and description or the address that it found including house number, zipcode, city and province.
This is what I needed.

Thanks again for your help!

jdraw · Jun 24, 2016

For anyone following this thread, here is the URL I found to get to the instructions surrounding this web service --https://api.postcode.nl/documentation/rest-json-endpoint#!

If anyone pursues this and gets a functioning implementation, it would be a good addition to the samples/tutorials area.

jdraw · Sep 17, 2016

Update:

I contacted the supplier of the Dutch rest-json address service api. I asked for an account to assist and learn more. Here was the response.

Hi Jack,

Regarding your question: I was trying to help a database user to access your site xml/json.
(http://www.access-programmers.co.uk/forums/showthread.php?t=287571) I registered, but did not receive an api key/password to access the web service. My intent was to test some program code to get info from your web service. It was really for educational/technical info.
Please advise. ( I have used google translate to try to understand your site)

I do not see any accounts registered with your email address. This can be done on https://api.postcode.nl/ We do require a Dutch Chamber of Commerce (Kamer van Koophandel) number for registration. This is one of the measures we had to take to prevent abuse of our service.

Sincerely,

Henk Stolk

MrHans · Sep 17, 2016

In fact, the code posted above works with postcodes.nl web service, not postcode.nl
The first one allows for 100 free checks per day, the second one not...
Obviously you do need a api key, but it's free to request one...
The code posted works excellent, I have successfully implemented the functionality by adding the nodes to an collection, from the collection you can fill in (or just check) the address details.

jdraw · Sep 17, 2016

Great that you have it working.
I just tried signing up for a free account at postcodes.nl web service. I don't think it is available outside the .nl

Can not get past an incorrect phone number (I tried various formats).

Niet alle velden zijn (juist) ingevuld.

MrHans · Sep 18, 2016

It says you didn't fill all required fields, a valid Dutch zip code is '1101 BD' for example.
4 digits followed by 2 letters

MrHans · Sep 18, 2016

Oh and phone number should be:
+31622464358
(just a random example)

jdraw · Sep 18, 2016

MrHans,

Thanks for the additional info. I did manage to login and get a free account and an api key.
It says I have not added a domain?? What does that mean in your view?
I used Google translate to translate all Dutch instructions.
I will look at your sample and try to mock something up --just as a learning exercise.

XML Processing for function

Registered User

Super Moderator

Registered User

Super Moderator

Registered User

Super Moderator

Registered User

Wino Moderator

Wino Moderator

Registered User

Super Moderator

Wino Moderator

Registered User

Super Moderator

Super Moderator

Registered User

Super Moderator

Registered User

Registered User

Super Moderator

Similar threads

Users who are viewing this thread