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.
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
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.
Code:
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
Thanks for any advice you can offer.
Jim