Answering Questions From XML Data With Counts and Joins

Someone hands you an XML file and a question. How much did we sell last quarter? Which sensors failed? How many papers per year? The question is simple and the file is not: it is large, or it is five thousand files, or it comes from a system nobody documented. This guide is a method for getting from file to answer without producing a confident, wrong number. It follows the order I work in, uses the same orders data throughout so the numbers can be checked against each other, and spends most of its words on the checks, because that is where work on XML succeed or fail.

The method in six steps

1. Pin the question. Write it as a sentence with a unit, a period and a population. "Revenue in euros for paid orders placed in March" is a question. "Sales" is not.

2. Inspect the shape. Find out what the records are, how they repeat, which fields are optional and which values occur, before writing the extraction.

3. Extract once. Pull the fields you need into a typed table, with a stated type for each. Do not query the XML repeatedly.

4. Check the extraction. Count the records and sum one numeric field, both against the source by another route.

5. Answer in SQL or a DataFrame, with joins that report what they did not match.

6. Record it. Keep the script, the source's checksum, the checks' output and the choices you made.

The rest of the guide takes them in turn.

Pin the question

Most wrong answers begin with a question that was not pinned. "How many customers?" can mean distinct names, distinct ids, customers with at least one paid order or customers who ordered in the period. Write the definition, and write what is excluded: canceled orders, test accounts, credit notes. When the data carries a unit, the definition names it, because totals across units are meaningless. In the invoice example, 3,143,948.49 is the sum of the payable amounts of 5,000 invoices when you ignore the currencyID attribute. It adds euros to pounds to dollars and answers nothing (summing values across thousands of XML invoices).

Say who will use the number and how precisely. A figure for a finance close and a figure for a rough chart tolerate different error. That decides how much checking to do, not whether to do any.

Inspect the shape

Do not open a large XML file in an editor, and do not load it as a tree to look at it. Look from the outside, cheaply, in this order.

The first and last bytes. head -c 2000 shows the declaration, encoding, root, namespaces and the first record. tail -c 500 shows whether the file ends where it should. A file that stops in mid-record was truncated, and everything you compute from it is short.

A record count. grep -c '<order ' file.xml returned 400,000 in 0.3 seconds on the 132 MB file. For machine-written files whose structure you have seen, it is an independent count that needs no parser. Count the repeating child as well: grep -o '<line ' file | wc -l gave the line count, 300,251 on the 100,000-order file.

Well-formedness. xmllint --noout --stream file.xml checked the whole file in 1.1 seconds with about 13 MB of memory, where the same check without --stream built the tree and needed about 1.9 GB (parsing big XML files fast).

The structure. A streaming script that counts every element path and attribute path tells you the record element, the depth, the optional fields and how often each occurs (detecting structure changes in an XML feed has it, and counting elements and attributes in unknown XML is the exploratory version). Look for paths that occur in fewer records than the repeating element: those are your nullable columns. Look for attributes that carry roles, like a contrib-type or a type, because they split one element name into several meanings.

The values. For each field you plan to use, list the distinct values or the range. A status with seven spellings of "paid", a date column with some N/A, a quantity of -1: you want to meet these now.

Extract once

The extraction is a small, boring program. Make it a streaming loop that writes typed rows, for three reasons: it is bounded in memory, it is rerunnable and it makes the types explicit.

import decimal, pyarrow as pa, duckdb
from lxml import etree

cols = {"order_id": [], "status": [], "customer": [], "qty": [], "price": []}
for _, el in etree.iterparse("o400k.xml", events=("end",), tag="order"):
    oid, st, cu = int(el.get("id")), el.get("status"), el.findtext("customer")
    for line in el.iter("line"):
        cols["order_id"].append(oid); cols["status"].append(st); cols["customer"].append(cu)
        cols["qty"].append(int(line.get("qty")))
        cols["price"].append(decimal.Decimal(line.findtext("price")))
    el.clear()
    while el.getprevious() is not None:
        del el.getparent()[0]
con = duckdb.connect(); con.register("lines", pa.table(cols))

Money is Decimal, not float. The loader of a tool that infers types would often choose double, and the sums then carry rounding noise: Spark's inference printed 2.543324211699986E8 for a revenue that is exactly 254332421.17 (flattening XML in Spark and Databricks). Identifiers are text or integers by a decision, not by accident. Dates are dates.

