• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

XML - Looping through distinct node - URGENT REQUEST


I hope I can clearly explain what I am looking for.   In the ASP code below, you will see the statement "Set oNodes = oXML.selectNodes("//z:row[@WHSE='AK' and @FDATE<999901 and @FDATE>200403 ]")"   As you can see, I am selecting a specific Warehouse(WHSE) record and then running the remaining code against that record.  The code works fine...execept for the fact that I want to run the code for each distinct WHSE.   In my XML data file there will be multiple rows of data for each Warehouse locations.  I want to select each distinct one, run the calculations below and move on to the next distinct WHSE.   In the end, I will be incrementing a counter of locations that are in a negative variance and will render that value back to the HTML screen.

Thank you in advance for your help.   THIS IS A HOT REQUEST!  

ASP Server Side Code:


' Declarations

    Dim myValue
    Dim mySalesSumHist
    Dim myCostSumHist    
    Dim mySalesSumCur
    Dim myGMCur
    Dim myCostSumCur
    Dim myVarianceCount

    mySalesSumHist = 0
    myCostSumHist = 0
    myGMHist = 0
    myVarianceCount = 0

    Set oXML = Server.CreateObject("MSXML2.DOMDocument")
    oXML.setProperty "SelectionNamespaces", "xmlns:x='#RowsetSchema'"
    Set oNodes = oXML.selectNodes("//z:row[@WHSE='AK' and @FDATE<999901 and @FDATE>200403 ]")
    Set vNodes = oXML.selectNodes("//z:row[@WHSE='AK' and @FDATE=999901]")

    If oNodes.length > 0 Then

        For Each oNode In oNodes
        SalesHist = oNode.selectSingleNode("@c3").text
        CostsHist = oNode.selectSingleNode("@c4").text
        mySalesSumHist = mySalesSumHist + SalesHist
        myCostSumHist = myCostSumHist + CostsHist
    End If

    If vNodes.length > 0 Then

        For Each vNode In vNodes
        SalesCur = vNode.selectSingleNode("@c3").text
        CostsCur = vNode.selectSingleNode("@c4").text
        mySalesSumCur = mySalesSumCur + SalesCur
        myCostSumCur = myCostSumCur + CostsCur
     End If  
   ' Do the math

     myGMHist = ((mySalesSumHist - myCostSumHist)/mySalesSumHist)*100
     myGMCur = ((mySalesSumCur - myCostSumCur)/mySalesSumCur)*100
     myGMVar = myGMCur - myGMHist

   ' Now update the variance counter if a WHSE exceeds limits

      If myGMVar < 0 then
            myVarianceCounter = myVarianceCounter + 1

      End if
   ' Now lets render the results of the counter back to the HTML screen  
     Response.write "<form name='myForm'>"
     Response.write "<input type='Text' id='myVarianceCounter' value='" & myVarianceCounter & "' />"
     Response.write "</form>"

1 Solution
In XSLT this sort of thing is pretty easy, but you should be able to do it with DOM methods:

I think I'd do it by first iterating over all row nodes, and building a dictionary object (associative array) of unique values for WHSE. Actually it could be a simple one dimensional array of strings, with a bit of logic to see if a value exists before adding a new one.  Then iterate over the items and construct your nodesets.

Mike Sharp

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now