Convert XML to Excel

Finding the rows in a tree

XML has no notion of a table, so the first thing that has to happen is identifying which elements are the records. The reliable signal is repetition: a parent element containing many children of the same name almost always means a list of records, and those children's own child elements and attributes become the columns. That works on the overwhelming majority of data XML — exports, feeds, API responses, configuration dumps — because they are all built the same way.

Attributes and child elements are both turned into columns, which is necessary because XML offers two ways to say the same thing and different systems choose differently. One export writes id="42" as an attribute; another writes <id>42</id> as an element; a third does both in the same document. A grid does not care about the distinction and neither should you, so both arrive as columns, with attributes marked so that a name appearing in both forms does not collide.

Nesting deeper than one level is flattened by joining the element names, in the same way as nested JSON. Where a record contains a repeated child — three order lines inside an order — a grid cannot represent it as rows without duplicating everything around it, so the repeated values are kept in their cell. Namespaces are stripped from the column names, because a column called ns2:customerName helps nobody in a spreadsheet, while the values themselves are untouched.

Prose XML has no table to find and converts badly. An XHTML page, a DocBook article, an ePub chapter or an SVG drawing is a document rather than a dataset: its structure is nested and irregular by design, and there is no repeating record to build rows from. The conversion will produce something, and that something will not be useful. This page is for data XML, and being clear about the difference saves more time than any amount of tuning.

When you want something else

When you know the structure, extract it deliberately rather than inferring it. xmlstarlet sel -t -m "//order" -v "@id" -o "," -v "customer" -n data.xml pulls exactly the fields you want with an XPath expression, and xq does the same with jq-style syntax. That is the difference between a conversion that happens to work and one you can put in a pipeline.

For very large files, everything here is the wrong shape: the document is parsed into memory whole, and XML parsed as a tree typically occupies several times the size of the file. Streaming parsers — Python's iterparse, or SAX in any language — walk a multi-gigabyte document with constant memory, and are the only realistic approach past a few hundred megabytes.