Parsing xml document into table

[Tarnish]

Hi all,

I'm looping through a folder and pulling xml files (one at a time, obviously) into a variable in a procedure in vba. I've tried importing the xml files but each file produces multiple tables and the tables simply don't work well together. I've also tried applying a transformation file and that doesn't seem to fix the table issue, either, though I don't really know what my goal is with the transformation. I can create the table easily in terms of using a transformation to turn the xml into a nice html table, but that table does not show up when I import it.

So I pull all the text from one of these files into a variable, and I need to go through the text systematically and pull out pieces to form a table record and append that record to (most likely) an existing table. In most cases there will be multiple records pulled out of each file.

I have searched for pieces of information in the past in vba using string functions and pulled that out without any problem, but the difference here is I need pull text from between an opening and closing xml tags, save it to a variable, then move forward in the text file and go to the next place and pull that data out (again, data between opening and closing xml tags).

Can someone give me an idea of what to use to work with a text file in this manner? I've done perl to create rss feeds from web pages in the past and it seems like that's the kind of process I need, I just don't know what to use in vba.

Thanks for any help,
T

 

[PHV]

Have a look here:

http://msdn.microsoft.com/en-us/library/ms764730(VS.85).aspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vbajock]

And here:

http://msdn.microsoft.com/en-us/library/ms763742(v=VS.85).aspx

I used to use it all the time when I was supporting an online trading app that interfaced to Amazon, it looks complex at first, but once you get the hang of the DOM, you can do anything with XML.

 

[Tarnish]

-Thanks for the replies.

When I import the actual xml file, access (2003) ends up making like 5 or 6 tables out of it and the tables don't seem to relate to each other very well in terms of me being able to look at them and see any common field I could use as a pk.

I'll look at the suggestions. I have a feeling it probably will be easier for me to just convert the xml files to a text extension and import them as text and try to loop through them and get the info I need...

Thanks again,
T