TECH TIP: Flattening XML data into 1 table in Microsoft Access

I had to deal with XML data with parent-child-child-child etc … you get the idea. If you import/link this XML file into Microsoft Access, it just creates disconnected (no relationship) parent table, child table, child table, child table, etc. If you want to write a join query to flatten the data, you just can’t because there is no joining keys between the tables.

Here is a non-programmatic way of taking care of this little dilemma:

1) Open the XML file up in Microsoft Excel instead and select the first option (default) and if there are any errors, just ignore them

2) Microsoft Excel opens the file up and flattens the entire parent-child-child relationship into a flat row per data node

3) Save the Microsoft Excel file somewhere on your hard drive

4) Open up Microsoft Access and link this Microsoft Excel spreadsheet to a table

5) Walla! You are using the XML data flat in your Microsoft Access database

Just remember to import the XML into Microsoft Excel anytime your data changes and just refresh your Microsoft Access link.

Obviously, there are many programmatic ways of solving this data dilemma but the above tip is for solving it in a non-programmatic way for the masses (specially small business owners).

%d bloggers like this: