Access 2003 XML Import Help (1 Viewer)

Hi,

I'm importing an XML file into Access 2003 to make a table but I'm having problems with the tables that are automatically being created.

The XML tags have this format:

When I import this XML file into my Database, it creates 2 tables:
Address -which only contains fields : ADDRESS1, City, PostalCode, and Country
Company - which contains fields: ID, Name, ProductList, and Status

The Problem:
What I want is to have all the fields in 1 table, OR the Address table to also have the ID so I know which company goes with which address!

I'm not too familiar with XMLs but I think I have to create a new Schema for it?

Edit: I'm importing this XML file through VBA code (planning on automating the import) and appending it onto another table. The reason I'm appending the data onto a table is because when I import, it makes the fields "Text" with a max character limit but I need some fields to be "Memo". The reason for this is so I don't get any Truncation Errors


Thanks for any help,
Muskilh

PS.
Sorry if this could have been posted in a better section

My current solution is to not let Access import the XML and create the tables, but instead I parse through the XML in VB. I'm making the records myself while getting the data from the XML tags (aka nodes).

This method takes way too long (about 20 minutes for 25,000 different nodes in the XML), if anyone has a better idea please let me know.

Thanks,

Muskilh

Perhaps your code is slow? You could import the file into a single field table and use a recordset to populate other tables.

Kicking this old post alive because i am struggling with the same problem.

Is your problem solved? How did you do it?

It looks like you would need to change your xml definition tags. That's what defines how your nodes should be created during import.

Hey,

Making the records through VB was taking really long but it wasn't a problem with how I wrote the code, just the fact that letting make each individual record while parsing through the xml was slow.

EDIT: I checked and I found that I fixed the problem using a .XSL file to transform the schema when importing.

Good luck,
--Muskilh

muskilh said:

Hey,

Making the records through VB was taking really long but it wasn't a problem with how I wrote the code, just the fact that letting make each individual record while parsing through the xml was slow.

EDIT: I checked and I found that I fixed the problem using a .XSL file to transform the schema when importing.

Good luck,
--Muskilh

Click to expand...

Would you please post you xsl as well? It may help others when they import XML into Access.

Also, for what it may be worth, you can import XML into Excel, then import the Excel xls file into Access. Access and Excel import XML differently.