Using DuckDB to Read XML Through Python and Arrow Tables

DuckDB is the tool most people reach for when they have a file and a question: it reads CSV, JSON and Parquet in place, and answers SQL in milliseconds. XML is not on that list in the core engine. That does not make it hard. Python parses the XML, DuckDB queries what comes out, and the two share memory through Arrow, so no copy to disk is needed. There is also a community extension that reads XML directly, and it is worth knowing what it does well and where it guesses.

The route through Python

The plan has three parts: stream the records with iterparse, collect columns into Arrow tables in batches, and register the table with DuckDB.

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

def batches(path, size=100000):
    cols = {"order_id": [], "status": [], "sku": [], "qty": [], "price": []}
    n = 0
    for _, el in etree.iterparse(path, events=("end",), tag="order"):
        oid, st = int(el.get("id")), el.get("status")
        for line in el.iter("line"):
            cols["order_id"].append(oid); cols["status"].append(st)
            cols["sku"].append(line.get("sku")); 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]
        n += 1
        if n % size == 0:
            yield pa.table(cols); cols = {k: [] for k in cols}
    if cols["order_id"]:
        yield pa.table(cols)

table = pa.concat_tables(list(batches("o400k.xml")))
con = duckdb.connect()
con.register("lines", table)
print(con.sql("SELECT status, count(*), sum(qty*price) FROM lines GROUP BY status ORDER BY status").fetchall())

The streaming part keeps the parser's memory flat (parsing big XML files fast explains the clearing lines). Batching the output into tables of 100,000 orders keeps Python lists small and builds Arrow in chunks. The price column holds decimal.Decimal values, so Arrow infers a decimal type and DuckDB receives exact numbers. The output:

parse to Arrow: 7.9s  rows=1199909  arrow_bytes=62MB
duckdb query on the table: 53 ms -> [('open', 402389, '1007988756.66'), ('paid', 401103, '1003578628.72'), ('shipped', 396417, '990411449.62')]
peak_rss=273MB

Parsing took 7.9 seconds, more than the 4.3 seconds of a sum-only loop, because building Python lists and Arrow arrays for 1.2 million rows is real work. The query on the table took 53 milliseconds. The revenues sum to 3,001,978,835.00, the same figure every other engine in this cluster produced. Peak memory of 273 MB is higher than a pure streaming run since the whole 62 MB table is held. To stay flat, write each batch to a Parquet file and let DuckDB query the files (choosing Parquet after you parse XML).

Once the table is registered, everything DuckDB does is available: joins to a CSV on disk, window functions, COPY ... TO 'out.parquet'. The XML is just a source, and the loop above is the only XML-specific code.

The webbed extension

DuckDB's community extensions include webbed, which reads XML and HTML directly. Installing and using it:

import duckdb
con = duckdb.connect()
con.sql("INSTALL webbed FROM community")
con.sql("LOAD webbed")
print(con.sql("SELECT count(*) FROM read_xml('o100k.xml', record_element='line')").fetchall())
print(con.sql("DESCRIBE SELECT * FROM read_xml('o100k.xml', record_element='line')").fetchall())

[(300251,)]
webbed read_xml 100k orders: 2.9s
[('sku', 'VARCHAR', 'YES', None, None, None), ('qty', 'VARCHAR', 'YES', None, None, None), ('price', 'DOUBLE', 'YES', None, None, None)]

It counted 300,251 lines in 2.9 seconds, the right number, with one line of SQL and no Python loop. Three observations. The parameter is record_element, and my first attempt with another name was rejected by the binder, which is a reminder that a community extension's options are not stable across versions. Type inference made sku and qty text and price a double, which is the same trap that Spark's inference fell into: sums of doubles carry rounding noise, and you should cast to DECIMAL(10,2) in the query or avoid the route for money. And with record_element='line' the result has only the line's own fields; the order id and status, which live on the parent, are not part of the rows. Getting them needs a different record element and a nested column handling, so a flat feed where each record is self-contained is the sweet spot.

Use the extension for exploration and for flat feeds where the fields you need are in the record element. Use the Python route when records need fields from ancestors, when you need exact types from the start, when you need namespaces handled your way, or when you want a validation step in the loop.

Doing the join-to-your-own-data step

The reason to put XML in DuckDB is usually to combine it with something else. With the table registered:

SELECT c.region, sum(l.qty * l.price) AS revenue
FROM lines l
JOIN read_csv('customers.csv') c USING (order_id)
GROUP BY c.region;

A join between a parsed XML table and a CSV on disk is one query, with no staging database. This is the case joining XML-derived tables to your own data develops. Remember the usual caution: when the keys come from two systems, check for leading zeros, whitespace and case before trusting the join count.

Writing the loop so it can be trusted

A parse loop that feeds an analysis should be boring and checked. Three small habits do it. Put the record count in the output: after building the table, compare table.num_rows with a grep -o '<line ' count on the source, and stop if they differ. Give every column an explicit Arrow type instead of letting pa.table guess, because a column that happens to hold only whole numbers in one file and decimals in the next will change type between runs and break the downstream query. And decide on the failure policy before the first run. A line with a missing price can raise, which stops the load and names the order, or it can append None and count the rejects. Both are valid, and the second needs a counter that you print at the end.

DuckDB has one more convenience worth using: because the table is registered by name, you can build the whole analysis as views over it, CREATE VIEW revenue_by_status AS SELECT ..., and keep the SQL in a file under version control. The Python part shrinks to a loader that you rarely touch, and the questions live where analysts can read them. When the question set stabilizes, write the table out to Parquet once and stop parsing XML at query time altogether.

Related reading

This post is part of XML in warehouses and lakes. The older version of the same idea is in DuckDB and XML through Python, and a similar route with pandas in converting XML to a pandas DataFrame. The other engines are covered in flattening XML in Spark and Databricks. Reference: the DuckDB Python API and Arrow integration and the community extensions list. Look at a record in the XML formatter.

When DuckDB is not the right way to read XML

The files are very large and the question is simple. A grep count or a streaming sum is faster than building a table. Use DuckDB when you will ask several questions.

You need XSD validation or XSLT. Those belong to the XML toolchain. Validate with xmllint, then load.

The documents are mixed content. A table of text runs and tags is not what DuckDB is good at. Extract the text first.

The data must live in a shared database. DuckDB is excellent for a single analyst's session. For shared tables with concurrent writers, load into PostgreSQL (streaming XML into PostgreSQL without a DOM).