如何遍历XML节点并验证值是否存在? [英] How to loop through XML-nodes and validate if values exists?

问题描述

我已经通过API以XML的形式获取了数据，并且希望循环遍历节点(有几种相同类型的节点)并将其添加到某些字段/表中.

I have through an API fetched my data as an XML, and I wish to cycle through nodes (there are several of the same type) and add them to certain fields/a table.

来自XML文件的示例:

Example from the XML-file:

<HistRating
  xmlns="">
  <EndrAr>2020</EndrAr>
  <EndrMnd>7</EndrMnd>
  <Rating>A</Rating>
</HistRating>
<HistRating
  xmlns="">
  <EndrAr>2019</EndrAr>
  <EndrMnd>6</EndrMnd>
  <Rating>A</Rating>
</HistRating>

我尝试了以下格式(这时我需要的XML在xmlDoc xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")中的字符串中.并不是一种真正的性感"方式来编写它，但是我在这款游戏中是新手:

I have tried the following format (at this point the XML I need is in a string in xmlDoc xmlDoc = CreateObject("MSXML2.DOMDocument.6.0"). Fully aware that this is not a really "sexy" way to write it, but I'm new at this game:

Set nodeXML = xmlDoc.getElementsByTagName("EndrAr")
Range("G1").Value = nodeXML(1).Text
Range("H1").Value = nodeXML(2).Text
Range("I1").Value = nodeXML(3).Text

Set nodeXML = xmlDoc.getElementsByTagName("EndrMnd")
Range("G2").Value = nodeXML(1).Text
Range("H2").Value = nodeXML(2).Text
Range("I2").Value = nodeXML(3).Text

Set nodeXML = xmlDoc.getElementsByTagName("Rating")
Range("G3").Value = nodeXML(1).Text
Range("H3").Value = nodeXML(2).Text
Range("I3").Value = nodeXML(3).Text

只要所有三个项目都在那里，此方法就很好用.不幸的是没有给出.如果是新公司，即(3)将不存在(上面每年有一行)，我想将单元格设置为Blank或 No value 或其他任何内容.

This works great as long as all three items are there. Unfortunately that is not given. If it is a new company i.e. (3) wont exist (there is one line per year above), and I would like to either set the cell to Blank or No value or something.

运行上述代码时的结果:

The result from when I run the above code:

但是，如果我尝试添加第4行以测试如果值不存在会发生什么，我将得到以下信息(出于明显的原因)

But if I try to add a line 4 to test what happens if value does not exists I get the following (for obvious reasons)

我希望获得帮助:

• 我可以说些魔术"吗?添加 ifmissing (尝试过，但是无法正常工作)吗?
• 其他添加如果找不到变量的方法，请在单元格中输入以下内容
• 还是我应该解决这个问题的完全不同的方式?

• Can I by some "magic" add a ifmissing (tried it, but could not get it to work)?
• Other ways to add a if variable is not found, input following into cell
• Or are there a complete different way I should have solved this?

这是从30个节点中添加最近的 X 个可用年份(其中X为4，或者小于4则更少)的会计数据.

This is to add accounting data from last X available years (where X is ie 4, or less if not 4 is available) from 30 nodes.

推荐答案

首先查询所有 HistRating 元素，然后遍历该集合:

Start by querying all of the HistRating elements, then loop over that collection:

Const MAX_YEARS As Long = 4
Dim ratings, rating, c As Range, i as Long

Set c= Range("A1")
Set ratings = xmlDoc.getElementsByTagName("HistRating")
For Each rating in ratings
    c.offset(0, i) = rating.getElementsByTagName("EndrAr")(0).Text
    c.offset(1, i) = rating.getElementsByTagName("EndrMnd")(0).Text
    c.offset(2, i) = rating.getElementsByTagName("Rating")(0).Text
    i = i + 1
    If i >= MAX_YEARS Then Exit For 'exit if processed enough nodes
Next rating

这篇关于如何遍历XML节点并验证值是否存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！