UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Parsing Xml With VBA, Access 2016    
 
   
tedteng85
post Aug 3 2016, 06:22 PM
Post#1



Posts: 2
Joined: 3-August 16



I am new to Access and was playing around with an open API for a map app, I got a response in simple XML but unable to parse it back. Please help, I've been trying to figure it out for 2 nights and got no where frown.gif

API response:
CODE
<DirectionMatrixResponse>
<status>0</status>
<message>ok</message>
<info>
<copyright>
<text>@2016 Baidu - Data</text>
<imageUrl>http://api.map.baidu.com/images/copyright_logo.png</imageUrl>
</copyright>
</info>
<result>
<elements>
<distance>
<text>10KM</text>
<value>10014</value>
</distance>
<duration>
<text>18MIN</text>
<value>1101</value>
</duration>
</elements>
</result>
</DirectionMatrixResponse>


I just need to grab the distance: 10KM and duration: 18MIN
Didn't get very far with my code:
CODE
Dim XMLHttpReq As MSXML2.XMLHTTP
Dim HTMLDoc As HTMLDocument
Dim TDelements As IHTMLElementCollection
Dim TDelement As Object

Sub MapDistance()

    Set XMLHttpReq = New MSXML2.XMLHTTP
  
    URL =
    
    XMLHttpReq.Open "GET", URL, False
    XMLHttpReq.send
    
    If XMLHttpReq.ReadyState = 4 And XMLHttpReq.status = 200 Then
    
        Set HTMLDoc = New HTMLDocument
        HTMLDoc.body.innerHTML = XMLHttpReq.responseText
    
        Set TDelements = HTMLDoc.getElementsByTagName("text")
        
        
        For Each TDelement In TDelements
            Dim strAddressDistance As String
            strAddressDistance = TDelement.innerText
            MsgBox strAddressDistance
        Next
    
    End If
  
End Sub


Please lend a hand, thanks notworthy.gif
Go to the top of the page
 
ADezii
post Aug 3 2016, 07:16 PM
Post#2



Posts: 2,072
Joined: 4-February 07
From: USA, Florida, Delray Beach


