如何在VBA中解析XML并检索特定值 [英] How to parse XML in VBA and retrieve specific values

问题描述

我已经花了两个星期的时间，未能成功地搜索如何解析一种特定的XML并仅获取少量值.我已经尝试了Internet上的每一个代码，直到找到可以解决部分问题的代码.

I've already spent two weeks searching unsuccessfully how to parse one specific XML and fetch just few values. I already tried every single code on internet until I found one that solved part of my problem.

我要从美国农业部获取的XML是免费的.

The XML i'm trying to fetch it's from U.S Department of Agriculture, and is free to access.

https://apps.fas.usda.gov/psdonline/app/index.html#/app/about

    Dim xmlDoc As MSXML2.DOMDocument60
    Dim xmlNode As MSXML2.IXMLDOMNode
    Dim xmlNodeList As MSXML2.IXMLDOMNodeList
    Dim myNode As MSXML2.IXMLDOMNode

    Dim URL As String, APIkey As String

    APIkey = "8DB688F8-1E22-4031-B581-59C221ECDDA6"

    URL = "https://apps.fas.usda.gov/PSDOnlineDataServices/api/CommodityData/GetCommodityDataByYear?commodityCode=2222000&marketYear=2018"

    Set xmlDoc = New MSXML2.DOMDocument60
    xmlDoc.async = False

    With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", URL, False
    .SetRequestHeader "Accept", "text/xml"
    .SetRequestHeader "API_KEY", APIkey
    .Send
    xmlDoc.loadXML .ResponseText
End With

Set xmlNodeList = xmlDoc.getElementsByTagName("*")
    For Each xmlNode In xmlNodeList
        For Each myNode In xmlNode.childNodes
          If myNode.nodeType = NODE_TEXT Then
            Debug.Print xmlNode.nodeName & "=" & xmlNode.text
          End If
        Next myNode
    Next xmlNode
    Set xmlDoc = Nothing
End Sub

此代码的响应显示了列出的整个XML，但是当我尝试找到一个特定的节点时，代码结果就什么也没有.

The response of this code show the entire XML listed, but when I try to find one specific node, the code result it's nothing.

在

Set xmlNodeList = xmlDoc.getElementsByTagName("*")

我尝试使用地址"//AttributeDescription"，但显然只使用"*"即可.

I've tried to use the address "//AttributeDescription", but apparently just work using the "*".

例如，我需要收到以下回复:

I need to receive, for example, The response below:

AttributeDescription =生产

AttributeDescription=Production

CountryName =巴西

CountryName=Brazil

值= 0.00000

Value=0.00000

我尽了最大的努力来获得正确的响应，并且我还认为XML结构的格式不正确，原因是寻址时缺少响应...

I did my best trying to get the right response and I also consider that the XML structure it's not in the right format due the lack of response when addressing...

有什么我可以解决的问题吗?

Is there anything that I can do to solve this issue?

推荐答案

这里有两个独立的问题.

There are two separate issues here.

当XML文档具有默认名称空间时，MSXML2在使用XPath时会出现问题-请参阅此处以获取详细信息.在从USDA网站下载的文档的开头，有一些名称空间声明:

MSXML2 has issues using XPath when the XML document has a default namespace - see here for details. At the start of the downloaded document from the USDA site, there are some namespace declarations:

<ArrayOfCommodityData xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/PSDOnline.DataServices.Models">

这里声明了两个名称空间.一个带有前缀 i 的默认名称空间，该名称空间覆盖没有名称空间前缀的任何元素.如果您查看XML文档中的"CalendarYear"条目-< CalendarYear i:nil ="true"/> -，则可以看到"CalendarYear"位于默认名称空间中，而"nil"位于"i"命名空间中.

There are two namespaces declared here. One with the prefix i and a default namespace that covers any element which does not have a namespace prefix. If you look at a "CalendarYear" entry in the XML document - <CalendarYear i:nil="true" /> - then you can see that "CalendarYear" is in the default namespace whereas "nil" is in the "i" namespace.

要使MSXML2与默认名称空间一起使用，必须声明一个与默认名称空间具有相同URI的名称空间.这是使用XML文档的SelectionNamespaces属性完成的，如下所示:

To make MSXML2 work with default namespaces, you have to declare a namespace which has the same URI as the default namespace. This is done using the SelectionNamespaces property of the XML document, like this:

xmlDoc.SetProperty "SelectionNamespaces", "xmlns:r='http://schemas.datacontract.org/2004/07/PSDOnline.DataServices.Models'"

我选择 r 作为命名空间，但您选择的名称无关紧要-它必须与文档中的任何其他命名空间不同.

I chose r as the namespace but the name you choose is irrelevant - it just has to be different from any other namespaces in the document.

这导致了第二个问题.您正在使用 getElementsByTagName ，它仅将标签名称作为参数，但是您正在传递XPath字符串.要处理XPath字符串，您需要使用 SelectNodes 代替，并且需要使用我们添加的名称空间，如下所示:

This leads on to the second problem. You are using getElementsByTagName which just takes a tag name as a parameter but you are passing in an XPath string. To deal with an XPath string, you need to use SelectNodes instead and you need to use the namespace we added, like this:

Set xmlNodeList = xmlDoc.SelectNodes("//r:AttributeDescription")

这篇关于如何在VBA中解析XML并检索特定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！