How to parse xml response in VBA?

[jimatqsi]

I'm working in ACCESS/VBA trying to get rates from UPS via XML. I have something that may well work but it strikes me that I may be approaching this in a very clumsy way.

I am using a series of for/next loops to loop through the nodes, and within each of those loops I have select case statements looking for individual tags. Any one tag may require drilling down into another node with for/next and select case until I get to a value I can use.

Is this really the best way to do that? I've read some things about XML processing that makes me think there's an easier way.

Here's the code. I'm sending xml to UPS and getting a response, so I'm going to start my code at the .send.

myHTTP.send (myXML)  '  sends rate request to ups
 
'MsgBox myDom.XML
Dim myNode As IXMLDOMNode
Dim oDoc As MSXML2.DOMDocument
Dim fSuccess As Boolean
Dim oRoot As MSXML2.IXMLDOMNode
 
 ' =================================================
    Set oDoc = New MSXML2.DOMDocument
    oDoc.async = False
    oDoc.validateOnParse = False
    fSuccess = oDoc.Load(myHTTP.responseXML)
         ' If anything went wrong, quit now.
 
        If Not fSuccess Then
            If ynInteractive Then
              MsgBox "problem loading response xml"
 
            End If
        End If
         ' Get the root of the XML tree.
        Set oRoot = oDoc.documentElement
 
        strNodeName = ""
         ' Go through all children of the root.
         myXML = myHTTP.responseText
 
        For Each oResponseResult In oRoot.childNodes ' for each node in the root?
            Select Case oResponseResult.nodeName
                Case "Response"
                Case "RatedShipment"
                    Dim oRatedShipmentResultNodes As MSXML2.IXMLDOMNodeList
                    Dim oRatedShipmentNode  As MSXML2.IXMLDOMNode
                    strWork = ""
