如何用VBA遍历Ebay API的XML提取全部图片URL？

尝试从给定XML中提取三个图片URL，但现有VBA代码仅能获取第一个，无法遍历所有URL。

原XML结构 #

<PictureDetails>
    <GalleryType>Gallery</GalleryType>
    <PhotoDisplay>PicturePack</PhotoDisplay>
        <PictureURL>https://i.ebayimg.com/00/s/ODAwWDgwMA==/z/0zYAAOSwkARdzcXA/$_57.JPG?set_id=8800005007</PictureURL>
       <PictureURL>https://i.ebayimg.com/00/s/ODAwWDgwMA==/z/Dq4AAOSwa-hdxedG/$_57.JPG?set_id=8800005007</PictureURL>   
       <PictureURL>https://i.ebayimg.com/00/s/ODAwWDgwMA==/z/xtYAAOSwwMxdxedH/$_57.JPG?set_id=8800005007</PictureURL>
</PictureDetails>

现有VBA代码 #

Set objXML = New MSXML2.DOMDocument
     objXML.LoadXML (objHTTP.ResponseText)

     Dim xItemList As IXMLDOMNodeList
     Set xItemList = objXML.SelectNodes("//PictureDetails")

     Dim xItem As IXMLDOMNode

     For Each xItem In xItemList
     
        eBayImgURL = xItem.SelectNodes("//PictureURL").Item(0).Text
        Debug.Print "Image URL"
        Debug.Print eBayImgURL
        
     Next

问题原因 #

1. //PictureURL是全局搜索，会从XML根节点查找所有PictureURL，再取Item(0)自然每次都拿到第一个URL。
2. 仅循环PictureDetails节点（通常只有一个），但内部没有遍历每个PictureURL子节点。

修正后的代码 #

方案一：直接获取所有PictureURL节点

Set objXML = New MSXML2.DOMDocument
objXML.LoadXML (objHTTP.ResponseText)

Dim picNodes As IXMLDOMNodeList
Set picNodes = objXML.SelectNodes("//PictureDetails/PictureURL")

Dim picNode As IXMLDOMNode
For Each picNode In picNodes
    Debug.Print "Image URL"
    Debug.Print picNode.Text
Next

方案二：在PictureDetails节点下遍历子节点

Set objXML = New MSXML2.DOMDocument
objXML.LoadXML (objHTTP.ResponseText)

Dim xItemList As IXMLDOMNodeList
Set xItemList = objXML.SelectNodes("//PictureDetails")

Dim xItem As IXMLDOMNode
Dim picNodes As IXMLDOMNodeList
Dim picNode As IXMLDOMNode

For Each xItem In xItemList
    ' 使用相对路径查找当前PictureDetails下的PictureURL，而非全局搜索
    Set picNodes = xItem.SelectNodes("PictureURL")
    For Each picNode In picNodes
        Debug.Print "Image URL"
        Debug.Print picNode.Text
    Next
Next

说明 #

• 用//PictureDetails/PictureURL或在xItem下用PictureURL（相对路径），能精准定位到目标节点。
• 嵌套循环遍历每个PictureURL节点，即可输出所有图片URL。

内容的提问来源于stack exchange，提问作者Vince