使用 VBA 解析 XML 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11506651/
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
Parse XML File with VBA
提问by Estarius
I have a XML file with a structure similar to this:
我有一个结构与此类似的 XML 文件：
<egh_eval>
<eval_set>
<eval_id>FLOAT</eval_id>
<eval_d>
<height>INT</height>
<weight>INT</weight>
</eval_d>
<eval_e>
<height>INT</height>
<weight>INT</weight>
</eval_e>
<eval_cred>
<credit>FLOAT</credit>
</eval_cred>
</eval_set>
I need to parse the complete file and put it in a table. (Note: eval_d and eval_e actually have more than a hundred attributes each). I tried using MSXML2 however I get stuck when I try to parse the file. By using the answers at How to pase XML in VBAand Parse XML in VBAI was able to get there :
我需要解析完整的文件并将其放入表格中。（注意：eval_d 和 eval_e 实际上每个都有一百多个属性）。我尝试使用 MSXML2，但是当我尝试解析文件时卡住了。通过使用How to pase XML in VBA和Parse XML in VBA 中的答案，我能够到达那里：
Dim fSuccess As Boolean
Dim oDoc As MSXML2.DOMDocument
Dim oRoot As MSXML2.IXMLDOMNode ' Level 0 egh_eval
Dim oChild As MSXML2.IXMLDOMNode ' Level 1 eval_set
Dim oChildren As MSXML2.IXMLDOMNode ' Level 2 eval_id, eval_d, eval_e, eval_cred
Dim domList As MSXML2.IXMLDOMNodeList
Set oDoc = New MSXML2.DOMDocument
oDoc.async = False
oDoc.validateOnParse = False
fSuccess = oDoc.Load(Application.CurrentProject.Path & "\file.xml")
Set oRoot = oDoc.documentElement
Set oChild = oRoot.childNodes(0)
Set oChildren = oChild.childNodes(0)
For i = 0 To oChild.childNodes.length - 1
For y = 0 To oChildren.childNodes.length - 1
MsgBox oChildren.nodeName & " : " & oChildren.nodeTypedValue
oChildren.childNodes.nextNode
Next
oChild.childNodes.nextNode
Next
However, instead of giving me the right values, it gives me the float in eval_id 4 times...
但是，它没有给我正确的值，而是给了我 eval_id 中的浮点数 4 次...
Thanks !
谢谢 ！
EDIT: I am using Microsoft Access 2002 SP3
编辑：我使用的是 Microsoft Access 2002 SP3
回答by Tomalak
Your loop is all wrong. Don't use a counted loop. There is For Eachwhich will do exactly what you need, and it's much more readable, too.
你的循环全错了。不要使用计数循环。有For Each哪些可以完全满足您的需求，而且可读性也更高。
Dim egh_eval As MSXML2.IXMLDOMNode
Dim eval_set As MSXML2.IXMLDOMNode
Dim eval_prop As MSXML2.IXMLDOMNode
Set egh_eval = oDoc.documentElement.childNodes(0)
For Each eval_set In egh_eval.childNodes
If eval_set.nodeType = NODE_ELEMENT Then
For Each eval_prop In eval_set.childNodes
If eval_prop.nodeType = NODE_ELEMENT Then
MsgBox eval_prop.nodeName & " : " & eval_prop.childNodes.length
End If
Next eval_prop
End If
Next eval_set
When you use childNodesyou must check the nodeTypeproperty. Comments, text nodes and so on will all be in the list of child nodes, not just element nodes.
当您使用时，childNodes您必须检查nodeType属性。注释、文本节点等都将在子节点列表中，而不仅仅是元素节点。
It might be a good idea to look into using XPath to select your elements. Doing this with DOM methods is error-prone and cumbersome. Read up on IXMLDOMNode::selectNodesand IXMLDOMNode::selectSingleNode.
考虑使用 XPath 来选择元素可能是个好主意。使用 DOM 方法执行此操作容易出错且麻烦。继续阅读IXMLDOMNode::selectNodes和IXMLDOMNode::selectSingleNode。
For Each eval_set In oDoc.selectNodes("/egh_eval/eval_set")
Set eval_id = eval_set.selectSingleNode("eval_id")
' always check for empty search result!
If Not eval_id Is Nothing Then
MsgBox eval_id.text
' ...
End If
Next eval_set
Also, on a general note. This:
此外，在一般情况下。这个：
fSuccess = oDoc.Load(Application.CurrentProject.Path & "\file.xml")
is actually both not necessary and a bad idea, since you do never seem to check the value of fSuccess). Better:
实际上这既不必要又是一个坏主意，因为您似乎从未检查过fSuccess)的值。更好的：
Sub LoadAndProcessXml(path As String)
Dim oDoc As MSXML2.DOMDocument
If oDoc.Load(path) Then
ProcessXmlFile oDoc
Else
' error handling
End If
End Sub
Sub ProcessXml(doc As MSXML2.DOMDocument)
' Process the contents like shown above
End Sub
Creating multiple subs/functions has several advantages
创建多个 subs/functions 有几个优点
- Makes error handling much easier, since every function only has one purpose.
- You will need less variables, since you can define some variables in the function arguments
- Code will become more maintainable, since it's more obvious what 3 short functions do than what one long function does.
- 使错误处理更容易，因为每个函数只有一个目的。
- 您将需要更少的变量，因为您可以在函数参数中定义一些变量
- 代码将变得更易于维护，因为 3 个短函数的作用比一个长函数的作用更明显。
