Application of xpath to parse xml document using vba

Question

Today i came across a blog where I found a demo in which it was shown how to parse items applying xpath from xml document using vba. it will be just awesome if it is possible to do the same from a website.

Here is how it is done from a locally saved file:

Sub XML_Parsing()
    Dim xml As Object, post As Object

    Set xml = CreateObject("MSXML2.DOMDocument")
    xml.async = False: xml.validateOnParse = False
    xml.Load (ThisWorkbook.Path & "\htdocs.txt")

     For Each post In xml.SelectNodes("//DistributionLists/List")
        x = x + 1: Cells(x, 1) = post.SelectNodes(".//Name")(0).Text
        Cells(x, 2) = post.SelectNodes(".//TO")(0).Text
        Cells(x, 3) = post.SelectNodes(".//CC")(0).Text
        Cells(x, 4) = post.SelectNodes(".//BCC")(0).Text
    Next post
End Sub

And the above code should be applied upon a text file named "htdocs.txt" saved in Desktop.

<?xml version="1.0" encoding="utf-8"?>
<DistributionLists>
    <List>
        <Name>Recon</Name>
        <TO>John;Bob;Rob;Chris</TO>
        <CC>Jane;Ashley</CC>
        <BCC>Brent</BCC>
    </List>
    <List>
        <Name>Safety Metrics</Name>
        <TO>Tom;Casper</TO>
        <CC>Ashley</CC>
        <BCC>John</BCC>
    </List>
    <List>
        <Name>Performance Report</Name>
        <TO>Huck;Ashley</TO>
        <CC>Tom;Andrew</CC>
        <BCC>John;Seema</BCC>
    </List>
</DistributionLists>

The extracted results:

Recon   John;Bob;Rob;Chris  Jane;Ashley Brent
Safety Metrics  Tom;Casper  Ashley  John
Performance Report  Huck;Ashley Tom;Andrew  John;Seema

Now, I've got two questions:

1. How to parse the same from a website as i did above, as in "example.com"? If it was "html element" then i could load like "html.body.innerHTML = http.responsetext" but in this case what should be the process?
2. If i do the above thing using EARLY BINDING: what should be the reference to add to the library?

SIM · Accepted Answer · 2017-09-30 15:42:23Z

1

Seems to have found the solution already. Here it is:

Sub XML_Parsing()
    Dim http As New XMLHTTP60
    Dim xmldoc As Object, post As Object

    With http
        .Open "GET", "http://wservice.viabicing.cat/v1/getstations.php?v=1", False
        .send
        Set xmldoc = .responseXML
        xmldoc.LoadXML .responseXML.xml
    End With

     For Each post In xmldoc.SelectNodes("//station")
        x = x + 1: Cells(x, 1) = post.SelectNodes(".//lat")(0).Text
        Cells(x, 2) = post.SelectNodes(".//long")(0).Text
    Next post
End Sub

Partial results:

$41.40  2.180042
41.39553    2.17706
41.393699   2.181137
41.39347    2.18149

answered Sep 30, 2017 at 15:42

SIM

22.5k6 gold badges45 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Application of xpath to parse xml document using vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related