Using VBA to process web response.xml

[Bilbo_Baggins_Esq]

I'm having trouble ascertaining the correct syntax for processing an XML response where the response contains multiple nodes with the same name within a subset.

Ultimately, I will move this all over to my production VBA app which connects to my Access db. However, the sample is provided in an Excel VBA Macro so I'm working with it for now in VBA.
Since i can step the VBA, it seems easier to work in VBA for now.

I added some code to my VBA to save a local copy of the response.xml for GetSearchResults.
I've located items which have some listing enhancement(s).
Primarily, with the query I'm using, ListingEnhancement are Border and BoldTitle (In that order when they are both present).

Here is a sample item from the entire array:
- <SearchResultItem>
- <Item>
<ItemID>xxxxxxxxxxxx</ItemID>
- <ListingDetails>
<StartTime>2008-03-17T07:14:54.000Z</StartTime>
<EndTime>2008-03-24T02:00:00.000Z</EndTime>
<ViewItemURL>EDITED OUT</ViewItemURL>
<ViewItemURLForNaturalSearch>EDITED OUT</ViewItemURLForNaturalSearch>
</ListingDetails>
- <SellingStatus>
<BidCount>21</BidCount>
<CurrentPrice currencyID="USD">250.0</CurrentPrice>
</SellingStatus>
<Site>US</Site>
<Title>The North Face Himalayan down Parka Jacket 850 fill</Title>
<Currency>USD</Currency>
<ListingType>Chinese</ListingType>
<GiftIcon>0</GiftIcon>
<SubTitle>Expedition Quality Parka, warm, many technical features</SubTitle>
<ListingEnhancement>Border</ListingEnhancement>
<ListingEnhancement>BoldTitle</ListingEnhancement>
<PaymentMethods>PayPal</PaymentMethods>
<Country>US</Country>
<PostalCode>80487</PostalCode>
- <ShippingDetails>
<ShippingType>Calculated</ShippingType>
</ShippingDetails>
- <SearchDetails>
<BuyItNowEnabled>false</BuyItNowEnabled>
</SearchDetails>
- <PictureDetails>
<GalleryType>Gallery</GalleryType>
</PictureDetails>
<ListingDuration>Days_7</ListingDuration>
</Item>
</SearchResultItem>

As you can see from the above sample, <ListingEnhancement> has two nodes inside this one item (item number). I am sure in some cases there may be more nodes, but I have just used this one as a sample.

In my VBA I am running a For Each n. as follows:

        For Each n In response.selectNodes("GetSearchResultsResponse/SearchResultItemArray/SearchResultItem/Item")
            If n.nodeName = "Item" Then

Thus each Item in the Array is processed seperately.
My code successfully captures and ports all single nodes over to the Excel sheet just fine.
However, when I get to <ListingEnhancement> I have issues because there are multiple nodes.

I have been able to make this code capture one node (if there is only one node or the first node if there is more than one node)

If Not (n.selectSingleNode("ListingEnhancement") Is Nothing) Then
    If (n.selectSingleNode("ListingEnhancement").Text) = "Border" Then
        Range("q" & outputRow).Value = "True"
    End If

    If (n.selectSingleNode("ListingEnhancement").Text) = "BoldTitle" Then
        Range("r" & outputRow).Value = "True"
    End If
End If

In the above code, I am first checking to see if the n. even contains a <ListingEnhancement> node, and if so, I ascertain its value by process of elimination.
I can even reverse the two above node test sequences and I still get a valid return for the first node.
However, I do not get the second node at all.

Working somewhat differently, I have also tried keeping each possible <listingEnhancement> separate as follows:

If Not (n.selectSingleNode("ListingEnhancement") Is Nothing) Then
    If (n.selectSingleNode("ListingEnhancement").Text = "Border") Then
        Range("q" & outputRow).Value = "True"
    End If
End If

If Not (n.selectSingleNode("ListingEnhancement") Is Nothing) Then
    If (n.selectSingleNode("ListingEnhancement").Text = "BoldTitle") Then
        Range("r" & outputRow).Value = "True"
    End If
End If

The end result is the same.
I capture the first <ListingEnhancement> node, but none of the subsequent nodes.
I always get an error, or if I put an "If Not" handler in, it just skips on to the rest of the code to capture other node sections.

