asked on

How to edit this script involving XML parsing...???

I've got a script setup right now that takes an XML and grabs a specific node based on the value of one of the elements and then parses out the data for that node. An XML example is here: http://www.carvertubs.com/temp/applications/freightQuote/xmlResponse/15497231.xml. The current script I'm using is as follows:

'Select the <CARRIER> node based on the value of <SCAC> child node
Set centralFreightNode = objXML.selectSingleNode("/FQQUOTE/CARRIER[SCAC='CEDE']")

'Make sure the node was found
If NOT TypeName(centralFreightNode)="Nothing" Then
      'Node was found - now get the values you want from the XML
      carrierRate = centralFreightNode.selectSingleNode("RATE").text
      freightCost = centralFreightNode.selectSingleNode("DETAIL/FREIGHTCOST").text
      arrivalNotification = centralFreightNode.selectSingleNode("DETAIL/ARRIVAL_NOTIFICATION").text
      residentialFee = centralFreightNode.selectSingleNode("DETAIL/NON-COMMERCIAL_DELIVERY").text
      liftGate = centralFreightNode.selectSingleNode("DETAIL/LIFTGATE_DELIVERY").text
      fuelSurcharge = centralFreightNode.selectSingleNode("DETAIL/FUEL_SURCHARGE").text
      transitTime = centralFreightNode.selectSingleNode("TRANSIT").text
      Set centralFreightNode = Nothing
Else
'Handle if not found
End If

The reason behind this is that we only wanted to ship with Central Freight, which isn't always returned by the API, depending on the particular request. So, if Central Freight is returned by the API this grabs that data and displays, otherwise I simply display a message asking them to call.

We've added a couple of carriers to the list of people that we can use. These carriers have different values, of course, for FQQUOTE/CARRIER/SCAC element. There are 3 different ones I can use now, CEDE, RDWY, and DAFG. How can I edit this so that it does what it's doing now for all 3 of these carriers, and then only returns the data for the one with the lowest value for RATE? At times all 3 will come back, at times only 1 or 2 will, and at very rare times none could come back.

Any information on this would be greatly appreciated. Thanks!

Hecatonchires

dim minrateval
dim minrateshipper
dim shipper
dim shippers(3)
shippers(0) = "CEDE"
shippers(1) = "RDWY"
shippers(2) = "DAFG"
minrateval = 9999999999999999 'a very large number

for each shipper in shippers
Set centralFreightNode = objXML.selectSingleNode("/FQQUOTE/CARRIER[SCAC='" & shipper & "']")

If NOT TypeName(centralFreightNode)="Nothing" Then
'Node was found - now get the values you want from the XML
carrierRate = centralFreightNode.selectSingleNode("RATE").text
Set centralFreightNode = Nothing
if carrierrate < minrateval then
minrateval = carrierrate
minrateshipper = shipper
end if
end if
next

Set centralFreightNode = objXML.selectSingleNode("/FQQUOTE/CARRIER[SCAC=" & minrateshipper & "]")

'Make sure the node was found
If NOT TypeName(centralFreightNode)="Nothing" Then
'Node was found - now get the values you want from the XML
carrierRate = centralFreightNode.selectSingleNode("RATE").text
freightCost = centralFreightNode.selectSingleNode("DETAIL/FREIGHTCOST").text
arrivalNotification = centralFreightNode.selectSingleNode("DETAIL/ARRIVAL_NOTIFICATION").text
residentialFee = centralFreightNode.selectSingleNode("DETAIL/NON-COMMERCIAL_DELIVERY").text
liftGate = centralFreightNode.selectSingleNode("DETAIL/LIFTGATE_DELIVERY").text
fuelSurcharge = centralFreightNode.selectSingleNode("DETAIL/FUEL_SURCHARGE").text
transitTime = centralFreightNode.selectSingleNode("TRANSIT").text
Set centralFreightNode = Nothing
Else
'Handle if not found
End If

Andrew Angell

ASKER

ok, this looks good, but I'm not getting the actual stuff to display when the carriers are returned. I'm just getting the 'handle if not found part no matter what gets returned in the XML. Here's a copy of the XML that I just got back that includes CEDE and RDWY but I got the error back as if none of them were returned: http://www.carvertubs.com/temp/applications/freightQuote/xmlResponse/15592895.xml

Here is the complete section of my script:

'grab the quotes for each shipper specefied and store the lowest rate in variables
dim minrateval
dim minrateshipper
dim shipper
dim shippers(3)
shippers(0) = "CEDE"
shippers(1) = "RDWY"
shippers(2) = "DAFG"
minrateval = 9999999999999999 'a very large number

for each shipper in shippers
Set centralFreightNode = objXML.selectSingleNode("/FQQUOTE/CARRIER[SCAC='"&shipper&"']")

