Import XML into a table?

chris-uk-lad

Registered User.
Local time
Today, 03:09
Joined
Jul 8, 2008
Messages
271
Hi all,

I have an XML document containing records i want in a table within an Access databse. As far as im aware i cannot do this with the normal DoCmd.Transfer in Access 2000.

Does anyone know a method to import an XML document into an Access table?

Im then wanting to manipulate the table and output again as a text document, something i can fashion myself.

Thanks
 
how is it saved? data? a spreadsheet? what program do you view it with?
 
it is saved as an .XML viewed via a internet browser as default
 
look at

microsoft dom model

this is a link, although its not showing up properly

msdn.microsoft.com/en-us/library/ms766487(VS.85).aspx


---------
the dom document functionality is either an existing reference in vba, or needs downloading and installing - i forget which.

--------
i think there is an intrinsic import xml facility in access - (try transfertext, with acimportxml parameter) - its just that in my experience it didnt help/didnt work as i expected
 
Last edited:
I received a few errors while trying to contrust a DOM method but while debugging it dawned on me that it may be possible to read in the whole document as a long string and cut it after certain tags?

So if i had <tag1>AA</tag1><tag2>BB</tag2><tag3>CC</tag3><tag1>AA</tag1>
I could read in the line as a string (opening the xml as a file in notepad shows it as a continuous line of text) and cutting up the tags and restarting a DO method after each </tag3>

Just means trying to code all that lol, ill try both methods anyway :S
 
Here is a GENERAL code to tranfer XML files:

Create a button and put this code:

Const acAppendData = 2

Application.ImportXML GIVE_THE_LINK_FOR_YOUR_XML, acAppendData

MsgBox "Procedure Complete.", vbInformation, "Your DB Name"
 
chris

editolis suiggested using importxml - as i say in my experience, this doesnt do it right

since an xml is designed recursively, it actually makes sense to walk the tree recursively. the dom model actually treats the xml file as a tree of nodes

so effectively you can walk a tree by this sort of code - i actually posted a proper version of this to the code samples, but it isnt there yet. Hope this makes sense.

Code:
sub treewalk(thisnode as node)
if thisnode.haschildren then
  for each childnode
      treewalk(childnode)
  next
else
  'this is a leafnode
  'so process thisnode.xml in some way
end if
end sub
 
Hello chris-uk-la! I'm setting up an information database about plants, so I have a bunch of XML files that I will use for a web site and I created an application in MS Access 97 to manipulate the XML Files. So I have some functions I have written to make my life easier, and I hope you will find usefull. It's not so fancy stuff because I have done it in a hurry, I have so much work with the website.
I don't know exactly what you want, so I will send you the module I have written in a private msg. And if you have any question you can always, post back or send me a private message, as you wish.
The name of the functions are quite descriptive. You will see there:
Function Get_XML_Node(File As String, Node As String),
Public Sub Modify_XML_Node(File As String, Node As String, NewText As Variant),
Public Sub Create_XML_Node(File As String, Node As String, root As String, Caption As String, Text),
Public Sub Delete_XML_Node(File As String, Node As String),
Public Function NodeExists(File As String, Node As String) As Boolean,
........And so on.
There might be some bugs, but you should be able to figure out the way I'm manipulating the XML files.
I hope it will help you. Cheers!!
 

Users who are viewing this thread

Back
Top Bottom