If it is a simple *.xml File as you indicated you can actually use a low level IO Statement to: read each Line, look for the <distance> and <duration> XML Tags, then remove the <text> and </text> Tags from the Line immediately after.
  1. Code Definition (assuming File exists in C:\Test\ and is named Test.xml (C:\Test\Test.xml):
    CODE
    Dim strLine As String
    Dim blnFoundDistance As Boolean
    Dim blnFoundDuration As Boolean

    Open "C:\Test\Test.xml" For Input As #1

    Do While Not EOF(1)
      Line Input #1, strLine
        If strLine = "<distance>" Then
          blnFoundDistance = True
        ElseIf strLine = "<duration>" Then
          blnFoundDuration = True
        End If
        If blnFoundDistance And strLine <> "<distance>" Then
          Debug.Print Replace(Replace(strLine, "<text>", ""), "</text>", "")
            blnFoundDistance = False
        End If
        If blnFoundDuration And strLine <> "<duration>" Then
          Debug.Print Replace(Replace(strLine, "<text>", ""), "</text>", "")
            blnFoundDuration = False
        End If
    Loop

    Close #1
  2. Sample OUTPUT:
    CODE
    10KM
    18MIN
Go to the top of the page
 
cheekybuddha
post Aug 4 2016, 04:52 AM
Post#3


UtterAccess VIP
Posts: 10,342
Joined: 6-December 03
From: Telegraph Hill


See Jack's posts in this thread for an alternative method.

hth,

d
Go to the top of the page
 
tedteng85
post Aug 4 2016, 06:05 AM
Post#4



Posts: 2
Joined: 3-August 16



Thanks ADezii and cheekybuddha, I did managed to figure it out, its the same method as you suggested cheekybuddha. Thanks guys!

CODE
Sub MapDistance()
        
        Dim xDoc As Object
        Dim DistanceNode As Object
        Dim DurationNode As Object
        Set xDoc = CreateObject("MSXML2.DOMDocument")
        
        xDoc.async = False
        xDoc.validateOnParse = False
        
        xDoc.Load ("XML URL goes in here")
        
        Set DistanceNode = xDoc.selectNodes("//result/distance/text")
        Set DurationNode = xDoc.selectNodes("//result/duration/text")
              
        txtDistance = DistanceNode(0).Text
        txtTime = DurationNode(0).Text

        Set xDoc = Nothing    

End Sub
Go to the top of the page
 
cheekybuddha
post Aug 4 2016, 06:08 AM
Post#5


UtterAccess VIP
Posts: 10,342
Joined: 6-December 03
From: Telegraph Hill


yw.gif

Glad we could help!

Thanks for sharing your final code.

d
Go to the top of the page
 
tdragger
post May 9 2018, 03:20 PM
Post#6



Posts: 2
Joined: 9-May 18



I am trying to work with the same DistanceMatrixResponse xml file but apparently a few element names have changed. Here is the xml:

CODE
<?xml version="1.0" encoding="UTF-8"?>
<DistanceMatrixResponse>
<status>OK</status>
<origin_address>318-328 Gap Rd, Ronks, PA 17572, USA</origin_address>
<destination_address>3000 Battleship Pkwy, Mobile, AL 36602, USA</destination_address>
<row>
  <element>
   <status>OK</status>
   <duration>
    <value>57881</value>
    <text>16 hours 5 mins</text>
   </duration>
   <distance>
    <value>1764612</value>
    <text>1,096 mi</text>
   </distance>
  </element>
</row>
</DistanceMatrixResponse>


The difference is that it is written as an Access 2016 function instead of a sub. I have the XML stored in a Long Text field in a table and am passing the XML in to the function. I am trying to pull the Distance Value from the XML.

CODE
Public Function getDist(gxml As String)
        
        Dim xDoc As Object
        Dim DistanceNode As Object
        'Dim DurationNode As Object
        Dim txtDistance As String
        
        Set xDoc = CreateObject("MSXML2.DOMDocument")
        Call xDoc.SetProperty("SelectionLanguage", "XPath")
        
        xDoc.async = False
        xDoc.validateOnParse = False
        
        xDoc.Load (gxml)
        
        Set DistanceNode = xDoc.selectNodes("//row/distance/value")
        Debug.Print DistanceNode.Text
        
        txtDistance = DistanceNode(0).Text
        getDist = txtDistance

        Set DistanceNode = Nothing
        Set xDoc = Nothing
        

End Function


I am getting a "Run-time error '438': Object doesn't support this property or method" error on the Debug.Print line. I was getting the same error on the txtDistance = line before adding the Debug line.

If I remove the Debug line, the error changes to: "Run-time error '91': Object variable or With block variable not set".

What am I doing wrong?
This post has been edited by tdragger: May 9 2018, 03:22 PM
Go to the top of the page
 
cheekybuddha
post May 9 2018, 08:01 PM
Post#7


UtterAccess VIP
Posts: 10,342
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

What is the value of gxml?

How are you calling your getDist() function?

d

--------------------


Regards,

David Marten
Go to the top of the page
 
AlbertKallal
post May 9 2018, 10:44 PM
Post#8


UtterAccess VIP
Posts: 2,670
Joined: 12-April 07
From: Edmonton, Alberta Canada


This is one case in which I would flip to early binding. That way you get inteli-sense during codeing.

Then flip back to late binding as you have when you deploy

You have a number of errors.

You want to use loadxml(), not load (load() assumes a file name, not a string).

And you don’t want to select “nodes”, but a single node.

And you need a full qualified x-path name from the VERY top of the xml tree.

Try this:

CODE
Public Function getDist(gxml As String) As String
        
   Dim xDoc           As Object
   Dim DistanceNode   As Object
  
   Set xDoc = CreateObject("MSXML2.DOMDocument")
    
   xDoc.async = False
   If xDoc.LoadXML(gxml) = False Then
      MsgBox "xml load fail"
      Exit Function
   End If
  
   Set DistanceNode = xDoc.SelectSingleNode("//DistanceMatrixResponse/row/element/distance/value")
   Debug.Print DistanceNode.Text
   getDist = DistanceNode.Text
  
   Set DistanceNode = Nothing
   Set xDoc = Nothing

End Function


Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
tdragger
post May 10 2018, 06:13 AM
Post#9



Posts: 2
Joined: 9-May 18



Thank you very, very much. This is my first attempt at VBA and XML. I really appreciate the assistance.

To answer the other question posted, this function is being called via an update query. I'm not making a commercial grade product so not trying for super speed or efficiency.
Go to the top of the page
 
cheekybuddha
post May 10 2018, 07:07 AM
Post#10


UtterAccess VIP
Posts: 10,342
Joined: 6-December 03
From: Telegraph Hill


Is the parameter gxml variable filled with the actual xml, or a URL?

--------------------


Regards,

David Marten
Go to the top of the page
 
AlbertKallal
post May 10 2018, 12:27 PM
Post#11


UtterAccess VIP
Posts: 2,670
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
Is the parameter gxml variable filled with the actual xml, or a URL?


On post #6, in this thread, the poster notes:

QUOTE
I have the XML stored in a Long Text field in a table and am passing the XML in to the function. I am trying to pull the Distance Value from the XML.


So the xml is in a long text field, and “am passing the xml to the function”

So the “gxml” value in question is of course the xml, and not a path name to a file.

So you "are" zeroing in on that the original code assumed a "path to file" (xml.load), but now working with raw xml text string, then we had to use xml.LoadXML().


Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com

Go to the top of the page
 
cheekybuddha
post May 10 2018, 09:18 PM
Post#12


UtterAccess VIP
Posts: 10,342
Joined: 6-December 03
From: Telegraph Hill


Thanks, Albert - I missed that. thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
AlbertKallal
post May 10 2018, 10:15 PM
Post#13


UtterAccess VIP
Posts: 2,670
Joined: 12-April 07
From: Edmonton, Alberta Canada


Actually, David, I kind of thought you were right on the money, because if this "was" still a file, the xDoc.Load(path to file) was the big issue!

So xDoc.Loadxml("xml string") was the required fix. So even if you "missed" the posters part, your question and instincts were 100% on the money.

And really - given the incredible experience here? Heck we all just trying to pitch in and share our knowledge, and also learn at the same time.

I am long past those days in terms of questioning my experience - but that don't mean I miss things! The brains that UA has sitting around on
a bad day is still remarkable!

All in all I think this post resulted in a rather nice + short routine showing how to pull nodes out of XML - readers will benefit from this thread for years to come!

Kind of of the whole point of UA!

Where we go one, we go all! (that is a inscription on a Bell on JFK's boat - it really is a statement of unity and sharing ideas here!).

So even in "missing" something, you still zeroed in on a big part of the issue (file vs raw xml string).

All in all, the experience of people here is what makes UA such a great place to hang out! I been around the block in terms of on-line communities.

UA is the gold standard here! - even WHEN we miss something we still really fantastic! (at least I like to think so and that's the story I am sticking with!!! )


uarulez2.gif

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd September 2018 - 03:43 PM