The result of the extraction is a table, and every question from here on is SQL. For recurring use, write it once to Parquet: a 132 MB XML file became 12.1 MB, and a grouped sum ran in 19 ms where re-streaming the XML takes about four seconds (choosing Parquet after you parse XML). When the data is many small files, as in the invoice and article examples, the same idea holds: one pass over the files, one table, then queries.

Check the extraction

A table that loaded without error is not a correct table. Two checks catch most defects, and both compare with the source by a different route.

Count. The number of orders in the table equals the number of <order tags in the file; the number of lines equals the number of <line tags. On the large file: 400,000 orders, 1,199,909 lines, in both.

Sum. One numeric field, summed in the table and summed another way. The revenue computed in SQL from the table was 3,001,978,835.00, and so was the revenue from the simple streaming loop, from PostgreSQL after COPY, from Spark with a decimal schema and from the Parquet file. Five routes, one number. When an engine disagrees, the difference tells you the cause: a disagreement in the cents means a type problem, in the thousands a lost record.

Add two cheap ones. Distribution: counts by a categorical field against a direct grep, here 134,011 open, 133,769 paid and 132,220 shipped from three tools that sum to the 400,000 total (counting records by attribute in a large XML export). Nulls: count rows where a field that must exist is missing. A permissive reader that turns a malformed value into null and moves on is the quietest way to lose data.

Keep the failures. When you process thousands of files, some will not parse and some will lack the field. Collect them in lists, print them with the totals and exit nonzero. In the invoice test, a truncated file and an invoice without a payable total were reported separately, with the line and column of the first, and the good files still summed to the same totals.

Answer the question

With a clean table, the answers are short. On the 400,000 orders:

orders, lines, avg lines/order: 400000, 1199909, 3.0
top 3 customers: customer-486 6936227.43, customer-301 6763447.33, customer-325 6762174.94
revenue by month (first 3): 2026-01 252433062.61, 2026-02 249595897.26, 2026-03 251120694.28
share of revenue from top 10 customers: 2.23 percent

Each is one query. Notice what the last one says: in this generated data the ten biggest customers account for 2.23 percent of revenue, so there is no concentration, a result that a real data set would not show and that the generator guarantees. That is a reminder to look at whether an answer is plausible before reporting it. If the top ten of a real retailer held 2.23 percent, you would check the extraction, as it would suggest that most customers were being merged or split.

Some questions need more than a group-by:

Time series. For sensor files, the work is in the checks before the averages. A day of three sensors at ten-second cadence produced 8,640, 8,340 and 8,640 readings, which pointed to a 50 minute gap in the second, and a difference check found a single jump of 50.44 degrees in the third at 16:40, where the device had switched to Fahrenheit under a unit attribute that still said Celsius. The hourly means looked like numbers throughout: 22, then 74. Only the gap and jump checks make them trustworthy (extracting time series from XML sensor files).

Counts of people and papers. For archives of article metadata, the traps are in definitions: which date is "the year", what counts as an author when the same element holds editors, how to normalize keywords (analyzing publication metadata from XML archives).

Combining sources. The most dangerous step is the join.

Join carefully

Attaching your own data to a table from XML is where rows vanish. An inner join of 300,251 order lines to a customer CSV returned 298,971 rows and 748,233,254.05, losing 1,280 lines and 3,239,307.30 of revenue, with no error. Two customers were spelled with a capital letter and a trailing space in the CSV. The anti-join found them at once:

unmatched customers: customer-42 638 lines, customer-43 642 lines

638 plus 642 is 1,280, the exact number of missing lines, and normalizing both keys with lower(trim(...)) restored all 300,251 rows and the full 751,472,561.35 (joining XML-derived tables to your own data). Three rules follow. After any join meant only to add columns, assert that the row count and the key totals did not change. Always run the anti-join and report its count. And normalize keys once, at load, with the original kept beside it.

Choosing the grain of the table

The most consequential decision in the extraction is the grain: what one row represents. For orders with lines there are three honest answers, and each supports different questions.