'                    Dim oRatedShipmentResultNodes As MSXML2.IXMLDOMNodeList
'                    Dim oShipmentNode  As MSXML2.IXMLDOMNode
 
                    Set oRatedShipmentResultNodes = oResponseResult.childNodes
 
                    For Each oRatedShipmentNode In oRatedShipmentResultNodes   '  for each node under "rated shipment"
 
                        Select Case oRatedShipmentNode.nodeName
                                Case "service"
                                    ' service / code
                                Case "Ratedshipmentwarning"
                                    'ratedshipmentwarning/
                                Case "BillingWeight"
                                    'billingweight/unitofmeasurement/code
                                    'billingweight/weight
                                        Dim oBillingWeightResultNodes As MSXML2.IXMLDOMNodeList
                                        Dim oBillingWeightNode  As MSXML2.IXMLDOMNode
                                        Set oBillingWeightResultNodes = oRatedShipmentNode.childNodes
 
                                        For Each oBillingWeightNode In oBillingWeightResultNodes
 
                                            Select Case oBillingWeightNode.nodeName
                                                Case "UnitOfMeasurement"
                                                Case "Weight"
                                                Case Else
                                                    MsgBox "oBillingWeightNode " & oBillingWeightNode.nodeName & " Not handled"
                                            End Select '  oBillingWeightNode.nodeName
 
                                        Next  '  oBillingWeightNode In oBillingWeightResultNodes
                                        Set oBillingWeightResultNodes = Nothing
                                Case "TransportationCharges"
                                    'transportationcharges/currencycode
                                    'transportationcharges/monetaryvalue
                                        Dim oTransportChargesResultNodes As MSXML2.IXMLDOMNodeList
                                        Dim oTransportChargesNode  As MSXML2.IXMLDOMNode
                                        Set oTransportChargesResultNodes = oRatedShipmentNode.childNodes
 
                                        For Each oTransportChargesNode In oTransportChargesResultNodes
                                            Select Case oTransportChargesNode.nodeName
                                                Case "CurrencyCode"
                                                Case "MonetaryValue"
                                                Case Else
                                                    MsgBox "oTransportChargesNode " & oTransportChargesNode.nodeName & " Not handled"
                                            End Select '  oTransportChargesNode.nodeName
 
                                        Next  '  oTransportChargesNode In oTransportChargesResultNodes
                                        Set oTransportChargesResultNodes = Nothing
 
                                Case "ServiceOptionCharges"
                                    'serviceoptionscharges/currencycode/monetaryvalue
                                        Dim oServiceOptionResultNodes As MSXML2.IXMLDOMNodeList
                                        Dim oServiceOptionNode  As MSXML2.IXMLDOMNode
                                        Set oServiceOptionResultNodes = oRatedShipmentNode.childNodes
 
                                        For Each oServiceOptionNode In oServiceOptionResultNodes
                                            Select Case oServiceOptionNode.nodeName
                                                Case "CurrencyCode"
                                                Case "MonetaryValue"
                                                Case Else
                                                    MsgBox "oServiceOptionNode " & oServiceOptionNode.nodeName & " Not handled"
                                            End Select '  oServiceOptionNode.nodeName
 
                                        Next  '  oServiceOptionNode In oServiceOptionResultNodes
                                        Set oServiceOptionResultNodes = Nothing
 
                                Case "TotalCharges"
                                    'totalcharges/currencycode/monetaryvalue
                                        Dim oTotalChargesResultNodes As MSXML2.IXMLDOMNodeList
                                        Dim oTotalChargesNode  As MSXML2.IXMLDOMNode
                                        Set oTotalChargesResultNodes = oRatedShipmentNode.childNodes
 
                                        For Each oTotalChargesNode In oTotalChargesResultNodes
                                            Select Case oTotalChargesNode.nodeName
                                                Case "CurrencyCode"
                                                Case "MonetaryValue"
                                            End Select '  oTotalChargesNode.nodeName
 
                                        Next  '  oTotalChargesNode In oTotalChargesResultNodes
                                        Set oTotalChargesResultNodes = Nothing
 
                                Case "GuaranteedDaystoDelivery"
                                    'Guaranteeddaystodelivery
                                Case "ScheduledDeliveryTime"
                                    'ScheduledDeliveryTime
                                Case "RatedPackage"
                                    'RatedPackage/TransportationCharges/CurrencyCode
 
                                    'RatedPackage/TransportationCharges/MonetaryValue
                                    'ratedpackage/ServiceOptionsCharges/CurrencyCode
                                    '                               /MonetaryValue
                                    'ratedpackage/TotalCharges/CurrencyCode
                                    '                       MonetaryValue
                                    'ratedpackage/weight
                                    'ratedpackage/billingweight/unitofmeasurement/code
                                    'ratedpackage/billingweight/weight
                                        Dim oRatedPackageResultNodes As MSXML2.IXMLDOMNodeList
                                        Dim oRatedPackageNode  As MSXML2.IXMLDOMNode
                                        Set oRatedPackageResultNodes = oRatedShipmentNode.childNodes
 
                                        For Each oRatedPackageNode In oRatedPackageResultNodes
 
                                            Select Case oRatedPackageNode.nodeName
                                                Case "TransportationCharges"
                                                Case "ServiceOptionsCharges"
                                                Case "TotalCharges"
                                                Case "Weight"
                                                Case "BillingWeight"
                                                Case Else
                                                    MsgBox "RatedPackagesResult " & oRatedPackagesResultNode.nodeName & " Not handled"
                                            End Select '  oRatedPackageNode.nodeName
 
                                        Next  '  oRatedPackageNode In oRatedPackageResultNodes
 
                                    Set oRatedPackageResultNodes = Nothing
                                Case Else
                                    MsgBox "oRatedShipmentNode " & oRatedShipmentNode.nodeName & " Not handled"
 
                        End Select ' oRatedShipmentNode.nodeName
 
                    Next  'oRatedShipmentNode In oRatedShipmentResultNodes   '  for each node under "rated shipment"
                Case Else
                    MsgBox "oResponseResult='" & oResponseResult.nodeName
                End Select 'oResponseResult.nodeName
 

I've attached a text file with a sample response file from UPS (1 package) in case it's helpful.

Thanks for any advice you can offer.

Jim