Read xml File into Recordset

[Deleted member 73419]

Hi,
I have an excel file which I need to read into a recordset so that I can perform additional queries on it.

The xml file contains:

[SIZE=3][FONT=Calibri]<Catalog>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]<Rec>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]<ITEM dt:dt="string">1903</ITEM>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]<QTY dt:dt="string">15</QTY>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]<SUB dt:dt="string">Widget 1</SUB>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]</Rec>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]<Rec>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]<ITEM dt:dt="string"/>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]<QTY dt:dt="string">10</QTY>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]<SUB dt:dt="string">Widget 2</SUB>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]</Rec>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]<Rec>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]<ITEM dt:dt="string">1551</ITEM>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]<QTY dt:dt="string">5</QTY>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]<SUB dt:dt="string">Widget 3</SUB>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]</Rec>[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]<Catalog>[/SIZE][/FONT]

and i'd like to end up with a recordset, something like this:
Item | Qty | Sub
================
1903 | 15 | Widget 1
| 10 | Widget 2
1551 | 5 | Widget 3

which I can perform additional SQL queries on.

Something like "SELECT * FROM xmlFile" would be really nice but I suspect there has to be some code to read in the file?

Any help is appreciated.

Thanks

 

[jal]

Is this really an Excel file? When I copy and paste the data into a textfile and change the extension to .XLS, I find that Excel 2003 cannot read the file. Maybe Excel 2007 can read it? (I don't have Excel 2007 installed).

 

[Galaxiom]

XML is not an Excel file. It is an Extensible Markup Language file.
Access 2003 onwards can import them into a table.

There appears to be an error in the sample since the last line should be a close tag:
</Catalog>

 

[dragofly]

Galaxiom, do you know how to import it in older versions of access.

 

[jal]

Galaxiom, do you know how to import it in older versions of access.

You might be able to use the Document Object Model. I gave it a whirl using Access 2003 but it might work in earlier versions. But here's the problem. This xml reader doesn't like your xml text, specifically the phrases where the datatypes are defined as "string". In other words here's what it likes (I simply removed those phrases using my mouse).

<Catalog>
<Rec>
<ITEM>1903</ITEM>
<QTY>15</QTY>
<SUB>Widget 1</SUB>
</Rec>
<Rec>
<ITEM>1800</ITEM>
<QTY>10</QTY>
<SUB>Widget 2</SUB>
</Rec>
</Catalog>

So at this point you would either need to create some code to remove those phrases, or find some code to configure the xml reader to ignore them. I have no prior experience with configuring the xml reader, but perhaps i could create some code to remove those phrases, if I have time.