VBA Importing multiple XML children under a single parent node

Question

I'm trying to import data from a XML response into an excel sheet and I am having difficulty with repeated child nodes under a single parent node. I need to be able to import the CategoryID, CategoryName, and CategoryParentName lines for each category. I've been able to get the first two but cant figure out the CategoryParentName since its repeated. The XML is as follows.

<GetSuggestedCategoriesResponse xmlns="urn:ebay:apis:eBLBaseComponents">
  <Timestamp>2010-01-19T22:08:02.568Z</Timestamp>
  <Ack>Success</Ack>
  <Version>647</Version>
  <Build>E647_CORE_BUNDLED_10438248_R1</Build>
  <SuggestedCategoryArray>
    <SuggestedCategory>
      <Category>
        <CategoryID>18871</CategoryID>
        <CategoryName>Memory Cards</CategoryName>
        <CategoryParentID>625</CategoryParentID>
        <CategoryParentID>3327</CategoryParentID>
        <CategoryParentID>18866</CategoryParentID>
        <CategoryParentName>Cameras & Photo</CategoryParentName>
        <CategoryParentName>Camera Accessories</CategoryParentName>
        <CategoryParentName>Digital Camera Accessories</CategoryParentName>
      </Category>
      <PercentItemFound>4</PercentItemFound>
    </SuggestedCategory>
    <SuggestedCategory>
      <Category>
        <CategoryID>48629</CategoryID>
        <CategoryName>Color</CategoryName>
        <CategoryParentID>293</CategoryParentID>
        <CategoryParentID>14948</CategoryParentID>
        <CategoryParentID>48633</CategoryParentID>
        <CategoryParentID>48638</CategoryParentID>
        <CategoryParentID>48628</CategoryParentID>
        <CategoryParentName>Electronics</CategoryParentName>
        <CategoryParentName>Gadgets</CategoryParentName>
        <CategoryParentName>Surveillance</CategoryParentName>
        <CategoryParentName>Surveillance Cameras</CategoryParentName>
        <CategoryParentName>Wired Cameras</CategoryParentName>
      </Category>
      <PercentItemFound>4</PercentItemFound>
    </SuggestedCategory>

Here's my attempt for the VBA code. Any help is greatly appreciated!

Sub CategoryResponse()
Outputrow = Sheet1.Range("E1") + 7
        
Nodes = "GetSuggestedCategoriesResponse/SuggestedCategoryArray/SuggestedCategory"
For Each CAT In responseItem.SelectNodes(Nodes)
   Outputcol = 3
   Sheet1.Range("A" & Outputrow).Value = CAT.SelectSingleNode("PercentItemFound").Text
   Sheet1.Range("B" & Outputrow).Value = CAT.SelectSingleNode("Category/CategoryID").Text
        
   Nodes1 = "GetSuggestedCategoriesResponse/SuggestedCategoryArray/SuggestedCategory/CategoryParentName"
   For Each PAR In responseItem.SelectNodes(Nodes1)
      Sheet1.Cells(Outputrow, Outputcol).Value = PAR.SelectSingleNode("CategoryParentName").Text
      Outputcol = Outputcol + 1
   Next PAR
   
   Sheet1.Cells(Outputrow, Outputcol).Value = UST.SelectSingleNode("Category/CategoryName").Text
   Outputrow = Outputrow + 1
Next UST

End Sub

barrowc barrowc · Accepted Answer · 2015-06-19T00:41:37

Nodes1 should be created relative to the current node in the For Each CAT ... loop and not relative to responseItem. Try:

Nodes1 = "Category/CategoryParentName"
For Each PAR In CAT.SelectNodes(Nodes1)
   Sheet1.Cells(Outputrow, Outputcol).Value = PAR.Text
   Outputcol = Outputcol + 1
Next PAR

You want to select the CategoryParentName nodes which relate to the current SuggestedCategory node. Your original code would have returned all of the CategoryParentNames (which matched the selectNodes expression) regardless of which SuggestedCategory node they related to

VBA Importing multiple XML children under a single parent node

1 Answers