Analysis on XML Data When Nobody Has Converted It Yet

An XML file arrives and someone asks a question about what is in it. How many orders, how much revenue, which products, how many records are missing a field, what changed since last week. The standard advice is to convert the XML to something else first (CSV, a database, JSON) and then analyze that. It is good advice for recurring work and a detour for a single question. The file may be bigger than your spreadsheet, the structure may not map onto a table cleanly, and the conversion is where mistakes enter. Often the quickest honest answer comes from the XML itself, with the right tool at the right size.

This guide is a method for that situation. It covers the order of work, how to choose a tool from five that are within reach, the traps that cost me time (namespaces, attributes, types, memory), and how to check an answer you cannot easily eyeball. The numbers come from runs on generated data, which I describe, and from one real public file. The spokes in this cluster take individual jobs further, and they are linked where they apply.

A method in six steps

I follow the same order every time, because the order protects against the mistakes that look like results.

1. Look. Open the first screen of the file and note the size, the encoding declaration, the namespaces on the root and whether it is pretty-printed.

2. Profile. Count every element and attribute path. This tells you the records, what repeats, what is rare and how deep it goes.

3. Sample. Read ten records by eye, including the biggest and the oddest. Look at actual values: formats, units, empties.

4. Choose a tool by the shape of the question and the size of the data (the next section).

5. Answer. Write the smallest thing that produces the number.

6. Prove it. Get the same number a second way, or reconcile with a control total the sender gave you.

Steps 1 to 3 take ten minutes on a file you have never seen. They are what separates analysis from guessing, and they are the steps that get skipped when someone is waiting for an answer. Step 6 is the one that gets skipped when the answer looks plausible.

Step 1: look

For a file that fits on a screen or two, xmllint --format file.xml | head -40 shows the structure with indentation, even when the file is one long line. The command builds a tree, so for a very large file use head -c 2000 file.xml instead, which reads only the start. What you are checking: the XML declaration and its encoding, the root element and its xmlns attributes, and the first record.

xmllint --format invoices.xml | head -12

<?xml version="1.0" encoding="UTF-8"?>
<invoices>
  <invoice id="1" cur="EUR">
    <customer>c1</customer>
    <due>2026-02-02</due>
    <line sku="S232" qty="5">
      <amount>97.23</amount>
    </line>
    <line sku="S98" qty="2">
      <amount>84.87</amount>
    </line>
    <line sku="S244" qty="5">

Already you know the record (invoice), that it has an id and a currency as attributes, a customer, a due date and repeating line children that each carry a SKU and quantity as attributes and an amount as an element. You also know there is no namespace, which makes every path easier. If the root had an xmlns="..." attribute, you would write down the URI now, because every query will need it.

Step 2: profile

A streaming count of paths answers the structural questions at once. The script and its reasoning are in counting elements and attributes in an unknown XML file. On the invoices:

  20000  /invoices/invoice
  20000  /invoices/invoice/customer
  20000  /invoices/invoice/due
  49819  /invoices/invoice/line
  49819  /invoices/invoice/line/amount
    400  /invoices/invoice/memo
  20000  /invoices/invoice/@cur
  20000  /invoices/invoice/@id
  49819  /invoices/invoice/line/@qty
  49819  /invoices/invoice/line/@sku
max depth 4 0.23s peak MB 21

It ran in 0.23 seconds at 21 MB of memory. The reading: the record is invoice (20,000); customer, due, cur and id are present on every one; line repeats about 2.5 times per invoice; memo is on 2 percent. A depth of 4 says it is data and not a document. The ratio of 49,819 to 20,000 is the first number that tells you that a table would need two levels.

Step 3: sample

Look at values, not structure. Print the distinct values of anything with few of them (currencies), the minimum and maximum of anything numeric, the length of the longest text, and a few records chosen to be unusual: the one with the most lines, one with a memo, the first and last in the file. Small surprises show up here that no schema reports: a currency stored in two spellings, an amount with a comma, a date in two formats. If the sample reveals a mix of formats, the question you were asked may have become a data cleaning job, and you should say so before you answer.

Step 4: choosing a tool

Five tools were within reach on every data set here. Each has a range where it is the right choice.

For one count, xmllint is the whole job:

xmllint --xpath 'count(//line)' invoices.xml
xmllint --xpath 'count(//invoice[@cur="USD"]/line)' invoices.xml

49819
30245

