2

Possible Duplicate:
MS Access XMLImport from text or stream instead of file?

This seems like such a simple thing to do (at least it is in Excel), but how does one import data from an xml source on the web into Access? I've done several searches and I'm not getting anywhere, so I'm starting to think it can't be done, but maybe I'm not asking the right question. Any help is appreciated.

Community
  • 1
  • 1
Craig Stanley
  • 75
  • 3
  • 7
  • not sure why `Application.ImportXML DataSource:="MyURL", ImportOptions:=STRUCTURE_AND_DATA` wouldn't work. I would test it but I don't have a url to try... do you have a url i could play with? – Pynner Dec 18 '12 at 02:30
  • Try this one: http://w1.weather.gov/xml/current_obs/KSMF.xml – Craig Stanley Dec 18 '12 at 05:49

1 Answers1

2

If you save the XML as a local file, you can use Access' Application.ImportXML Method. Unfortunately, it won't allow you to import directly from a URL for a remote XML source. That point was addressed in a previous Stack Overflow question: MS Access XMLImport from text or stream instead of file? AFAIK, nobody has found a way to do it.

Another pitfall is that ImportXML fails with complex XML schemas; try yours to find out whether it's accepted.

Alternatively, you can open the XML as a MSXML DOMDocument and use its methods to extract only the values you want, then store them in your table. That approach does work with either a URL or a local file. You can find some tips for that approach at this SO answer: https://stackoverflow.com/a/3398999/77335

Community
  • 1
  • 1
HansUp
  • 95,961
  • 11
  • 77
  • 135