Summarizing a Large XML Export With a Streaming Parser

The usual reason to open a big XML export is a question: how much did we sell, to whom, which products dominate. You can answer it without loading the file into a database, with a script that reads the file once and keeps only running totals. This is worth knowing for two reasons: it is quick to write, and it is the same pass you would add to a loader to produce a control total.

The shape of the question

Each invoice has a currency attribute and one to four lines, each with a SKU, a quantity and an amount (the unit price). The questions: the number of invoices, the revenue per currency (quantity times amount, summed), and the three most ordered SKUs by quantity. Nothing needs more than one record in memory at a time, which is the condition for streaming.

The script

import sys, collections, time, resource
from decimal import Decimal
from lxml import etree

t = time.time()
tot = collections.defaultdict(Decimal)
cnt = collections.Counter()
skus = collections.Counter()
n = 0
for _, inv in etree.iterparse(sys.argv[1], tag="invoice"):
    cur = inv.get("cur")
    n += 1
    for ln in inv.iterfind("line"):
        amt = Decimal(ln.findtext("amount")) * int(ln.get("qty"))
        tot[cur] += amt
        cnt[cur] += 1
        skus[ln.get("sku")] += int(ln.get("qty"))
    inv.clear()
    while inv.getprevious() is not None:
        del inv.getparent()[0]
print(n, "invoices", f"{time.time()-t:.2f}s peak {resource.getrusage(resource.RUSAGE_SELF).ru_maxrss//1024} MB")
for c in sorted(tot):
    print(c, cnt[c], "lines", tot[c])
print(skus.most_common(3))

20000 invoices 0.21s peak 21 MB
EUR 9764 lines 1481098.58
USD 30245 lines 4579242.21
VND 9810 lines 1503163.23
[('S154', 623), ('S233', 620), ('S160', 606)]

The structure is the streaming idiom: iterparse with tag="invoice" yields each invoice when it is complete, the loop reads what it needs from the whole record (the attribute and the child lines), and the clear and sibling deletion at the end release it. The total number of lines per currency adds up to 49,819, the profile's count (9,764 + 30,245 + 9,810), a free consistency check.

Why Decimal

The amounts are money, and they are text in the file. Converting with float would be shorter, and sums of 30,245 floats collect rounding error in the last digits. Decimal(ln.findtext("amount")) parses the text exactly, and multiplying by an integer quantity stays exact. The result 4579242.21 is the true sum of the values as written. The cost is speed: Decimal arithmetic is slower than float, and for this file it did not matter (0.21 seconds in total). For a file with hundreds of millions of lines, accumulate integer cents instead (int(Decimal(text) * 100)), which is fast and exact.

Do not add currencies

The three totals are in three currencies: US dollars, euros and Vietnamese dong. Adding them would give a number that means nothing, and a summary that prints one grand total across currencies is a bug. If the question needs one figure, convert with a rate table with a stated date, as a separate and visible step, and keep the per-currency totals beside it. The structure of the script, with the currency as the dictionary key, makes the right behavior the easy one.

A second opinion

A summary you cannot check is a summary you should not send. The cheap check is a different method that does not share the code. This one loads the whole tree and sums only the USD lines with a different loop:

root = etree.parse("invoices.xml").getroot()
tot = sum(Decimal(a.text) * int(l.get("qty"))
          for l in root.iterfind(".//line") for a in l.iterfind("amount")
          if l.getparent().get("cur") == "USD")

tree USD 4579242.21 0.14s peak 82 MB

The same figure, 4579242.21, from code that shares nothing with the streaming loop but the parser. Notice the cost: 82 MB against 21 MB, for a 4.4 MB file. For this size, the tree is fine and a little faster. At 400 MB the tree would need roughly seven gigabytes and the streaming loop would still use 21 MB. The check works at any size if you run it on a sample (the first 1,000 invoices) and compare with the streaming result for the same sample.

For an independent check that does not use Python at all: xmllint --xpath 'sum(//invoice[@cur="USD"]/line/amount)' gives the sum of unit prices, which is a different number (it ignores quantity), so use it only for a simpler aggregate such as the count: xmllint --xpath 'count(//invoice[@cur="USD"]/line)' should print 30245.

Making it reusable

When the same export arrives every month, the script becomes a small tool. Four changes make it one.

1. Take the record tag, the group-by path and the measure as arguments, so it works on the next file without edits.

2. Write the summary as a table (CSV or a small JSON) with the file name, size, checksum and run date, so a number can be traced to an input.

3. Reject surprises. If a line has no amount or the quantity is not an integer, count it and carry on, then print the count of skipped lines at the end. A summary that silently skips 2 percent of the data is wrong in a way nobody sees.

4. Keep the totals in the same units as the source. Print VND 1503163.23, not an approximation, and leave rounding to the report.

If the summary is going to be compared with another system's figure (the ledger), compare per currency and per month, since a single grand total can agree by accident.

Handling a bad record

Real exports have bad records. A line with an amount of N/A makes Decimal raise InvalidOperation, and the script stops. Decide ahead of time whether that is what you want. For a control total, stopping may be right: a number computed over a subset is not the number. For exploration, catch the exception, count the skip and keep the line number:

try:
    amt = Decimal(ln.findtext("amount")) * int(ln.get("qty"))
except (InvalidOperation, TypeError, ValueError):
    skipped.append(ln.sourceline)
    continue

Print the number of skipped lines and the first few line numbers at the end. The line number is one reason to use lxml here: sourceline is available on every element.

If the whole file is malformed, the parser stops at the error, and the totals so far are partial. Do not print a summary from a partial run. Wrap the loop so that a syntax error aborts the report, and see mismatched tag errors in large XML for finding the line.

Related reading

The profile that comes before this is in counting elements and attributes in an unknown XML file, the loader version of the idiom in loading large XML files into a database in chunks, and the cluster overview in analysis on XML data when nobody has converted it yet. The decimal arithmetic is documented in the Python decimal module, and iterparse in the lxml parsing guide. Test a path with the XPath tester.

When a summary should run in a database

The questions keep changing. A script answers the one question you coded. If people will ask a dozen follow-ups (by month, by customer, by SKU), load the records into a table once and use SQL (turning XML feeds into relational tables).

You need joins. Revenue by customer segment needs a customer table. Streaming totals cannot join to data that lives elsewhere without loading that data into memory too.

The result must be reproducible by others. A SQL query on a loaded table can be rerun and reviewed by someone who does not read Python.

The data is already in a database. If the XML is a column of a table, query it there with the engine's XML functions, and skip the export.