XML to Excel and to a table
Excel can import XML, and on flat files it does a decent job. The trouble starts where a record contains a list: that is exactly when totals begin to double, and the mistake is almost impossible to spot by eye.
Where the doubling comes from
Picture an order worth $5,000 with three line items inside it. The table should be either one row (the order) or three rows (the line items). When nested data is flattened carelessly you get a third option: three rows, each repeating the order total.
This is called a Cartesian product. A sum over such a table gives $15,000 instead of $5,000. If orders have different numbers of items, the error is uneven too, and you cannot check it by adding up in your head.
The worst part is that the table looks completely normal.
The right question to ask first
Before flattening an XML file, decide what a row is. The answer depends on the task, and it can change from question to question within the same file.
If a row is an order, the list of items inside has to be either collapsed (values joined together, or just counted) or left out. If a row is a line item, the order data repeats in every row on purpose, and you must sum the line-item field, not the order field.
A tool has to make that choice explicit. Tabulens shows which elements repeat and how many times, proposes a suitable one, and tells you plainly when repeating nested elements remain inside a row: their values are joined with a separator, and a hint next to them suggests switching to that level to expand them.
Attributes are data too
Half of all XML exports keep values not in element text but in attributes: <row id="1" name="First"/>. Some tools forget about them and show an empty table with the right number of rows.
Attributes should become columns on a par with nested elements, with a clear marker so that @id (an attribute) is not confused with id (a nested element) when the document has both.
Column names built from paths
When nested elements are flattened, the column name is built from the path: customer/email. That is longer than plain email, but it avoids collisions, which are inevitable when the same name appears at different levels (a date on the order and a date on the line item).
On export to DBF, where a field name cannot be longer than ten characters, such names have to be shortened, and the program must tell you how rather than silently truncate them.
FAQ
Can I trust a pivot table built on an imported XML file?
Only if you are sure the nesting was not flattened into a Cartesian product. Check the grand total against one order you know.
How do I count the line items in an order?
Open the table at the line-item level and group by the order identifier with a “count” measure.
What should I do with very deep nesting?
Choose the level where the data you need lives instead of flattening everything at once: you would end up with hundreds of columns that nobody can work with.
Free for personal use. Your file is not uploaded to a server. Windows version — 3.5 MB, no installation: details. Organizations: license.