49,819 lines, of which 30,245 are in US dollars. Those two figures are the control totals for everything that follows. For a streaming aggregate, summarizing a large XML export with a streaming parser shows the pattern: dictionaries and Counters, Decimal for money, clear each record. It produced revenue per currency in 0.21 seconds at 21 MB:

EUR 9764 lines 1481098.58
USD 30245 lines 4579242.21
VND 9810 lines 1503163.23

When the questions multiply, the move is to flatten into two tables and use SQL. Here is where I found the most surprising number of the whole exercise. The obvious way to put 49,819 rows into DuckDB from Python is executemany with an insert statement, and it took 24.6 seconds. The same rows registered as a pandas DataFrame and created as a table with create table ... as select took 0.07 seconds, with the parse itself at 0.28 seconds:

loaded 20000 invoices, 49819 lines in 24.56s            (executemany, row by row)
parse 0.28s, load via DataFrame 0.07s                    (DataFrame, then create table as select)

A 350-fold difference, and none of it was XML. DuckDB is an analytical engine built to take columns in bulk, and row-by-row inserts go through the slowest door it has. If an analysis feels slow, measure the stages separately before blaming the parser. With the tables in place the questions are one-liners:

select cur, sum(qty*amount) from invoice i join line l on l.invoice_id = i.id group by 1 order by 1;
select customer, count(*) n from invoice group by 1 order by 2 desc, 1 limit 3;
select count(*) filter (where memo is not null), count(*) from invoice;

[('EUR', Decimal('1481098.58')), ('USD', Decimal('4579242.21')), ('VND', Decimal('1503163.23'))]
[('c1', 23), ('c10', 23), ('c100', 23)]
[(400, 20000)]

The totals match the streaming script to the cent, and the memo count matches the profile (400 of 20,000). The loading side of the same flattening is described in flattening repeating XML elements into rows, and the DuckDB specifics in using DuckDB with XML through Python and an extension.

Step 5: the traps, in order of how often they cost me time

Namespaces. Four of the files in this cluster returned nothing for an unprefixed path: the Maven pom, the sitemap, the ECB rates and the podcast's itunes:duration. Each returned an empty result with no error. The remedy is the same every time: write down the namespace URI at step 1 and bind a prefix of your own in every query (how XML namespaces work). In an Android manifest the trap is inverted, since the elements are unnamespaced and the attributes are in the android namespace.

Values in attributes. The ECB file stores every rate as an attribute of an empty Cube, so findtext finds nothing and you read get("rate"). A profile shows this at once, because the attribute paths have the counts and the element paths have no text. See reading financial and government XML datasets with Python.

Types. Everything is text. Parse money as Decimal, identifiers as strings, dates by rule, and flags by dictionary. A podcast feed had durations as 00:42:10 and as 1850 in the same file, and a CRM export had dates in two orders; both need a rule written down, and a missing value kept distinct from zero (analyzing podcast and blog feeds from XML).

Memory. A tree takes 15 to 20 times the file size. On the 36.6 MB orders file the tree peaked at 566 MB, and the streaming version 21 to 46 MB. Anything over a few hundred megabytes should stream.

Units and directions. Do not add currencies. Check the direction of a rate: the ECB's USD rate of 1.1188 is dollars per euro, so 100 dollars are 89.38 euros, and multiplying by the rate instead would give 111.88, which also looks plausible.

Process start-up. For many small files, the cost is launching a program per file. 2,000 files took 3.2 seconds in a shell loop, 0.69 with eight parallel processes and 0.27 in one Python process, which also collected the one file that was not well-formed (extracting a field from thousands of XML files).

Step 6: prove it

Never send a number you have computed one way. Four checks, in increasing strength:

1. Count reconciliation. The rows you produced equal the elements in the source. 49,819 lines in the profile, in the XPath count, in the sum of per-currency line counts (9,764 + 30,245 + 9,810) and in the table.

2. Independent method. A different code path gives the same figure. The USD total of 4,579,242.21 came from a streaming loop, from a tree-based loop (which used 82 MB against 21) and from a SQL query. If three methods agree, the parse and the arithmetic are right; the remaining risk is in the question.

3. Control total from the sender. If the export came with a row count or a total, match it. If not, ask for one.

4. An unusual record by hand. Pick the invoice with four lines, compute its total with a calculator, and find it in the output.

Report the bad records too. A summary that says "49,819 lines read, 0 skipped" is a different statement from one that omits the skip count. Where a file is not well-formed, say so and say where: the parser tells you the line.

