解析由VBA XML文件 [英] Parse XML File with VBA

问题描述

我有一个XML文件，其结构类似于此：

I have a XML file with a structure similar to this:

<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>

我需要解析完整的文件，并把它放在一个表。 （注：eval_d和eval_e实际上有一百多各属性）。我试着用MSXML2然而，我被卡住，当我尝试解析文件。通过使用答案在如何填写Flash XML在VBA 和的 http://stackoverflow.com/questions/7271075/parse-xml-in-vba">Parse XML我能到那里：

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 VBA and Parse XML in VBA I was able to get there :

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

不过，不是给我正确的价值观，它给了我在eval_id 4倍的浮动...

However, instead of giving me the right values, it gives me the float in eval_id 4 times...

谢谢！

编辑：我使用Microsoft Access 2002 SP3

I am using Microsoft Access 2002 SP3

推荐答案

您的循环是完全错误的。不要使用计数循环。有对于每个这将不正是你所需要的，而且它更可读了。

Your loop is all wrong. Don't use a counted loop. There is For Each which will do exactly what you need, and it's much more readable, too.

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 childNodes you must check the nodeType property. Comments, text nodes and so on will all be in the list of child nodes, not just element nodes.

这可能是考虑使用XPath来选择你的元素是个好主意。与DOM方法这样做很容易出错和繁琐。读了<一href="http://msdn.microsoft.com/en-us/library/windows/desktop/ms754523%28v=vs.85%29.aspx"><$c$c>IXMLDOMNode::selectNodes和<一href="http://msdn.microsoft.com/en-us/library/windows/desktop/ms757846%28v=vs.85%29.aspx"><$c$c>IXMLDOMNode::selectSingleNode.

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::selectNodes and 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")

实际上既是没有必要的，一个坏主意，因为你永远似乎并检查 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

• 使错误处理变得更加容易，因为每个功能只有一个目的。
• 您将需要较少的变量，因为你可以在函数参数定义了一些变量
• code将变得更容易维护，因为它更明显的是什么3短函数的功能比一个长函数的功能。

这篇关于解析由VBA XML文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！