If NOT TypeName(centralFreightNode)="Nothing" Then
'Node was found - now get the values you want from the XML
carrierRate = centralFreightNode.selectSingleNode("RATE").text
Set centralFreightNode = Nothing
if carrierrate < minrateval then
minrateval = carrierrate
minrateshipper = shipper
end if
end if
next

'Select the <CARRIER> node based on the value of <SCAC> child node
Set centralFreightNode = objXML.selectSingleNode("/FQQUOTE/CARRIER[SCAC='"&minrateshipper&"']")

'Make sure the node was found
If NOT TypeName(centralFreightNode)="Nothing" Then
      'Node was found - now get the values you want from the XML
      carrierRate = centralFreightNode.selectSingleNode("RATE").text
      handlingFee = Round((carrierRate * .1),2)
      handlingFee = FormatNumber((handlingFee),2)
      rateWithHandling = cdbl(carrierRate) + cdbl(handlingFee)
      rateWithHandling = FormatNumber((rateWithHandling),2)
      freightCost = centralFreightNode.selectSingleNode("DETAIL/FREIGHTCOST").text
      If destResidence = "TRUE" Then
            arrivalNotification = centralFreightNode.selectSingleNode("DETAIL/ARRIVAL_NOTIFICATION").text
            residentialFee = centralFreightNode.selectSingleNode("DETAIL/NON-COMMERCIAL_DELIVERY").text
      End If
      If destLiftGate = "TRUE" Then
            liftGate = centralFreightNode.selectSingleNode("DETAIL/LIFTGATE_DELIVERY").text
      End If
      fuelSurcharge = centralFreightNode.selectSingleNode("DETAIL/FUEL_SURCHARGE").text
      transitTime = centralFreightNode.selectSingleNode("TRANSIT").text
      If Request.QueryString("tubShell") = 1 Then
            totalPrice = FormatNumber(cdbl(productPrice) + cdbl(rateWithHandling),2)
      Else
            totalPrice = FormatNumber(cdbl(eBayPrice) + cdbl(rateWithHandling),2)
      End If

      Set centralFreightNode = Nothing
      %>
      <table width="400" border="0" align="center" cellpadding="2" cellspacing="2">
<tr>
<td align="right"> </td>
<td> </td>
</tr>
<tr>
<td width="149" align="right"><strong>Shipping Cost: </strong></td>
<td width="237">$<%=rateWithHandling%></td>
</tr>

<tr>
<td colspan="2" align="center"><span class="copyright"><strong>NOTE</strong>: Changing fuel prices can cause slight differences in shipping rates on any given day. The rate returned here is a REAL-TIME quote as of <%=date()%>.</span></td>
</tr>
<tr>
<td colspan="2" align="center"> </td>
</tr>
</table>
       <%
Else 'Node was not found, handle this here
      %>
      We're Sorry. None of our carriers returned a rate for this particular quote. Please give us a call TOLL FREE at 866-592-8169 to receive a shipping quote.

      <%
End If
%>

Any ideas?

Hecatonchires

I'm sorry, I have very restricted web access here at work. Can you cut and paste the xml?

Hecatonchires

Ahh, also:
dim shippers(3) -> dim shippers(2)
I always forget in vb that the number in the brackets is the upper bound, not the number of elements in the array...

its doing an extra loop where shipper = "" and prolly returning zero for a nonexistant nodes value of carrierRate, making it the lowest rate and therefore the default carrier.

Andrew Angell

ASKER

Here's the XML...I switched the shippers(3) to shippers(2) and I'm getting the same result.

