Need Help Parsing XML File with VBA???

I have the following file that I'm trying to setup a little parsing routine to extract out the vehicle acknowledgment when a text message is sent to it.
This is a sample of the XML file and it can can contain as many as 50 messages but I limited it to (2) for this question.  Basically I need and example of how to loop through the <OUTBOUND_MESSAGE_ACKNOWLEDGEMENT> element for each message and get the vehicle_label, send_status, message_date, message_time, orig_outbound_message and turn_around.   Also, the first message has the orig_outbound_message element and the second one does not.  The second message has the turn_around element and the second first one does not.  This was sample data but I think the actual data will have the same elements in all messages.

The code below that I have thus far never finds any child nodes and that's kind of where I am now.

Function ParseXML2()
    Dim xmlDoc As New MSXML2.DOMDocument
    Dim xmlNode As MSXML2.IXMLDOMElement
 
    If Not xmlDoc.Load("C:\Temp\MsgStatusResponse.xml") Then
        Err.Raise xmlDoc.parseError.errorCode, , xmlDoc.parseError.reason
    End If
   
    For Each xmlNode In xmlDoc.childNodes(0).childNodes
        Debug.Print xmlNode.nodeName  
    Next xmlNode
 
End Function



<?xml version="1.0" encoding="utf-8"?>
<SEND_MESSAGES_STATUS>
 
  <RESPONSE_STATUS>
    <CODE>1</CODE>
    <DESCRIPTION>SUCCESS</DESCRIPTION>
    <MORE_DATA>NO</MORE_DATA>
  </RESPONSE_STATUS>

  <OUTBOUND_MESSAGE_ACKNOWLEDGEMENT>
    <VEHICLE_LABEL>MR31</VEHICLE_LABEL>
    <SEND_STATUS>ACK</SEND_STATUS>
    <MESSAGE_TIMESTAMP>
      <MESSAGE_DATE>O4/O1/2OOO</MESSAGE_DATE>
      <MESSAGE_TlME>05:04:34pm</MESSAGE_TlME>
      <MESSAGE_TIMEZONE>PDT</MESSAGE_TIMEZONE>
    </MESSAGE_TIMESTAMP>
    <ORIG_OUTBOUND_MESSAGE>TEST...</ORIG_OUTBOUND_MESSAGE>
  </OUTBOUND_MESSAGE_ACKNOWLEDGEMENT>

  <OUTBOUND_MESSAGE_ACKNOWLEDGEMENT>
    <VEHICLE_LABEL>MKTQA</VEHICLE_LABEL>
    <SEND_STATUS>NAK</SEND_STATUS>
    <MESSAGE_TIMESTAMP>
      <MESSAGE_DATE>04/1O/2OOO</MESSAGE_DATE>
      <MESSAGE_TlME>05:04:34pm</MESSAGE_TlME>
      <MESSAGE_TIMEZONE>PDT</MESSAGE_TIMEZONE>
    </MESSAGE_TIMESTAMP>
    <TURN_AROUND>81654</TURN_AROUND>
  </OUTBOUND_MESSAGE_ACKNOWLEDGEMENT>
</SEND_MESSAGES_STATUS>


Thanks,

ET

Start Free Trial