使用 VBA 计算子节点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39556384/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Counting child nodes using VBA
提问by peskywinnets
I've some XML that I want to count the number of products it contains information about...but struggling to get the right syntax, here's my code...
我有一些 XML，我想计算它包含有关信息的产品数量......但努力获得正确的语法，这是我的代码......
Dim XMLHttpRequest As XMLHTTP60
Set XMLHttpRequest = New MSXML2.XMLHTTP60
Dim objxmlSKU As MSXML2.IXMLDOMNodeList
Dim SKUCount As MSXML2.IXMLDOMNodeList
Dim objxmldoc As New MSXML2.DOMDocument60
Dim xmlNode As MSXML2.IXMLDOMNodeList
Dim count As Integer
XMLHttpRequest.Open "GET", signedURL, False
XMLHttpRequest.send (signedURL)
objxmldoc.loadXML (XMLHttpRequest.responseXML.XML)
xmlNamespaces = "xmlns:ns1='https://mws.amazonservices.com/FulfillmentInventory/2010-10-01'"
objxmldoc.SetProperty "SelectionNamespaces", xmlNamespaces
Set SKUCount = objxmldoc.selectNodes("//ns1:InventorySupplyList/ns1:member")
count = 0
For Each member In SKUCount
count = count + 1
Next
Debug.Print count
& here's the sample XML (I wish to count the number of blocks headed up with member ...therefore in the following code there are two blocks with the XML name member..
& 这里是示例 XML（我想计算以成员为首的块的数量......因此在下面的代码中有两个带有 XML 名称成员的块..
<ListInventorySupplyResponse xmlns="http://mws.amazonaws.com/FulfillmentInventory/2010-10-01/">
<ListInventorySupplyResult>
<MarketplaceId>A1F83G8C2ARO7P</MarketplaceId>
<InventorySupplyList>
<member>
<Condition>NewItem</Condition>
<SupplyDetail/>
<TotalSupplyQuantity>43</TotalSupplyQuantity>
<EarliestAvailability>
<TimepointType>Immediately</TimepointType>
</EarliestAvailability>
<FNSKU>B005H38BZ4</FNSKU>
<InStockSupplyQuantity>43</InStockSupplyQuantity>
<ASIN>B005H38BZ4</ASIN>
<SellerSKU>dtp-11 fba</SellerSKU>
</member>
<member>
<Condition>NewItem</Condition>
<SupplyDetail/>
<TotalSupplyQuantity>40</TotalSupplyQuantity>
<EarliestAvailability>
<TimepointType>Immediately</TimepointType>
</EarliestAvailability>
<FNSKU>B01BvMUHM8</FNSKU>
<InStockSupplyQuantity>40</InStockSupplyQuantity>
<ASIN>B01BVMUHM8</ASIN>
<SellerSKU>dsx-90 fba</SellerSKU>
</member>
</InventorySupplyList>
</ListInventorySupplyResult>
<ResponseMetadata>
<RequestId>8a7d1832-b271-4171-991f-7e39feee5bf1</RequestId>
</ResponseMetadata>
</ListInventorySupplyResponse>
回答by Thomas G
Example on how to do this with your XML doc :
关于如何使用您的 XML 文档执行此操作的示例：
Public Sub Test_XML()
Dim SKUCount As MSXML2.IXMLDOMNodeList
Dim objxmldoc As New MSXML2.DOMDocument60
Dim count As Integer
Dim strXML As String
Dim node As MSXML2.IXMLDOMNode
strXML = strXML & "<ListInventorySupplyResponse> "
strXML = strXML & " <ListInventorySupplyResult> "
strXML = strXML & " <InventorySupplyList> "
strXML = strXML & " <member> "
strXML = strXML & " <Condition>NewItem</Condition> "
strXML = strXML & " <SupplyDetail/> "
strXML = strXML & " <TotalSupplyQuantity>43</TotalSupplyQuantity> "
strXML = strXML & " <EarliestAvailability> "
strXML = strXML & " <TimepointType>Immediately</TimepointType> "
strXML = strXML & " </EarliestAvailability> "
strXML = strXML & " <FNSKU>B005H38BZ4</FNSKU> "
strXML = strXML & " <InStockSupplyQuantity>43</InStockSupplyQuantity> "
strXML = strXML & " <ASIN>B005H38BZ4</ASIN> "
strXML = strXML & " <SellerSKU>dtp-11 fba</SellerSKU> "
strXML = strXML & " </member> "
strXML = strXML & " <member> "
strXML = strXML & " <Condition>NewItem</Condition> "
strXML = strXML & " <SupplyDetail/> "
strXML = strXML & " <TotalSupplyQuantity>40</TotalSupplyQuantity> "
strXML = strXML & " <EarliestAvailability> "
strXML = strXML & " <TimepointType>Immediately</TimepointType> "
strXML = strXML & " </EarliestAvailability> "
strXML = strXML & " <FNSKU>B01BvMUHM8</FNSKU> "
strXML = strXML & " <InStockSupplyQuantity>40</InStockSupplyQuantity> "
strXML = strXML & " <ASIN>B01BVMUHM8</ASIN> "
strXML = strXML & " <SellerSKU>dsx-90 fba</SellerSKU> "
strXML = strXML & " </member> "
strXML = strXML & " </InventorySupplyList> "
strXML = strXML & " </ListInventorySupplyResult> "
strXML = strXML & " <ResponseMetadata> "
strXML = strXML & " <RequestId>8a7d1832-b271-4171-991f-7e39feee5bf1</RequestId> "
strXML = strXML & " </ResponseMetadata> "
strXML = strXML & "</ListInventorySupplyResponse> "
strXML = strXML & ""
objxmldoc.loadXML (strXML)
Set SKUCount = objxmldoc.selectNodes("//ListInventorySupplyResponse/ListInventorySupplyResult/InventorySupplyList/member")
' Get the number of "member" nodes:
Debug.Print "Total member nodes : " & SKUCount.length
count = 0
For Each node In SKUCount
' Loop on "Member" nodes
count = count + 1
Next
Debug.Print "Loop count = " & count
End Sub
outputs :
输出：
Total member nodes : 2 Loop count = 2
Total member nodes : 2 Loop count = 2
回答by trincot
When using selectNodesyou need to specify the XPath with the nodes names as they occur in the document, so without the ns1:prefix:
使用时，selectNodes您需要使用节点名称指定 XPath，因为它们出现在文档中，因此没有ns1:前缀：
Set SKUCount = objxmldoc.selectNodes("//InventorySupplyList/member")
Then the loop will work and return a count of 2.
然后循环将工作并返回 2 的计数。
If you want to get the count without the loop, you can use the lengthproperty:
如果要在没有循环的情况下获取计数，可以使用以下length属性：
Debug.Print SKUCount.length
