CODEWITHSUNDEEP
xmlexcelvba
Morgan Robinson
Morgan Robinson

Reputation: 13

VBA Importing multiple XML children under a single parent node

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

Upvotes: 1

Views: 883

Answers (1)

barrowc
barrowc

Reputation: 10689

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

Upvotes: 1

Related Questions