CODEWITHSUNDEEP
xmlms-accessvba
Leehbi
Leehbi

Reputation: 779

Extract data from XML by iterating

I have an XML string that contains data I need to write to a log table.

The XML looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<response success="true">
  <messages>
    <message key="warning-unmapped-item">Message 1</message>
    <message key="warning-unmapped-org">Message 2</message>
  </messages>

I need the information in the "response success" tag and each of the message keys/Text

I've been able to load the XML to a dom and get some of the data but not all of it. :

Dim Variables As IXMLDOMNodeList
Dim variable As IXMLDOMNode
Dim log As MSXML2.DOMDocument

Set log = New MSXML2.DOMDocument
log.loadXML (message)

Set Variables = log.selectNodes("//messages")
For Each variable In Variables
    Debug.Print variable.selectNodes("message").Item(0).Text
    Debug.Print variable.selectNodes("message").Item(1).Text
Next

I can get the first two messages using the above syntax but I'd rather iterate through the messages rather than refer to them literally as I don't know how many messages there will be. Also, I need the keys as well as text. Is there a better way to do this?

Upvotes: 1

Views: 46

Answers (1)

William Walseth
William Walseth

Reputation: 2923

You're really pretty close already, just a couple bits to add.

  1. To get all the message elements, select them directly with selectNodes( "//messages" ), or /messages/message
  2. Iterate the results as follows

  3. Use the ndMessage(i).getAttribute( key) to get the key attribute.

    strXPath = "//messages/message" Set ndMessages = xmlRates.selectNodes(strXPath)

    For i = 0 To ndMessages.Length - 1
  Debug.Print ndMessages(i).Text & " key:" & ndMessages(i).getAttribute( "key" )
Next i

Upvotes: 2

Related Questions