?
Solved

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

Posted on 2006-05-18
9
Medium Priority
?
240 Views
Last Modified: 2008-02-26
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!
0
Comment
Question by:Andrew Angell
  • 5
  • 2
7 Comments
 
LVL 7

Expert Comment

by:Hecatonchires
ID: 16714117
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
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 16719439
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">&nbsp;</td>
                  <td>&nbsp;</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">&nbsp;</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?

0
 
LVL 7

Expert Comment

by:Hecatonchires
ID: 16730749
I'm sorry, I have very restricted web access here at work.  Can you cut and paste the xml?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 7

Expert Comment

by:Hecatonchires
ID: 16730804
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.


0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 16764499
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>
0
 
LVL 7

Accepted Solution

by:
Hecatonchires earned 2000 total points
ID: 16781596
(The weekend was a killer.)

Ok, thats not working becuse of the dollar signs. It can't cast them to the right type. You'll need to take them off:

carrierRate = centralFreightNode.selectSingleNode("RATE").text
to
dim tempRate
tempRate = centralFreightNode.selectSingleNode("RATE").text
carrierRate = cdbl(right(tempRate, len(tempRate) - 1))
0
 
LVL 7

Expert Comment

by:Hecatonchires
ID: 16812562
How's this going?
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question