Acessing elements from AMAZON XML API in Excel VBA

Question

I am trying to parse data from Amazon Product Advertising API XML response. I am using DOMDocument in excel vba to load the xml document and parse the resposne. This is how the XML response looks like:

<ItemLookupResponse xmlns="http://webservices.amazon.com/AWSECommerceService/2011-08-01">
<OperationRequest>
<RequestId>fd9f1314-29932-45a0-8fc4-c999276425f4</RequestId>
<RequestProcessingTime>0.0265880000000000</RequestProcessingTime>
</OperationRequest>
<Items>
<Request>
<IsValid>True</IsValid>
<ItemLookupRequest>
<IdType>ASIN</IdType>
<ItemId>B00CSDILZI</ItemId>
<ResponseGroup>Offers</ResponseGroup>
<VariationPage>All</VariationPage>
</ItemLookupRequest>
</Request>
<Item>
<ASIN>B00CSDILZI</ASIN>
<OfferSummary>
<LowestNewPrice>
<Amount>26761</Amount>
<CurrencyCode>USD</CurrencyCode>
<FormattedPrice>$267.61</FormattedPrice>
</LowestNewPrice>
<TotalNew>22</TotalNew>
<TotalUsed>0</TotalUsed>
<TotalCollectible>0</TotalCollectible>
<TotalRefurbished>0</TotalRefurbished>
</OfferSummary>
<Offers>
<TotalOffers>1</TotalOffers>
<TotalOfferPages>1</TotalOfferPages>
MoreOffersUrl>...</MoreOffersUrl>
<Offer>
<OfferAttributes>...</OfferAttributes>
<OfferListing>
<OfferListingId>...</OfferListingId>
<Price>
<Amount>26761</Amount>
<CurrencyCode>USD</CurrencyCode>
<FormattedPrice>$267.61</FormattedPrice>
</Price>
<AmountSaved>
<Amount>23841</Amount>
<CurrencyCode>USD</CurrencyCode>
<FormattedPrice>$238.41</FormattedPrice>
</AmountSaved>
<PercentageSaved>47</PercentageSaved>
<Availability>Usually ships in 1-2 business days</Availability>
<AvailabilityAttributes>
<AvailabilityType>now</AvailabilityType>
<MinimumHours>24</MinimumHours>
<MaximumHours>48</MaximumHours>
</AvailabilityAttributes>
<IsEligibleForSuperSaverShipping>0</IsEligibleForSuperSaverShipping>
</OfferListing>
</Offer>
</Offers>
</Item>
</Items>
</ItemLookupResponse>

I am trying to loop through the entire xml and extract item details like ASIN, FormattedPrice, Lowest price etc to a excel sheet. This is how I tried:

Sub subReadXMLStream()

Dim xmlDoc As MSXML2.DOMDocument
Dim xEmpDetails As MSXML2.IXMLDOMNode
Dim xParent As MSXML2.IXMLDOMNode
Dim xChild As MSXML2.IXMLDOMNode
Dim Col, Row As Integer

Set xmlDoc = New MSXML2.DOMDocument
xmlDoc.async = False
xmlDoc.validateOnParse = False
' use XML string to create a DOM, on error show error message
If Not xmlDoc.Load("E:\web\offersumm.xml") Then
    Err.Raise xmlDoc.parseError.ErrorCode, , xmlDoc.parseError.reason
End If

Set xEmpDetails = xmlDoc.DocumentElement
Set xParent = xEmpDetails.FirstChild
Row = 1
Col = 1

Dim xmlNodeList As IXMLDOMNodeList

Set xmlNodeList = xmlDoc.SelectNodes("//Item")
For Each xParent In xmlNodeList
    For Each xChild In xParent.ChildNodes
        Worksheets("Sheet2").Cells(Row, Col).Value = xChild.Text
        Debug.Print Row & " - "; Col & " -  " & xChild.Text
        Col = Col + 1
    Next xChild
    Row = Row + 1
    Col = 1
Next xParent

End Sub

However This is printing the entire text in the tags as a like

1 - 1 -  B00CSDILZI
1 - 2 -  26761 USD $267.61 22 0 0 0
1 - 3 -  1 1 ... ... ... 26761 USD $267.61 23841 USD $238.41 47 Usually ships in 1-2 business days now 24 48 0

But what I am trying to achieve is get elements by its tag name and the print then in cells. Can any one tell me what I am doing wrong here ?

Answer 1

The following code gets the ASIN, Offers/Offer[0]/OfferListing/Price/FormattedPrice and Offers/Offer[0]/OfferListing/Availability for each Item element in the XML. It supposes there are multiple Offer elements possible in Offersbut only one OfferListing in each Offer. If there are multiple Offer elements, it will get the first.

The syntax using in SelectNodes and SelectSingleNode is XPath, see http://en.wikipedia.org/wiki/XPath. For MSXML2.DOMDocument see https://msdn.microsoft.com/en-us/library/aa923288.aspx

Sub subReadXMLStream()

Dim xmlDoc As MSXML2.DOMDocument
Dim xEmpDetails As MSXML2.IXMLDOMNode
Dim xParent As MSXML2.IXMLDOMNode
Dim xChild As MSXML2.IXMLDOMNode
Dim lCol, lRow As Long

Set xmlDoc = New MSXML2.DOMDocument
xmlDoc.async = False
xmlDoc.validateOnParse = False

If Not xmlDoc.Load("P:\offersumm.xml") Then
    Err.Raise xmlDoc.parseError.ErrorCode, , xmlDoc.parseError.reason
End If

Set xEmpDetails = xmlDoc.DocumentElement
Set xParent = xEmpDetails.FirstChild
lRow = 1
lCol = 1

Dim xmlNodeList As IXMLDOMNodeList

Dim sFormattedPrice As String, sAvailability As String

Set xmlNodeList = xmlDoc.SelectNodes("//Item")
For Each xParent In xmlNodeList
    For Each xChild In xParent.ChildNodes
        If xChild.nodeName = "ASIN" Then
            Worksheets("Sheet2").Cells(lRow, lCol).Value = xChild.Text
            lCol = lCol + 1
        ElseIf xChild.nodeName = "Offers" Then
            sFormattedPrice = xChild.SelectSingleNode("Offer[0]/OfferListing/Price/FormattedPrice").Text
            Worksheets("Sheet2").Cells(lRow, lCol).Value = sFormattedPrice
            lCol = lCol + 1
            sAvailability = xChild.SelectSingleNode("Offer[0]/OfferListing/Availability").Text
            Worksheets("Sheet2").Cells(lRow, lCol).Value = sAvailability
            lCol = lCol + 1
        End If
    Next xChild
    lRow = lRow + 1
    lCol = 1
Next xParent

End Sub