<?xml version="1.0" encoding="ISO-8859-1"?><FQQUOTE QUOTEID="15676862"><CARRIER OPTIONID="1"><CARRIERNAME>DO NOT USE</CARRIERNAME><SCAC>DNUE</SCAC><RATE>$70.80</RATE><DETAIL><FREIGHTCOST>$60.00</FREIGHTCOST><FUEL_SURCHARGE>$10.80</FUEL_SURCHARGE></DETAIL><TRANSIT>2</TRANSIT></CARRIER><CARRIER OPTIONID="2"><CARRIERNAME>MID-STATES EXPRESS</CARRIERNAME><SCAC>MSXN</SCAC><RATE>$87.44</RATE><DETAIL><FREIGHTCOST>$69.70</FREIGHTCOST><FUEL_SURCHARGE>$17.74</FUEL_SURCHARGE></DETAIL><TRANSIT>2</TRANSIT></CARRIER><CARRIER OPTIONID="3"><CARRIERNAME>USF HOLLAND</CARRIERNAME><SCAC>HMES</SCAC><RATE>$88.99</RATE><DETAIL><FREIGHTCOST>$70.56</FREIGHTCOST><FUEL_SURCHARGE>$18.43</FUEL_SURCHARGE></DETAIL><TRANSIT>1</TRANSIT></CARRIER><CARRIER OPTIONID="4"><CARRIERNAME>LAKEVILLE EXPRESS</CARRIERNAME><SCAC>LAKE</SCAC><RATE>$89.94</RATE><DETAIL><FREIGHTCOST>$77.40</FREIGHTCOST><FUEL_SURCHARGE>$12.54</FUEL_SURCHARGE></DETAIL><TRANSIT>1</TRANSIT></CARRIER><CARRIER OPTIONID="5"><CARRIERNAME>DAYTON FREIGHT LINES</CARRIERNAME><SCAC>DAFG</SCAC><RATE>$94.76</RATE><DETAIL><FREIGHTCOST>$78.29</FREIGHTCOST><FUEL_SURCHARGE>$16.47</FUEL_SURCHARGE></DETAIL><TRANSIT>1</TRANSIT></CARRIER><CARRIER OPTIONID="6"><CARRIERNAME>SAIA MOTOR FREIGHT</CARRIERNAME><SCAC>SAIA</SCAC><RATE>$102.99</RATE><DETAIL><FREIGHTCOST>$84.04</FREIGHTCOST><FUEL_SURCHARGE>$18.95</FUEL_SURCHARGE></DETAIL><TRANSIT>1</TRANSIT></CARRIER><CARRIER OPTIONID="7"><CARRIERNAME>OLD DOMINION FREIGHT LINE</CARRIERNAME><SCAC>ODFL</SCAC><RATE>$112.60</RATE><DETAIL><FREIGHTCOST>$87.82</FREIGHTCOST><FUEL_SURCHARGE>$24.78</FUEL_SURCHARGE></DETAIL><TRANSIT>1</TRANSIT></CARRIER><CARRIER OPTIONID="9"><CARRIERNAME>ROADWAY EXPRESS</CARRIERNAME><SCAC>RDWY</SCAC><RATE>$124.85</RATE><DETAIL><FREIGHTCOST>$103.15</FREIGHTCOST><FUEL_SURCHARGE>$21.70</FUEL_SURCHARGE></DETAIL><TRANSIT>2</TRANSIT></CARRIER><CARRIER OPTIONID="10"><CARRIERNAME>CENTRAL FREIGHT</CARRIERNAME><SCAC>CEDE</SCAC><RATE>$126.72</RATE><DETAIL><FREIGHTCOST>$105.69</FREIGHTCOST><FUEL_SURCHARGE>$21.03</FUEL_SURCHARGE></DETAIL><TRANSIT>2</TRANSIT></CARRIER><CARRIER OPTIONID="11"><CARRIERNAME>WATKINS MOTOR LINES</CARRIERNAME><SCAC>WWAT</SCAC><RATE>$131.45</RATE><DETAIL><FREIGHTCOST>$113.21</FREIGHTCOST><FUEL_SURCHARGE>$18.24</FUEL_SURCHARGE></DETAIL><TRANSIT>2</TRANSIT></CARRIER><CARRIER OPTIONID="12"><CARRIERNAME>UPS FREIGHT</CARRIERNAME><SCAC>OVNT</SCAC><RATE>$131.63</RATE><DETAIL><FREIGHTCOST>$112.32</FREIGHTCOST><FUEL_SURCHARGE>$19.31</FUEL_SURCHARGE></DETAIL><TRANSIT>1</TRANSIT></CARRIER><CARRIER OPTIONID="13"><CARRIERNAME>FEDEX FREIGHT</CARRIERNAME><SCAC>GGGG</SCAC><RATE>$137.46</RATE><DETAIL><FREIGHTCOST>$112.14</FREIGHTCOST><GUARANTEED_SERVICE>$.00</GUARANTEED_SERVICE><FUEL_SURCHARGE>$25.32</FUEL_SURCHARGE></DETAIL><TRANSIT>1</TRANSIT></CARRIER><CARRIER OPTIONID="14"><CARRIERNAME>WATKINS GUARANTEED QUALITY SERVICE</CARRIERNAME><SCAC>WWAG</SCAC><RATE>$159.98</RATE><DETAIL><FREIGHTCOST>$115.22</FREIGHTCOST><GUARANTEED_SERVICE>$27.00</GUARANTEED_SERVICE><FUEL_SURCHARGE>$17.76</FUEL_SURCHARGE></DETAIL><TRANSIT>2</TRANSIT></CARRIER><CARRIER OPTIONID="16"><CARRIERNAME>ROADWAY EXPRESS (GUARANTEED DAY PM)</CARRIERNAME><SCAC>RDWP</SCAC><RATE>$182.47</RATE><DETAIL><FREIGHTCOST>$103.37</FREIGHTCOST><GUARANTEED_SERVICE>$55.00</GUARANTEED_SERVICE><FUEL_SURCHARGE>$24.10</FUEL_SURCHARGE></DETAIL><TRANSIT>2</TRANSIT></CARRIER></FQQUOTE>

ASKER CERTIFIED SOLUTION

Hecatonchires

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hecatonchires

How's this going?