Store XML Data into Access DB

[R1972]

Hi,

We are having around 2000 XML files, which contains full of valid information. We want to extract the data from XML file to Excel sheet or store into Access database.

Your suggesstions would be appreciated.

Thanks,
Raj

 

[rdroske]

What you want to do is add a reference to the MS XML Objects (3.0 or 4.0) to you Project. If you don't see it in
the list search MS for XML Parser.

Adds a bunch of objects you can DIM as New. Main ones are

Dim XMLDoc As MSXML2.DOMDocument30
Dim XMLElement As MSXML2.IXMLDOMElement

You can load the whole XML file into a Doc with

XMLDoc.async = False
XMLDoc.Load "C:\xmlfilename"


Reads the whole XML file into a tree structure of "Nodes"
which you can then walk through extracting whatever data you need.

Start with

Set XMLElement = XMLDoc.documentElement which is the root element

and the rest is a childnode of that or a child node of a childnode etc depending on the number of levels in the file.

just view an element in the watch window to see where the attributes and everything are.

 

[R1972]

Thanks for your useful hints.

Raj