One-liners worth keeping

Before writing any script, a handful of xmllint --xpath expressions answer a surprising share of questions. I ran these on the invoices file, and each took well under a tenth of a second (the whole file is only 4.4 MB; the tree for a bigger one will take longer and more memory):

Two of these agree with other methods, which is the sort of cross-check I look for. The 623 units of SKU S154 equals the figure the streaming script produced when it counted the most ordered SKUs (('S154', 623)), and 400 plus 19,600 is the 20,000 invoices of the profile. When two independent paths give the same number, you can stop doubting the parse and start doubting the question.

Some notes on the expressions. number(amount) > 90 converts the text before comparing, because XPath compares text with text otherwise, and "9.99" > "90" is a different question as strings. count(line)=4 inside a predicate counts children of the current invoice. string(...) is there so that an empty result prints as an empty line, not an error. The functions in XPath 1.0 are limited (there is no max, min or regular expression), and the useful ones are listed in XPath functions worth learning for everyday XML work. When the question needs grouping, you have left the one-liner's range and should reach for the streaming script or SQL.

Reading a file you did not make

Analysis often starts with files from outside: a supplier, a public portal, a colleague. Three precautions belong at step 1.

Read bytes, not text. Hand the parser the file and let it honor the encoding declaration. A CRM export in Windows-1252, with accented names, parsed correctly in lxml because the parser read the declaration. The same file read as UTF-8 text fails or garbles the names. The failure modes and the fixes are in encoding mismatch errors in XML documents.

Do not let the parser fetch things. XML can reference external entities and DTDs, and a parser may load them. Recent lxml defaults are conservative, and still, for a file you did not make, use a parser with resolve_entities=False and no_network=True, or the defusedxml wrappers (safe XML parsing in Python with defusedxml). The attacks are described in what an XML external entity attack is.

Set limits. A hostile or broken file can be enormous or absurdly nested. Check the size before parsing, and for streaming scripts consider a cap on depth. A profile run is a good place to see the maximum depth first.

If the file is not well-formed, the first job is to find where. The parser gives a line and column, and pretty-printing the region around it usually makes the problem visible (fixing XML parse errors fast).

How big is big

The scale of the file decides most of the tool choice, and the measurements in this cluster give some anchor points. They are from one laptop and should be read as ratios.

The memory column is the one to read. A tree costs about 15 to 20 times the size of the file, and it scales linearly, so a 500 MB export needs roughly 8 to 10 GB as a tree and the same 20 to 50 MB as a stream. Time matters less than people expect: parsing runs at tens of megabytes per second, so even a gigabyte takes under a minute. If a script takes an hour, the XML is almost never the reason; look at the loop body, the database inserts (the 24.6 seconds earlier) and the disk.

Streaming has a price: one question per pass. If you will ask many questions of the same large file, pay for one pass that loads it into tables, then query the tables.

Handling the awkward records

Real files contain records that do not fit the pattern, and the way a script treats them decides whether the answer is right. Four kinds turn up.

Missing fields. A record without the element you are summing. Decide whether it counts as zero or is skipped, and count them either way. Handling optional XML elements and missing values goes through the three states (absent, empty, blank).

Values that do not parse. An amount of N/A, a date of 2021-13-45. Catch the exception, count the record, keep its line number (el.sourceline), and print the count and the first few lines at the end.

Duplicates. The same invoice id twice. A profile will not show it, since the counts are by path. Count distinct ids in SQL, or keep a set in the script (20,000 ids is a trivial amount of memory).

Wrong shapes. An invoice with no lines, a line with two amounts. Test the assumptions you built the script on with a few asserts or counters, so they fail loudly: assert len(amounts) == 1.

A summary should end with a line such as "20,000 invoices read, 49,819 lines, 0 skipped, 0 duplicates". The zeros are information.

A question from start to finish

To see the method in motion, take a question someone might actually ask of the invoices file: "How much did we bill in US dollars, and which customers are the biggest?"

Look. The first screen shows invoices with a currency attribute, a customer, a due date and lines with a SKU, a quantity and an amount. No namespace. UTF-8.

Profile. 20,000 invoices, 49,819 lines, all fields present on every record except memo. Currency is an attribute of the invoice, not of the line, so a line's currency is its parent's.

