3

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>

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

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

Thanks !

EDIT: I am using Microsoft Access 2002 SP3

Improve this question
3
  • Which version of MS Access are you using ( msdn.microsoft.com/en-us/library/ff823157.aspx ) ? Commented Jul 16, 2012 at 14:42
  • I am using microsoft access 2002 SP3. I tried the importXML method however I really disliked how it would make a seperate table for each of my level 2 data. All the data needs to be in the same table. Also, I will have to control the name of the columns where the data goes at some point in the future. Commented Jul 16, 2012 at 14:47
  • Can you show the expected output? Commented Jul 16, 2012 at 18:06

1 Answer 1

Reset to default
5

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.

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

is actually both not necessary and a bad idea, since you do never seem to check the value of fSuccess). Better:

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

  • 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.
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

The solution seems to work, however it reads only one eval_set and the first element does not appear (eval_id). It shows " : 1156687.44" instead of "eval_id : 1156687.44". I replaced the 2nd part of the string with .NodeTypedValue to get the value of the element
@Estarius: nodeTypedValue only has a use when you supply an XML Schema that defines the data types. Don't use it if you have no schema. Before we go on, please confirm that you don't have On Error Goto 0 in effect.
I don't have an On Error Goto yet... What should I use instead of nodeTypedValue ? Because the .length in the code isn't really the information I am looking for since it only returns well ... the length of the value
@Estarius Good. -- I'd recommend using .text. The .length is of course the number of child nodes and was meant to serve as a placeholder for whatever you really wanted to do. It's a good idea to set a breakpoint in the code and step through line-by-line in the debugger. Inspect all the objects you have, their properties, and so on. Also, have you switched to XPath or are you still fiddling with childNodes?
I am still using the childNodes, considering there are hundreds of attributes and XPath seems to require me to specify each attribute... It would be great to make the real table, but very heavy to simply get all the data of the XML into a table
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.