XML to CSV, Explained: Repeating Records Become Spreadsheet Rows

XML and CSV want different things. XML nests: a record can hold children, and those children can hold more children, as deep as the schema feels like going. CSV is flat: a grid of rows and columns with no hierarchy at all. So converting between them is not a syntax swap. It is a decision about which part of the tree is "a row," which part is "a column," and what to do with everything that does not fit that two-dimensional picture.

Most XML you actually need to flatten is a list of similar things: orders, products, log entries, search results. That is the case this guide covers, because it is the case where the conversion is clean and worth doing by hand instead of writing a parser.

The one rule that makes everything click

Here is the mental model. Find the element that repeats. Each repetition is a row. Each child of that element is a column.

That is the whole trick. If your XML has <library> wrapping a long run of <book> elements, then <book> repeats and each <book> is a row. The root <library> is just the container, the bag that holds the records, so it never becomes a row itself. A common first mistake is expecting the outermost tag to map to a line of CSV. It doesn't. The container is structural; the repeating element inside it carries the data.

When several elements repeat in the same file, detection can guess the wrong one. A feed that opens with a <header> block and then lists three hundred <order> entries has two repeating shapes. In that case you name the record element explicitly so only <order> becomes rows and the header is left out of the grid.

A worked example

Take this XML:

<catalog>
  <book id="b1">
    <title>SICP</title>
    <author>Abelson</author>
    <year>1985</year>
  </book>
  <book id="b2">
    <title>The Little Schemer</title>
    <author>Friedman</author>
    <year>1974</year>
    <note>used</note>
  </book>
</catalog>

<book> repeats, so each <book> is a row. The children title, author, year, and note become columns. The id attribute, when you turn attributes on, becomes a column named @id so it never collides with a child of the same name. The result:

@id,title,author,year,note
b1,SICP,Abelson,1985,
b2,The Little Schemer,Friedman,1974,used

Notice the second book has a <note> and the first does not. The header is the union of every record's fields, in first-seen order, and the missing cell is simply left blank. That blank is the whole point: it keeps the columns aligned even when the source is ragged. You can convert that example yourself in the XML to CSV converter and watch the header settle as you add or remove a tag.

Flattening nested data

Leaf elements with plain text map straight to columns. The interesting case is when a child holds more children. An address like <addr><city>London</city><zip>EC1</zip></addr> is not a single value, so it cannot live in one cell as-is. The converter flattens it into dotted column names: addr.city and addr.zip. That dotted convention is readable, it survives a round trip into a spreadsheet, and it tells anyone reading the CSV exactly where the value came from in the original tree.

There is a limit, and it is an honest one. A branch that is genuinely tangled, repeated sub-lists inside a record, mixed text and elements, will not flatten into clean separate columns. When that happens the inner text is gathered so the content shows up rather than vanishing into an empty cell. You see the data; you just may not get the granular columns you wanted. The fix is upstream: reshape the XML so each record is one level of nesting deep, then convert. A grid is two dimensions, and you cannot squeeze an arbitrary tree into two dimensions without losing some of its shape, so the cleanest exports come from XML that was already close to tabular.

Where this actually pays off

Legacy systems are the reason this conversion still matters. Plenty of older exports, supplier feeds, SOAP responses, and decade-old reporting tools speak XML and only XML, while the spreadsheet, the database importer, and the analyst on the other end want CSV.

I hit this last month with a product feed. A supplier sent a few hundred <product> elements, each with a <sku>, <name>, <price>, and an occasional <promo> tag, and a merchandiser wanted it in Google Sheets by end of day. The instinct is to write a quick script. But the file was one clean repeating element, so I pasted it, let <product> get detected as the row, and had the CSV in the time it took to read this sentence. The products carrying a stray <promo> lined up under one column with blanks for the rest, exactly as they should. No script, no "can you re-export this" thread, just paste and download.

The same shape shows up when you migrate off a system that only emits XML into one that ingests CSV. Turn attributes on so a primary key stored as id="..." rides along as an @id column. Pick the delimiter your importer expects, tab or pipe if the data is full of commas, and let the escaping handle the rest.

Why the escaping is not optional

CSV looks simple until a value contains a comma, a quote, or a line break. Then a naive join shifts every column to the right and quietly corrupts the import. This is the single most common way a "working" export breaks at the loading stage.

Proper conversion follows RFC 4180: any field holding a comma, a double quote, or a newline gets wrapped in quotes, and internal quotes are doubled. A value like he said "hi", ok comes out as "he said ""hi"", ok", which any compliant reader parses back to the original string. This is why building a CSV test fixture from a real XML sample is more honest than typing toy data: the messy values that actually break parsers come along correctly escaped, so your test exercises the real edge cases.

A related gotcha: if the converter returns nothing, suspect the markup, not the tool. An unclosed tag, a stray & that should be &, or mismatched nesting makes the parser reject the whole document and report a line number rather than guess. Fix the markup, or clean it with an XML formatter first, then convert.

When CSV is the wrong target

Flattening loses hierarchy, and sometimes you want to keep it. If the records are deeply nested and the relationships between levels matter, a flat grid throws away exactly the structure you care about. In that case keep the tree and convert to a format that preserves nesting instead of a format that erases it. For that, reach for the XML to JSON converter, which keeps the parent-child shape intact. CSV is the right destination when your data is a list of similar records; JSON is the right destination when the nesting is the point.

Match the target to the shape of the data, find the repeating element, turn nested children into dotted columns, and let RFC 4180 escaping protect the values, and an XML export drops into a spreadsheet without a single line of code.

---

Made by Toolora · Updated 2026-06-13