Flattening XML in Spark and Databricks With Native Reader

Spark has been the default tool for parsing XML in data platforms for years, first through the spark-xml library and, since Spark 4.0, through a built-in reader. The reader turns a file of repeating records into a DataFrame, and from there the usual explode-and-select flattening applies. This post runs it on the orders file used in the rest of this cluster, shows the schema it infers and the one default that corrupts money, and gives the pattern for XML stored as strings.

Reading the file

The reader needs to know which element is a row. Everything else is inferred:

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.master("local[4]").getOrCreate()
df = spark.read.format("xml").option("rowTag", "order").load("/data/o100k.xml")
df.printSchema()

root
 |-- _id: long (nullable = true)
 |-- _status: string (nullable = true)
 |-- customer: string (nullable = true)
 |-- lines: struct (nullable = true)
 |    |-- line: array (nullable = true)
 |    |    |-- element: struct (containsNull = true)
 |    |    |    |-- _qty: long (nullable = true)
 |    |    |    |-- _sku: string (nullable = true)
 |    |    |    |-- price: double (nullable = true)
 |-- placed: date (nullable = true)

The mapping is regular. XML attributes become fields with an underscore prefix (_id, _status, _qty, _sku), child elements become fields without it, and a repeated child becomes an array of structs, here lines.line. The wrapper lines is a struct that holds the array, as in the XML. The reader inferred placed as a date, _id and _qty as long and price as double by looking at the data. Inference reads the data twice, which is a cost on big files and a reason to supply a schema in production.

Flattening with explode

The array becomes rows with explode, and the struct fields become columns:

lines = (df.select(F.col("_id").alias("order_id"), F.col("_status").alias("status"),
                   F.explode("lines.line").alias("l"))
           .select("order_id", "status", F.col("l._sku").alias("sku"),
                   F.col("l._qty").alias("qty"), F.col("l.price").alias("price")))
lines.show(3)
print("rows", lines.count())

+--------+------+-------+---+------+
|order_id|status|    sku|qty| price|
+--------+------+-------+---+------+
|       1|  paid|SKU1187|  9|124.37|
|       2|  paid|SKU0615|  2|569.38|
|       2|  paid|SKU6852|  2|316.44|
+--------+------+-------+---+------+
rows 300251

Order 2 has two lines and produces two rows, order 1 has one. The row count, 300,251, matches the number of lines found by the other engines on this file. That cross-check is worth keeping in any pipeline: the count of <line elements in the source against the rows in the output. explode drops orders with no lines; use explode_outer if you need an order with an empty list to survive as one row with nulls.

The double precision trap

Group by status and sum the revenue:

+-------+------+--------------------+
| status|     n|             revenue|
+-------+------+--------------------+
|   open|101594| 2.543324211699986E8|
|   paid|100600| 2.518637669099997E8|
|shipped| 98057|2.4527637327000093E8|
+-------+------+--------------------+

The revenue figures carry the artifacts of binary floating point: 2.543324211699986E8 is not 254332421.17. The inference chose double for price, because 124.37 looks like a floating point number, and every sum inherits the rounding. For money this is wrong, even if the error is a fraction of a cent, because totals should reconcile exactly with the source and with other systems.

The fix is to give the reader a schema with a decimal type:

schema = ("_id long, _status string, customer string, placed date, "
          "lines struct<line: array<struct<_qty int, _sku string, price decimal(10,2)>>>")
df = spark.read.format("xml").option("rowTag", "order").schema(schema).load("/data/o100k.xml")

+-------+------+------------+
| status|     n|     revenue|
+-------+------+------------+
|   open|101594|254332421.17|
|   paid|100600|251863766.91|
|shipped| 98057|245276373.27|
+-------+------+------------+

The totals are exact, 254332421.17 for open orders, and they add up to 751,472,561.35, the value PostgreSQL and DuckDB computed on the same file. An explicit schema also skips the inference pass and fails fast when a value does not fit, depending on the reader's mode setting (PERMISSIVE nulls the field and keeps the row, FAILFAST raises). Choose deliberately: for finance data, fail.

XML as a string column: from_xml

Not every platform lands XML as files read directly. A lake often stores each document or record as text in a column, and you parse it later. Spark's from_xml does that with a declared schema:

raw = spark.createDataFrame([('<order id="7" status="paid"><customer>Ana</customer><lines><line sku="A" qty="2"><price>3.50</price></line></lines></order>',)], "payload string")
parsed = raw.select(F.from_xml("payload", schema_for_one_order).alias("o"))
parsed.select("o._id", "o.customer", F.explode("o.lines.line").alias("l")) \
      .select("_id", "customer", "l._sku", "l._qty", "l.price").show()

+---+--------+----+----+-----+
|_id|customer|_sku|_qty|price|
+---+--------+----+----+-----+
|  7|     Ana|   A|   2| 3.50|
+---+--------+----+----+-----+

The same flattening applies once the string is a struct. This is the pattern for storing raw XML in a lake and parsing later: keep the original text for audit, and make the typed table a view or a derived dataset.

Practical notes

Startup cost. The whole job above took 10.9 seconds on four local cores, including starting Spark and the schema inference pass, for a file that the Python streaming loop in this cluster parses in about one second. Spark pays off when the data does not fit one machine, when it already sits in a cluster's storage, or when the transformation joins against other large tables. For one file on a laptop, a streaming script is faster to write and to run (parsing big XML files fast).

Splitting. A single large XML file is hard to read in parallel because the reader must find record boundaries. As I understand the reader's design, it splits a file by byte range and resynchronizes at the next row tag, which suits well-formed record files, but I did not test splitting. Many medium files are the safer layout for parallel reads.

Namespaces and attributes. Prefixed names are kept, and options control the attribute prefix and the value tag for elements that have both attributes and text. Read the options table for your version.

Corrupt records. Use PERMISSIVE mode with a columnNameOfCorruptRecord to keep bad rows for inspection.

Checking the result

A flattening job is correct only if you can prove it did not lose or invent rows, and Spark makes that easy. Run three checks after every load. First, compare the number of rows with the count of <line tags in the source: grep -o '<line ' file.xml | wc -l gives 300,251 for this file, and lines.count() printed the same number. Second, compare one aggregate with a calculation from a different engine. The revenue by status here matched the figures from PostgreSQL and DuckDB to the cent once the decimal schema was in place. Third, look for nulls where none are allowed, with a query that counts rows whose key columns are null. A reader in permissive mode converts a value that does not match the declared type into null and carries on, so null counts are where silent type problems show up.

On Databricks, the same reader sits behind Auto Loader and Delta tables, so the usual pattern is to land the XML files in cloud storage, read them with the declared schema, and write the flattened result as a Delta table. The checks run in the same notebook. Keep the schema string in version control next to the job, since it is the contract with the feed.

Related reading

This post is part of XML in warehouses and lakes. Other engines read the same data in loading XML into Snowflake with native support and using DuckDB to read XML through Python. The Spark documentation for the reader is the XML data source guide, and the original library it grew from is spark-xml. Look at the structure of a record in the XML formatter.

When Spark is more than you need for XML

The data fits on one machine. A streaming Python script or DuckDB is simpler and often faster for files up to several gigabytes.

You need strict validation. The reader checks well-formedness and types you give it, not an XSD. Validate upstream.

Latency matters. A cluster job has seconds of startup. For per-request parsing, use a library in the service.

You are not already running Spark. Standing up a cluster to parse one feed is the long way around.