CODEWITHSUNDEEP
xmlvbams-access-2010
JVDreamWorker
JVDreamWorker

Reputation: 80

MS Access VB XML Parsing issue

I am trying to create a form in MS Access that allows a user to send an address to USPS for verification and get the proper address. I have most of it working but being new to XML and parsing I am using code found elsewhere and trying to get it to work. I have been able to send the data and receive back the response with the XML. However I can't figure out how to parse out the nodes. Example I will have a form where a person puts address 1, 2, city, state, zip, etc... I want the result to either come back into those form fields or at least in a text box next to the fields. I am able to as a test display the results I get back from USPS but it obviously includes the XML code. So now I have to just parse out the data from the XML and display that. BUT I can't get just the data I am looking for. As an example, let say I want address 1, city, zip5 from the code. Here is what I Have that works as far as sending data and receiving the fixed address. But again, I can't seem to get the data out.

I suspect setting cstrXpath is not correct based on previous errors. Right now this code below does display the msgbox with the full result including code AT MsgBox myDom.XML but not the message that should display just address and zip5.

Private Sub Command0_Click()


Const cstrXPath As String = "/Address:Address1/Address2/City/State/Zip5/Zip4"

Dim myDom As MSXML2.DOMDocument
Dim xmlElement As MSXML2.IXMLDOMElement
Dim xmlSelection As MSXML2.IXMLDOMSelection
Dim i As Long
Dim myXML As String

 myXML = "http://production.shippingapis.com/ShippingAPITest.dll?API=Verify&XML=" & _
    "<AddressValidateRequest%20USERID=" & Chr(34) & "XXXXXXACCOUNTXX" & Chr(34) & "><Address>" & _
    "<Address1></Address1>" & _
    "<Address2>6406 Ivy Lane</Address2><City>Greenbelt</City><State>MD</State>" & _
    "<Zip5></Zip5><Zip4></Zip4></Address></AddressValidateRequest>"

'Set myDom = CreateObject("MSXML2.DOMDocument")
Set myDom = New MSXML2.DOMDocument
myDom.async = False
myDom.Load (myXML)

Set xmlSelection = myDom.selectNodes(cstrXPath)
'Debug.Print "xmlSelection.Length: " & xmlSelection.length
'i = 1
For Each xmlElement In xmlSelection
    MsgBox xmlElement.getAttribute("Address1") & xmlElement.getAttribute("Zip5")
 '   i = i + 1
Next xmlElement

    MsgBox myDom.XML
End Sub

Upvotes: 0

Views: 217

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

This might be rather unsophisticated but it seems to get the job done:

Option Compare Database
Option Explicit

Sub xmlParseTest()
    Dim myDom As New MSXML2.DOMDocument
    myDom.SetProperty "SelectionLanguage", "XPath"
    ' test data
    myDom.LoadXML _
            "<?xml version=""1.0""?>" & _
            "<AddressValidateResponse>" & _
                "<Address>" & _
                    "<Address2>6406 IVY LN</Address2>" & _
                    "<City>GREENBELT</City>" & _
                    "<State>MD</State>" & _
                    "<Zip5>20770</Zip5>" & _
                    "<Zip4>1441??</Zip4>" & _
                "</Address>" & _
            "</AddressValidateResponse>" & _
            ""
    Dim addr As MSXML2.IXMLDOMElement
    For Each addr In myDom.SelectNodes("//Address")
        Debug.Print "Start of Address"
        Dim item As MSXML2.IXMLDOMElement
        For Each item In addr.ChildNodes
            Debug.Print "    " & item.BaseName & ": " & item.Text
        Next
        Set item = Nothing
        Debug.Print "End of Address"
    Next
    Set addr = Nothing
    Set myDom = Nothing
End Sub

producing the following output in the VBA Immediate window:

Start of Address
    Address2: 6406 IVY LN
    City: GREENBELT
    State: MD
    Zip5: 20770
    Zip4: 1441??
End of Address

Upvotes: 1

Related Questions