Using Excel VBA to process XML web query response (1 Viewer)

Bilbo_Baggins_Esq · Mar 23, 2008

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:

Code:

        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)

Code:

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:

Code:

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?

Bilbo_Baggins_Esq · Mar 24, 2008

I figured it out,,, finally...

Code:

    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

Using Excel VBA to process XML web query response (1 Viewer)

Bilbo_Baggins_Esq

Registered User.

Bilbo_Baggins_Esq

Registered User.

Similar threads

Users who are viewing this thread