Importing XML (by batch) into Access database

[l]

Hi,
I am needing to import batches of XML files into an Access database.
Is there a way to do this through VBA macros?

Thanks for your help,
Louis

 

[Ron2006]

The following code will download and save XML from the web....

==============================
fh = FreeFile

Set msXML = CreateObject("Microsoft.XMLHTTP")
msXML.Open "GET", strWebsite, False
msXML.SetRequestHeader "Content-type", "text/xml"
msXML.send
strpageContent = msXML.responseText

Open "c:\OSdrill.txt" For Output As #FreeFile
Print #fh, strpageContent


Set msXML = Nothing
Close #fh
=============================

and then

docmd.transfertext acimporthtml etc.

will import html table information into access.

=============================

Ron

 

[l]

Thanks Ron,
Would this also work on multiple XML documents in a local folder?

 

[Ron2006]

As far as I know you cannot concatenate the files. I believe it
basically will load 1 table per file. I practiced first just doing it
manually to see what it would do. In this case this was the end of a
larger process. I had to download about 100 XML pages. However when I
looked inside I found that the data I wanted was the 7th table on each
of those pages. What I ended up doing was using vba to open my
sequentially named files one at a time and reading through each until I
got to the 7th <table> then writing each line in that file until I got
to the </table>. But I did not include those two identifiers. My output
was a larger file that contained the guts of each of the about 20 input
xml files. (I supplied the leading <table> and trailing </table>.) Then
I used the importxml to import that larger table. Actually that whole
process went a lot faster than I thought it would.

If the table within the xml has a name associated with it (I am not
sure of the format) then you may be able to be selective as to what is
imported if there is more than one table in the document.

Hope this helps.

Ron

 

[l]

Ron,
Thanks for all your help. I'll give it a try.

Regards,
Louis