Extract data from XML response in XMLHTTP VBA

Question

I am trying to get the response of POST method and I already got a point using the code

Sub Test()
    Dim http As New XMLHTTP60, html As New HTMLDocument, ws As Worksheet, myUrl As String, postData As String
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    myUrl = "http://idahoparcels.us:8080/cgi-bin/mapserv.exe?MAP=%2Fms4w%2Fapps%2Fdt%2F.%2Fdemo%2Fparcels%2Felmore%2Fparcels.map&typename=ms%3Aparcels"
    postData = CreateObject("Scripting.FileSystemObject").OpenTextFile(ThisWorkbook.Path & "\FormData.txt").ReadAll
    With http
        .OPEN "POST", myUrl, False
        .send postData
        html.body.innerHTML = .responseText
        
        'Stuck Here
        
    End With
End Sub

** The contents in the FormData.txt is like that

<GetFeature xmlns="http://www.opengis.net/wfs" service="WFS" version="1.1.0"
 outputFormat="text/xml; subtype=gml/2.1.2" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xsi:schemaLocation="http://www.opengis.net/wfs 
   http://schemas.opengis.net/wfs/1.1.0/wfs.xsd">
       <Query typeName="ms:parcels" srsName="EPSG:4326">
       <Filter xmlns="http://www.opengis.net/ogc">
       <PropertyIsLike wildCard="%" singleChar="_" escapeChar="\"
           matchCase="false">
          <PropertyName>PARCEL_ID</PropertyName>
          <Literal>%RP01N07E210010%</Literal>
       </PropertyIsLike>
 </Filter>
 </Query>
 </GetFeature>

The output or response is like XML .. but I have no great idea of how to deal with XML as response Can you guide me with one example only so as to learn about that? For example: I need "ms:ownership" which should be "USDA FOREST SERVICE"

Lots of examples here - https://stackoverflow.com/questions/11305/how-to-parse-xml-using-vba — Tim Williams, Jun 25 '20 at 06:54
Thanks a lot. I can't start with a clue. Can you give me example of the existing XML? — YasserKhalil, Jun 25 '20 at 07:22
I tried something like that `Set oSeqNodes = oXml.SelectNodes("//FeatureCollection/featureMember")` but I don't think this is right. — YasserKhalil, Jun 25 '20 at 08:19
If you're receiving XML then you don't load it into an HTML document - the answers at the link I posted show how it is done. If you've tried one of those then maybe update your question with the code and a description of what happened when you ran it. — Tim Williams, Jun 25 '20 at 15:39
I already stored the `.responseText' to be used as XML document and not as HTML document but I didn't know how to select the nodes. — YasserKhalil, Jun 25 '20 at 15:47
If you need to do this regularly then it's worth spending some time reviewing xpath questions here on SO https://www.google.com/search?q=xpath+vba+site:stackoverflow.com or a more general overview like https://analystcave.com/vba-xml-working-xml-files/ — Tim Williams, Jun 25 '20 at 15:58
See this: https://stackoverflow.com/a/58566523/6241235 Set the namespace with .setProperty "SelectionNamespaces", "xmlns:ms='http://mapserver.gis.umn.edu/mapserver'" then refer to that //ms — QHarr, Jun 25 '20 at 16:43

score 2 · Accepted Answer · answered Jun 25 '20 at 16:33

Here's an example:


    Sub Test()
        
        Dim http As New XMLHTTP60, ws As Worksheet, myUrl As String, postData As String
        Dim doc As MSXML2.DOMDocument60, xml As String, els, el
        
        Set ws = ThisWorkbook.Worksheets("Sheet1")
        myUrl = "http://idahoparcels.us:8080/cgi-bin/mapserv.exe?MAP=%2Fms4w%2Fapps%2Fdt%2F" & _
                ".%2Fdemo%2Fparcels%2Felmore%2Fparcels.map&typename=ms%3Aparcels"
        
        postData = ws.Range("A1").Value 'loading from cell for testing...
        
        With http
            .Open "POST", myUrl, False
            .send postData
            xml = .responseText
            ws.Range("B1").Value = xml 'log response for review
        End With
        
        Set doc = New MSXML2.DOMDocument60
        
        'You must add any namespace(s) for any desired elements
        'Watch the formatting...
        doc.SetProperty "SelectionNamespaces", _
            "xmlns:ms='http://mapserver.gis.umn.edu/mapserver' " & _
            "xmlns:wfs='http://www.opengis.net/wfs' " & _
            "xmlns:gml='http://www.opengis.net/gml' " & _
            "xmlns:ogc='http://www.opengis.net/ogc'"
        
        doc.LoadXML xml
        
        Set els = doc.SelectNodes("//ms:OWNERSHIP")
        For Each el In els
            Debug.Print el.xml
            Debug.Print el.Text
        Next el
    
    End Sub

+ for namespaces forgotten so often. - FYI Parsing a xml structure at any node hierarchy via a recursive function c.f. [Obtain Node/attribute names from xml](https://stackoverflow.com/questions/51887820/obtain-attribute-names-from-xml-using-vba/51919182#51919182); [xml parse](https://stackoverflow.com/questions/53558110/xml-parse-vba-excel-function-trip-msxml2-domdocument/53559474#53559474) — T.M., Jun 25 '20 at 18:17
@Tim Williams I tried this `Set els = doc.SelectNodes("//ms:parcels[starts-with('ms:LG_LINE_')]")` to select all the nodes that starts with `ms:LG_LINE_`. How can I achieve that as mine throws an error? — YasserKhalil, Jun 26 '20 at 07:47
I could figure this point out by using `Set els = doc.SelectNodes("//*[contains(name(),'ms:LG_LINE_')]")` — YasserKhalil, Jun 26 '20 at 08:28

Extract data from XML response in XMLHTTP VBA

1 Answers1