One row per order is the right grain for questions about orders: how many, by status, by month, by customer. It forces a decision about the repeating part. Sum the lines into an order total during extraction, or keep a separate lines table. Storing only the first line, a surprisingly common shortcut, quietly breaks every revenue figure.

One row per line is the right grain for questions about products and revenue. Order-level fields repeat on each row, and that makes counting orders wrong unless you count distinct order ids. On the 400,000 orders, count(*) over the lines table gave 1,199,909 and count(distinct order_id) gave 400,000. Those are different questions, and reports that confuse them overstate the number of orders by a factor of three.

Two tables, orders and lines, joined by the order id, which is the relational shape of the source. It avoids the repetition and the counting trap, and costs a join.

Whichever you pick, name the grain in the table name or a comment (order_lines, not orders) and write the key: which column or columns are unique. Test it. SELECT order_id, sku, count(*) FROM order_lines GROUP BY 1, 2 HAVING count(*) > 1 finds a grain that is not what you thought, and a key that is not unique means a duplicated record or a wrong key. Flattening repeating XML elements into rows goes through the options.

Optional elements deserve a rule too. A missing discount is null, which is not the same as zero. A sum treats null as absent, an average skips it, and a join on it fails. Decide which meaning holds for each field, and write it in the schema you keep beside the script.

A worked session

Here is the order in which I would work on a feed I have not seen, with the commands that were used on the orders file.

First, the outside view. ls -l gives the size, head -c 600 shows the root, the namespace and the first record, tail -c 200 shows a complete ending. xmllint --noout --stream confirms the file is well-formed in about a second. grep -c '<order ' returns 400,000, and grep -o 'status="[a-z]*"' | sort | uniq -c shows three statuses with counts that add to 400,000. Nothing has been parsed into memory and I already know the record count and one distribution.

Second, the shape. A streaming script prints every element path with its count. /orders/order/lines/line occurs 1,199,909 times, /orders/order/lines/line/price the same number, and every other path 400,000 times. So lines is always present and has at least one line, and no optional fields exist. That decides the table: lines grain, no nullable columns.

Third, the extraction, as above, typed, streaming. It takes a few seconds. Fourth, the checks: 400,000 distinct orders, 1,199,909 lines, revenue 3,001,978,835.00, zero nulls. They match the outside view. Fifth, the questions, each a query of milliseconds. Sixth, a note in the repository with the file's checksum, the script and the check output.

The whole session is under an hour for a new feed, and most of that is thinking. The same steps on a feed with optional fields and namespaces take longer in step two, and the checks keep them honest.

Questions the file cannot answer

Some questions look answerable and are not, and recognizing them early saves a day. A file of orders cannot tell you how many customers you lost, because customers who never ordered are not in it. A file of sensor readings cannot tell you whether a sensor was offline or the reading was dropped in transit, only that no reading exists. An export filtered by the sender, for example "orders updated in the last 30 days", answers questions about that window and no other, however large it is.

Check what the extract represents before computing on it. Is it a snapshot or a history? Is it complete for the period or sampled? Does it include canceled, deleted or test records, and are they marked? Did the system that produced it apply a filter, a join or a currency conversion on the way out? The answers are usually in the export's documentation, and when there is none, in a conversation with whoever runs the source. The cost of that conversation is small next to a report built on a population nobody described.

When the file cannot answer the question, say what it can answer instead. "These are the orders placed in the window, 400,000 of them, and they cannot show churn" is a better deliverable than a churn estimate built on an assumption. If the question matters, name the extra data that would settle it, a customer master or an event log, and who might have it.

Finally, keep the difference between a finding and a data artifact in view. A drop in orders on one date might be a business event, a failed export or a timezone shift that moved a day's orders into the next. The gap and range checks apply to business data as much as to sensors: look at counts per day before looking at revenue per day, and the artifacts show up as holes and spikes in the counts first.

Presenting the answer

A number without its caveats invites misuse, so present the answer with what it rests on. State the population (400,000 orders, all statuses, placed in 2026), the definition (revenue is quantity times unit price, before tax) and the checks that passed. If part of the data was excluded, say how much: "5,000 invoices read, 1 malformed file and 1 without a total excluded".

Choose the display for the question. A table of counts by status needs no chart. A time series needs a line, with gaps drawn as gaps; most plotting libraries connect points across a missing hour and hide it, which is why the gap check came first. Put the quality flags into the picture where they matter, such as shading hours with too few readings.

