I need to retrieve data from a website in XML format and save it in an Access database. While I'm able to pull the data successfully, I'm having problems parsing it. Specifically, I only seem to be able to read the first record returned, and don't know how to cycle through the remaining records.
Here's an example of what the source XML looks like. This is an employment history for a specific individual, so the names, titles and date fields will change with each record. In this example, this individual has 7 employment records available, each with a different company.
<DataRow>
<DataItem name="Symbol">00123S-E</DataItem>
<DataItem name="Company ID">061ABC-E</DataItem>
<DataItem name="Company Ticker">@NA</DataItem>
<DataItem name="Company Name">L.L. Bean, Inc.</DataItem>
<DataItem name="Title">Independent Director</DataItem>
<DataItem name="Function Code">IND</DataItem>
<DataItem name="Function Description">Independent Dir/Board Member</DataItem>
<DataItem name="Start Date">20140508</DataItem>
<DataItem name="End Date">@NA</DataItem>
</DataRow>
And here's the Access VBA code that I thought would work:
Private Sub bNewFetch_Click()
Dim xmldoc As MSXML2.DOMDocument
Dim xmlElement As MSXML2.IXMLDOMElement
Dim xmlSelection As MSXML2.IXMLDOMSelection
Dim myLineItem As Object
Set xmldoc = New MSXML2.DOMDocument
xmldoc.async = False
xmldoc.Load [Here's where the URL goes]
Set xmlSelection = xmldoc.selectNodes("//DataRow")
For Each xmlElement In xmlSelection
Set myLineItem = xmldoc.selectNodes("//DataRow/DataItem")
Debug.Print myLineItem(3).Text
Next xmlElement
End Sub
Unfortunately, instead of returning the names of the 7 different companies this individual worked for, the code is simply returning "L.L. Bean, Inc." 7 times. It seems to know there are 7 records, but instead of stepping through those records and returning the relevant Company Name for each, it's just repeating the Company Name from the first record.
Can anyone advise on what I need to do to retrieve all data from each returned record, and not just the first?
Thanks in advance for any suggestions.
SOLUTION:
Thanks to BankBuilder for pointing out the problem with my retrieval loop. The solution was to replace the last For/Next statement with:
For Each xmlElement In xmlSelection
Debug.print xmlElement.selectSingleNode("DataItem[@name='Company Name']").Text
Next xmlElement
myLineItem(0).Textis Symbol.myLineItem(1).Textis Company ID.myLineItem(2).Textis Company ID. What do you thinkmyLineItem(3).Textshould be?