Summing Values Across Thousands of XML Invoices by Currency

A folder of five thousand invoices, one XML file each, and a question: how much is owed? It is one of the most common XML jobs and one of the easiest to get subtly wrong. The parsing is trivial. The traps are in what you add, in what you do with the files that do not behave, and in how you start the work, which can be sixty times slower than it needs to be. This post sums the invoices four ways, shows the numbers and then adds the checks that make the total trustworthy.

One invoice

Each file looks like this, with namespace prefixes in the style of common e-invoicing vocabularies:

<Invoice xmlns="urn:example:invoice" xmlns:cac="urn:example:cac" xmlns:cbc="urn:example:cbc">
  <cbc:ID>INV-00001</cbc:ID>
  <cbc:IssueDate>2026-03-14</cbc:IssueDate>
  <cbc:DocumentCurrencyCode>EUR</cbc:DocumentCurrencyCode>
  <cac:InvoiceLine>...</cac:InvoiceLine>
  <cac:LegalMonetaryTotal>
    <cbc:PayableAmount currencyID="EUR">142.35</cbc:PayableAmount>
  </cac:LegalMonetaryTotal>
</Invoice>

The amount to add is the text of PayableAmount, and the currency is its currencyID attribute. The elements are in namespaces, so a path without the namespace mapping finds nothing (selecting elements with a namespace in XPath covers this).

Four ways to add them up

Python with lxml. Parse each file, find the element with a namespace map, add the text as a Decimal into a dictionary keyed by currency:

import glob
from decimal import Decimal
from collections import defaultdict
from lxml import etree

NS = {"cac": "urn:example:cac", "cbc": "urn:example:cbc"}
tot = defaultdict(Decimal)
for f in sorted(glob.glob("/tmp/inv/*.xml")):
    r = etree.parse(f).getroot()
    el = r.find("cac:LegalMonetaryTotal/cbc:PayableAmount", NS)
    tot[el.get("currencyID")] += Decimal(el.text)
print({k: str(v) for k, v in sorted(tot.items())})

python lxml  : 5000 files in 0.11s {'EUR': '1560107.09', 'GBP': '801415.64', 'USD': '782425.76'} bad 0

xmlstarlet, one command for all files. It accepts many files at once, prints the currency and amount for each, and awk adds them:

xmlstarlet sel -N cac=urn:example:cac -N cbc=urn:example:cbc \
  -t -m '/*/cac:LegalMonetaryTotal/cbc:PayableAmount' -v '@currencyID' -o ' ' -v . -n /tmp/inv/*.xml \
  | awk '{s[$1]+=$2} END{for(k in s) printf "%s %.2f\n", k, s[k]}' | sort

EUR 1560107.09
GBP 801415.64
USD 782425.76

It took 0.22 seconds. The figures are the same as Python's, which is the first cross-check you want: two independent tools agreeing.

grep and awk. If the files are machine-written and the element appears exactly once per file in a fixed form, a text tool is the fastest of all, 0.03 seconds here. It is also the most fragile: it breaks on a different attribute order, on a line break inside the tag, or on an extra PayableAmount elsewhere in the file. Use it to check a parser's result, not as the parser.

One xmllint per file. The loop for f in *.xml; do xmllint --xpath ... $f; done took 6.6 seconds, and it cannot group by currency without a second command. The parsing is not the cost; the cost is starting 5,000 processes. When a tool accepts many files, give it many files. When it does not, call it from a language that does.

The first trap: adding currencies

A sum that ignores the currencyID is wrong in an instructive way. With the one-process-per-file loop I added the numbers without looking at the attribute:

all currencies mixed: 3143948.49

3,143,948.49 is the sum of 1,560,107.09 euros, 801,415.64 pounds and 782,425.76 dollars. It looks like a total and is nothing. Always group by the unit before adding, and refuse to add across units without an explicit rate and date. If a currency attribute is missing, count that file as an error, not as the default currency.

The same advice holds for quantities in different units, prices with and without tax and amounts in cents versus units. If the XML carries the unit, group by it. If it does not, ask the provider before summing.

The second trap: the files that do not parse

Five thousand files from an outside source will include a bad one. I added two to the folder: a file truncated after 400 bytes, and an invoice with no payable total. The script reads inside a try and records the problems:

summed 5000 {'EUR': '1560107.09', 'GBP': '801415.64', 'USD': '782425.76'}
malformed: [('inv99998.xml', "expected '>', line 6, column 136 (inv99998.xml, line 6)")]
no total : ['inv99999.xml']

The good files still sum to the same totals, the truncated file is reported with the line and column where the parser gave up, and the invoice without a total is reported separately. Two different problems, two lists. A script that crashes on the first bad file loses an hour of work. A script that skips silently reports a total that is short by an unknown amount. The right behavior is to finish, print the total and print the exceptions, and to make the exit status nonzero if any exist, so a scheduler notices.

In the Python code above, r.find(...) returns None when the element is absent, and el.get(...) on None raises an error. That is why the checking version tests for None first. In xmlstarlet, a file with no match prints nothing, and the file silently contributes zero, so count outputs against inputs: ls | wc -l against the number of lines printed.

Make the total auditable

A number that someone will be asked about needs a trail. Three additions cost little.

Count files and amounts. Print "5,000 files, 5,000 amounts, 0 malformed, 0 without total" next to the sums. If the files and amounts differ, you know before the finance team does.

Cross-check with a second method. The Python and xmlstarlet results agreeing is evidence that neither mis-parsed. For a one-off, a grep on the raw files is a third.

Compare with the source's own totals. Many systems print a control total with the batch. If your sum differs, the difference is a list of files to check.

For larger sets, parallelize by file with a process pool (parallel processing of split XML files), or load the amounts into a table and let SQL do the grouping (streaming XML into PostgreSQL without a DOM).

Related reading

This post is part of answering questions from XML data. For command-line extraction tools, see extracting values with xmlstarlet, and for the broader invoice problem parsing tax-style e-invoices. Decimal arithmetic in Python is documented in the decimal module, and the xmlstarlet options in its user guide. Test the path expression in the XPath tester.

When summing XML invoices needs a stricter approach

The sum is a legal or accounting figure. Then it belongs in the accounting system, with the invoices as source documents. Use the sum here as a check and not as the figure of record.

Invoices can be credit notes. A credit note has a different document type and a negative effect. Read the type and sign before adding.

Rounding rules matter. Tax is often calculated per line and rounded, or per invoice, and the two differ by cents. Use the document's stated totals, not a recomputation, unless you know the rule.

The files are signed. If you must preserve evidence, work on copies and keep the originals untouched (XML signature wrapping in plain language explains why reformatting breaks signatures).