Excel VBA XML Navigation

Question

我在Excel 2007中使用VBA从某些XML中提取值时遇到一些问题。以下是数据的示例：

<work_order xmlns="http://www.sample.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:schemaLocation="http://www.sample.com" id="">
<link rel="self" type="application/xml" href="http://www.sample.com"/>
<work_order_info>...</work_order_info>
<entry_info>...</entry_info>
<posting_info>...</posting_info>
<instructions>...</instructions>
<address_info>...</address_info>
<crew_time>
    <confirm_date>04 Dec 2013</confirm_date>
    <minutes_required>100</minutes_required>
</crew_time>
<wo_products>
    <wo_product>
        <product_description>pants</product_description>
    </wo_product>
    <wo_product>
        <product_description>shirt</product_description>
    </wo_product>
    <wo_product>
        <product_description>shoes</product_description>
    </wo_product>
    <wo_product>
        <product_description>hat</product_description>
    </wo_product>
    <wo_product>
        <product_description>DPSB1</product_description>
    </wo_product>
</wo_products>

我需要做的是循环通过每个wo_product节点并获得productdescription。到目前为止，我拥有的是：

Dim nodeList As IXMLDOMNodeList
Dim node As IXMLDOMNode
Dim oXMLDoc As MSXML2.DOMDocument
Set oXMLDoc = New MSXML2.DOMDocument
oXMLDoc.async = False
oXMLDoc.Load "http://www.sample.com"
Set nodeList = oXMLDoc.SelectNodes("/work_order/wo_products/wo_product")
For Each node In nodeList
    Dim description As String
    description = node.SelectSingleNode("//product_description").Text
    MsgBox (description)
Next node

它循环了5次，但信息框每次都写着"裤子"。有什么想法吗？

Answer 1

使用description = node.SelectSingleNode("product_description").Text。