Need help reading XML Document and writing to DB

[Gumster]

Have this macro to read through the data, need to extend it a bit so it goes through the whole XMLfile and also outputs the data into an accessDB. Also would need the entry date to be put on each line instead of the null value. On the last row I'll provide a sample from the file, if you want to try the code you can just cut and paste and save it as C:\fileAsXML.xml
Any help greatly appreciated!


Sub ImportXML()
Dim xmlElement As MSXML2.IXMLDOMNode
Dim xmlNode As MSXML2.IXMLDOMNode
Dim childNode As MSXML2.IXMLDOMNode

Set domIn = New DOMDocument40
Set domOut = New DOMDocument40
domIn.async = False
If domIn.Load("C:\fileAsXML.xml") Then
Set xmlElement = domIn.childNodes(1).childNodes(0).childNodes(0)
Debug.Print xmlElement.Attributes(0).nodeValue
For Each xmlNode In xmlElement.childNodes
Debug.Print xmlElement.nodeValue, xmlNode.Attributes(0).nodeValue, xmlNode.Attributes(1).nodeValue
Next
End If
'Cleanup
Set domIn = Nothing
Set domOut = Nothing
Set domStylesheet = Nothing

' MsgBox "Done!"
End Sub



This is the sample XML:


<?xml version="1.0" ?>
- <curves title="Wind power output prognosis based on EC 12 and EC 12 Ens Mean for DK1 and DK2. MWh/h" phone="" updated="23/05/2005 00:45:57">
- <curve id="21865" title="PRO WND DK1 EC12 D1 F" type="forecast">
- <entry date="2005-01-01 00:00:00">
<point date="2005-01-01 00:00:00" value="736.32485617" />
<point date="2005-01-02 00:00:00" value="1780.443517074" />
<point date="2005-01-03 00:00:00" value="1720.361175887" />
<point date="2005-01-04 00:00:00" value="1388.030518839" />
<point date="2005-01-05 00:00:00" value="1251.817350687" />
<point date="2005-01-06 00:00:00" value="1504.235428622" />
<point date="2005-01-07 00:00:00" value="1555.934405103" />
<point date="2005-01-08 00:00:00" value="1753.155190667" />
<point date="2005-01-09 00:00:00" value="875.937421321" />
<point date="2005-01-10 00:00:00" value="297.084338682" />
</entry>
- <entry date="2005-01-02 00:00:00">
<point date="2005-01-02 00:00:00" value="1814.071772775" />
<point date="2005-01-03 00:00:00" value="1467.445247337" />
<point date="2005-01-04 00:00:00" value="1701.34184426" />
<point date="2005-01-05 00:00:00" value="1474.034630175" />
<point date="2005-01-06 00:00:00" value="1776.362718719" />
<point date="2005-01-07 00:00:00" value="1246.769483564" />
<point date="2005-01-08 00:00:00" value="1705.438341072" />
<point date="2005-01-09 00:00:00" value="1852.98240715" />
<point date="2005-01-10 00:00:00" value="976.256718497" />
<point date="2005-01-11 00:00:00" value="1518.111850689" />

 

[chiph]

What error are you getting?

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[CubeE101]

instead of
domIn.childNodes(1)

try:
domIn.DocumentElement
or
domIn.DocumentElement.childNodes(1)


domIn.childNodes(1) should not exist, if anything, it would be domIn.childNodes(0)

There is only one root element, and domIn is not the root... It is the document in whole

Hope This Helps

Visit My Site
PROGRAMMER: Red-eyed, mumbling mammal capable of conversing with inanimate objects.

 

[Gumster]

Thanks for the replies! What I need is to step through the values and write to a access database so that it looks like this. Put the Entry Date on each post and then select only these values that are relevant. The file I am trying to import is much larger than this sample file and contains three series of data. How do I step through the whole file and how do I write to access tables from a VB macro in access?
Thanks!

Entry Date Point Date Value
2005-01-11 00:00:00 2005-01-01 00:00 1518.111850689
2005-01-11 00:00:00 2005-01-02 00:00 1518.111850690
2005-01-11 00:00:00 2005-01-03 00:00 1518.111850691
2005-01-11 00:00:00 2005-01-04 00:00 1518.111850692
2005-01-11 00:00:00 2005-01-05 00:00 1518.111850693
2005-01-11 00:00:00 2005-01-06 00:00 1518.111850694
2005-01-11 00:00:00 2005-01-07 00:00 1518.111850695
2005-01-11 00:00:00 2005-01-08 00:00 1518.111850696
2005-01-11 00:00:00 2005-01-09 00:00 1518.111850697
2005-01-11 00:00:00 2005-01-10 00:00 1518.111850698