Importing XML records individually

[Guest]

Hi,
Is there a way to import data one record at a time from an XML file? I need
to read in the data for one record, perform a calculation, then read in the
next record and perform a calculation and repeat this for each record.
Regards and thanks,
Mark

 

[Chip Pearson]

It may not be terribly efficient to process the records one by one, but you
could try something like the following. It requires a reference to MSXML2.

Dim DOM As MSXML2.DOMDocument60
Dim Nodes As MSXML2.IXMLDOMNodeList
Dim Node As MSXML2.IXMLDOMNode
Dim XPath As String

Set DOM = New MSXML2.DOMDocument60
With DOM
.setProperty "SelectionLanguage", "XPath"
.validateOnParse = True
.Load "C:\Test.xml"
With .parseError
If .errorCode <> 0 Then
MsgBox "Invalid XML: " & vbCrLf & _
"Error: " & .reason & " on line: " & .Line
Exit Sub
End If
End With
XPath = "/Records/Record"
Set Nodes = .selectNodes(XPath)
For Each Node In Nodes
' calculate with node
Debug.Print Node.childNodes(0).nodeTypedValue
Next Node
End With

This assumes a basic XML structure like

<Records>
<Record>
<Element>123</Element>
</Record>
<Record>
<Element>234</Element>
</Record>
</Records>


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

 

[Guest]

Gosh Chip, that was a fast response! Many thanks.

Unfortunately the schema is a bit mote complex than that. It contains
historical sales data for several days for each of a number of products.

Something like:

Product
- Description
Sales
- SomeData
- MoreData
- History
- Day
- Date
- QuantitySold

Each product has a single deccription and a single instance of each of
SomeData and MoreData, but the 'Day' data contains several dates and
respective sales quantites for each product (it is on these that I have to
perform the calculations for each product to calculate the total sales for
two time periods before bringing in the next product record)

For me a simple solution would be to map each historical day's sales
quantity into an individual list and then sum the sales quantities row by row
for each product for the required dates, but I am being coerced in to doing
it using the current schema structure.

Regards and thanks,

Mark