So I'm still missing something here.

Is it possible to define a For Each / Next to work within an exiting For Each / Next ?

Is it possible to return a count of the <ListingEnhancement> nodes and use that count to force the read line down to the next node?

 

[gemma-the-husky]

ive been messing with xml

i'm not sure what you want to do with this,

but if there are multiple tags of the same value, how can you distinguish between the tags you are locating, without knowing the tree from which they are derived - ie how do you know which of these multiple values to return

therefore it seems to me you have to walk the whole tree to trace the tags in the nodes/subnodes to determine the structure of the tree, and pick out the elements you need in the order you need them.

but imnot 100% sure

given that file/import/xml just seems a waste of time. it isnt/wasnt easy to do

 

[Bilbo_Baggins_Esq]

ive been messing with xml

i'm not sure what you want to do with this,

I have a functional VB app which harvests data and ports it to a database which I then use for a search engine.

but if there are multiple tags of the same value, how can you distinguish between the tags you are locating, without knowing the tree from which they are derived - ie how do you know which of these multiple values to return

Wthin the array, there are up to 400 ItemID nodes
Each ItemID node has its own child nodes, and each are known from a defined list of possibilities. Not each one is present and are defined in the API as "conditional". Therefore a test must first be performed inside each ItemID node, in order to confirm its presence and then to capture its value if true.

There are certain nodes within each ItemID which may have multiple values.
<ListingEnhancement> is one.
<PaymentMethod> is another.

There is a defined list of each possible value for each repititious node. For purposes of this example, I have not included all the possibilities, but the list(s) is/are defined.

therefore it seems to me you have to walk the whole tree to trace the tags in the nodes/subnodes to determine the structure of the tree, and pick out the elements you need in the order you need them.

This may be true, and might work, but I cannot figure out how to "walk the tree". I get hung at the first <ListingEnhancement> node and cannot read past it unless it is to move on to a different node name.

given that file/import/xml just seems a waste of time. it isnt/wasnt easy to do

For the production code, you're right, saving the local copy is a waste of time. However, I did it in this circumstance in order to see how the potential values are presented in the response.xml.
Additionally, saving the local copy is actually quite easy with:

response.Save (responseFileLocation)

 

[gemma-the-husky]

to walk a tree you generally need a recursive algortithm - its far easier to write

so assume a start node called "rootnode"

then the recursive procedure is simply as below -(if you are famiiliar with recursion it will make sense, otherwise it may need a bit of thinking about - basically it moves down nodes, examining every subnode until it reaches a terminal node (leaf) then backs up back to the original node, and repeats this until every node has been examined)

[you can put extra things into the parameters, because eg you may need to know the name of the parent when you are traversing the treee, although the tree itself (as managed by the dll) is clearly multilinked, and you can derive much of the info with mynode.parent.name or some such reference]

sub traversetree(mynode as nodetype)

if mynode.haschildren then
   {process this node if required, then}
   for each childnode in mynode
         traversetree(childnode)
   next
else
   {no more children - process terminal leaf node}
   processnode
end if
end sub

and just call it with

sub main 
   {some procedure to establish a root node}
   call traversetree(rootnode)
end sub

there is a downloadable dll file (doesnt need registering) that has all the node type declarations to do this - you probably already have it. its just establishing the correct methods etc thats tricky. Note that you need a dll written in C or something similar - you cant do this directly in vba because vba doesnt have a pointer data type, and you really need pointers to handle trees (as pointers just dereference memory locations). i dont think collections can be used to manage a tree, although maybe they can.

 

[Bilbo_Baggins_Esq]

I figured it out,,, finally...

    If Not (n.selectSingleNode("ListingEnhancement") Is Nothing) Then ' identifies if the ItemID node contains a <ListingEnhancement>
        For Each x In n.selectNodes("ListingEnhancement")
            If x.nodeName = "ListingEnhancement" Then
                If (x.Text) = "Border" Then
                    Range("r" & outputRow).Value = "True"
                End If
                If (x.Text) = "BoldTitle" Then
                    Range("q" & outputRow).Value = "True"
                End If
                If (x.Text) = "Highlight" Then
                    Range("t" & outputRow).Value = "True"
                End If
            End If
        Next
    End If