Resist false precision. Revenue to the cent is appropriate for a reconciliation against the source, and "about 3.0 billion" is appropriate for a summary. A count of authors per paper does not need three decimals. Round at the last step, never during the calculation.

When the number surprises you, assume the method before the world. The top ten customers holding 2.23 percent of revenue, or hourly temperatures of 74 degrees in a room, are results to investigate before they are results to report. Surprise is a signal that a check is missing.

Making it routine

If the question returns, automate the parts that do not need thinking. Put the extraction in a script with arguments for the input and the output. Run it on a schedule against each new delivery. Have it write the check numbers to a log table and fail loudly when they disagree with the source or with the previous run beyond a tolerance you choose, for example a record count more than 20 percent away from yesterday's.

Keep a small corpus of test files next to the script: a tiny valid one, one with an optional field missing, one with a malformed record, one with a value that has a leading zero or a special character. The tests that parse them and compare the table to expected rows are the cheapest insurance against a change that breaks the extraction and passes unnoticed. They also document, better than prose, what the feed is allowed to contain.

When the volume grows, the next steps are in the same cluster: split large files and process the parts in parallel, move the table into a database or a lake, and index what you query.

Choose the tool by the job

The common mistake is the third row. A shell loop that starts a tool for each of thousands of files spends its time on process startup. Give the tool all the files, or loop in a language.

Mistakes that look like findings

A few errors are common enough to name.

• Counting the wrong unit. Files, records and entities are three counts. A repeated record counted twice, or a line item counted as an order.

• Mixing units. Currencies, time zones, Celsius and Fahrenheit, cents and units.

• Believing the unit attribute. The sensor said Celsius while reporting Fahrenheit.

• Trusting a type guess. A double for money, an integer for an identifier with leading zeros, a date parsed in the wrong day-month order.

• Letting a join drop rows. And not noticing, because the result looks reasonable.

• Ignoring the rows that failed to parse. The total is short and nobody knows by how much.

• Using a pattern match as a parser. grep is excellent for checking and unreliable as the only method on files you do not control.

• Averaging over gaps. A mean from fewer readings looks the same as any other.

Each has a cheap guard: report the counts, group by unit, check ranges and jumps, declare types, run the anti-join, list the failures, cross-check with a second tool, and keep the count of readings next to every mean.

Record the answer

An answer someone will rely on needs a trail. Keep, next to the result: the file name and SHA-256 of the source, the number of records and files read, the number that failed, the script and its version, the five check numbers (records, lines, one sum, the distribution and the nulls) and any choices that shaped the result, such as which date counted as the year. It takes minutes to write and it is the difference between "the number is 3,001,978,835.00" and "the number is 3,001,978,835.00, from file x with checksum y, matching four independent computations".

If the question will come back, turn the extraction into a job that appends new rows to a table, and put the checks in the job. A pipeline that prints "400,000 orders, 1,199,909 lines, 3,001,978,835.00, 0 failures" every night is a pipeline whose silence you can trust.

Related reading

For the extraction speed, parsing big XML files fast has the measurements. To get the extracted data into a warehouse, read handling XML in warehouses and lakes. The XPath side, for small files and for selecting fields, is in XPath queries that cover most real needs, and the one-file-at-a-time view in summarizing a large XML export by streaming.

References for the pieces used here: the lxml parsing documentation, the DuckDB SQL reference, the Python decimal module and the pandas time series guide. Check a path expression in the XPath tester.

When this method does not apply

The question is about the document, not the data. If you are asking how a manual is structured or how a paragraph reads, you need document tools (XSLT, text extraction), not tables.

The XML is tiny and the question is once. A hand count or a single XPath is faster than building an extraction. The checks still apply, in miniature: compare the result with a second view.

The source system can answer. If the XML is an export from a system with a reporting database, ask that database, and use the XML to verify. A number from the source of record beats a number rebuilt from a feed.

The stakes are formal. An audited figure comes from the accounting or regulatory process, with its own controls. This method produces a check on it, not a replacement.

Your data does not look like this data. The generated orders have no gaps in their structure and no corrupt records, which real exports do. The checks in this guide are written to make those visible when they exist.