Sample. Three currencies, with USD, EUR and VND. The VND amounts are in the same range of digits as the others, which for a currency worth a tiny fraction of a dollar per unit means the file does not convert, and the three must not be added. That observation alone changes the question: "in US dollars" means the USD invoices, not a conversion.

Choose. One total and one ranking, with a possible follow-up. That is two streaming aggregates, or one load and two queries. Given that the likely next question is "and by month", I load the tables.

Answer. The USD revenue is 4,579,242.21 over 30,245 lines. For the ranking, the revenue per customer is a join and a group, and the customer with the most invoices is a count; in this generated file the invoice counts are nearly flat (the top three customers have 23 invoices each), so "biggest" has to be defined: by revenue, by count, or by number of lines.

Prove. The USD figure from the streaming script, from the tree loop and from SQL is the same to the cent, and the 30,245 lines match xmllint's count of USD lines. The skipped count is zero and there are no duplicate ids.

Report. "USD invoices total 4,579,242.21 across 30,245 lines in 20,000-invoice file invoices.xml (4.4 MB). EUR and VND are reported separately. Biggest customers are listed by revenue; counts of invoices per customer are nearly equal, so the ranking by revenue is the informative one." That is the answer plus the caveat that changed it. Writing the caveat is the part that takes thought, and it is the part that stops a wrong decision.

The whole exercise took a few minutes of tool time, and most of the work was in the first three steps and the last two. The code in the middle is the shortest part. That ratio is typical, and it is why the order of the method matters more than the choice of library.

Comparing documents

A question that is not about totals is "what changed". Two exports of the same system, a config before and after. A byte diff is noisy, because attribute order, indentation and comments change without meaning. Compare canonical forms, then decide whether child order matters; the layers and the code are in comparing two XML documents for differences. For data exports, the better comparison is by key after loading both into tables: a full outer join on the record id gives you added, removed and changed records without any tree logic.

Special shapes

Sitemaps. A sitemap is a table of URLs and dates, and a few queries audit it: completeness of lastmod (4,285 of 5,000 present in the test file), section sizes, staleness and duplicates (analyzing sitemap data with SQL).

Build files. A pom and a manifest answer dependency and permission questions across hundreds of repositories, with namespace traps in each, and without the resolution the build tool does (analyzing Android and Maven XML files).

Feeds. Podcast and blog feeds give cadence, duration and size with RFC 822 dates and two duration formats.

Public data. Government and central bank files carry namespaces, attributes and a documentation page that lags the file. Save the file with its date in the name so that a figure can be reproduced.

Reporting the result

An answer is a number and a statement of how you got it. I write four lines under every figure: the source file with its date and size (and checksum if it matters), the method in one sentence, the control total it matched, and what was excluded or skipped, with a count. That is what lets someone else repeat it, and it protects you when the sender says the file was different. For a recurring question, keep the script, the profile output and the answer together in the repository, and rerun the profile each time. A change in the path counts is the earliest sign that the sender changed their export.

Where to read next

The spokes in this cluster: counting elements and attributes in an unknown XML file, summarizing a large XML export with a streaming parser, extracting a field from thousands of XML files, analyzing sitemap data with SQL, analyzing Android and Maven XML files, comparing two XML documents for differences, reading financial and government XML datasets and analyzing podcast and blog feeds. For the step after analysis, see turning XML feeds into relational tables and converting XML to JSON, CSV, YAML and Excel. The XPath functions used in the one-liners are in XPath functions worth learning for everyday XML work.

The sources: the XPath 1.0 specification for what the one-liners can do, the lxml parsing guide for streaming, the DuckDB Python documentation for registering DataFrames, and the Namespaces in XML recommendation for the trap that cost the most time. Try a path in the XPath tester.

When to convert the XML first

The question will be asked again, differently. One question deserves a script. A dozen deserve tables. Convert once, with the reconciliation, and use SQL afterwards.

The XML is deeply nested or document-like. If the answer depends on structure at several levels, a flattening step is an analysis in itself, and it should be a deliberate one with its own checks.

Other people will explore it. Analysts who live in spreadsheets or BI tools will be better served by a CSV or a database table than by a script, as long as you give them the control totals.

The file arrives regularly. A recurring feed needs a load, with staging, rejects and reconciliation, not an analysis script run by hand. See the loading cluster, starting with turning XML feeds into relational tables.

The source of truth is elsewhere. If the XML is an export of a database you can query directly, ask the database. The export is a copy, and it may be a day old or incomplete.