Importing XML files into Access in the way Excel does? Possible?

[e561414]

Greetings.

Problem:
I've been looking for a decent way to import XML files into Access but I have had no luck; everytime I try to import one of these, Access creates several tables and these tables don't even contain any record, for some reason. Every XML contains invoice information that I want my database to handle; all of them have a very similar structure but I only need a few columns that repeat throught all the XML files.

What I'd like:
I would like the program to create a scheme, similar to what Excel already does with its "import XML" feature in the developer tab. In Excel, the procedure is simple: click import > choose file > choose columns; and every new import, even with multiple files, will display only the columns I need, row by row.

Alternative:
If Access can't do that, is it a good idea if I do this?

• Create Excel file with the XML structure I want
• Create a form where the user hits a button, chooses the XML file and then
• Import the chosen XML into the Excel file
• Save and close the Excel file
• Append that data into an existing table in my database

Any suggestion is, of course, welcome. Thank you all in advance.

 

[xenou]

Anything that works is a good idea. Many xml files are very different from each other. There is no way to give advice without knowing the specifics of the xml files involved.

 

[e561414]

Anything that works is a good idea. Many xml files are very different from each other. There is no way to give advice without knowing the specifics of the xml files involved.

I'd rather not post the xml files for privacy reasons, but when I import them using Excel, the information is arranged into columns; some columns have all of the rows filled with data, others have just a few rows with data and other columns don't have anything in them. There are a lot of columns, more than 40, usually, but I only need the ones named "InvoiceNumber", "Date", "Emisor", "Unit", "UnitPrice" and "Quantity". They will always have the information I need, regardless of the XML file, because that's how invoices are distributed in my country. I hope that's clear, if not, please let me know.

Thanks in advance.

 

[xenou]

Hi,

I don't think there's any answer I can give unless I can see at least give the structure of the xml, maybe with some dummy data.

In general, Access works *automatically* when the data represents a single table. If the data represents tables with related tables, it doesn't know what to do. You would probably need to run an xsl against it, or provide a schema file along with the data file.

ξ