How to Query XML, YAML and Avro Files with SQL
CSV and JSON are well served by tooling. XML, YAML and Avro are the awkward middle — common enough that you receive them, rare enough that opening one means installing something. All three work here the same way, and the trick is understanding how nesting becomes columns.
The one concept: flattening
SQL wants rows and columns. These formats are trees. The bridge is dot-path flattening: each leaf in the tree becomes a column named after its path.
This JSON-equivalent structure…
customer:
name: Acme Ltd
address:
city: Austin
country: US
…becomes three columns:
| customer.name | customer.address.city | customer.address.country |
|---|---|---|
| Acme Ltd | Austin | US |
In the results panel each header shows a short alias on top with the full path beneath, and you can query either one.
Repeated elements become rows
When a node repeats — several <item> elements inside an order — you get one row per element, with the parent values repeated on each. An order with three items produces three rows that share the same order number. This is what makes aggregation work:
SELECT order_id, COUNT(*) AS item_count, SUM(price) AS order_total
FROM O
GROUP BY order_id
XML
Drop the .xml file in. When the root contains a single repeated child tag, that tag is treated as the record set — so a file of <order> elements gives you one row per order.
<orders>
<order>
<id>1001</id>
<customer><name>Acme Ltd</name><city>Austin</city></customer>
<amount>250</amount>
</order>
</orders>
Query it with the flattened names:
SELECT id, customer_name, customer_city, amount
FROM O
WHERE amount > 100
SELECT * FROM O LIMIT 1 and read the headers. That is faster than reasoning about the XML.YAML
.yaml and .yml both work, and flatten identically. YAML files are often configuration rather than records, which makes them handy for questions like “which of these services has debug enabled?”
SELECT name, image, replicas
FROM S
WHERE replicas > 1
A YAML file holding a single object rather than a list produces one row — still queryable, just not very interesting. The value there is seeing every setting laid out as columns.
Avro
Avro is a binary format with the schema embedded in the file, common in Kafka pipelines and Hadoop estates. Opening one normally means Java tooling or the avro-tools jar.
Here it is read via WebAssembly, in the browser, with no install. Drop the .avro file in and query it like anything else. The embedded schema supplies the column names, and nested records flatten to dot-paths in the usual way.
Open an XML, YAML or Avro file without installing anything.
Open the tool →Turning them into something friendlier
Often the real goal is to hand the data to somebody who cannot open the original. Once loaded, export to CSV, JSON, Excel or Parquet — see converting between formats. XML→Excel and Avro→CSV are two of the more common rescues.
Mixing formats in one query
Format stops mattering once files are loaded. An XML export and a CSV lookup table join exactly as two CSVs would:
SELECT X.order_id, X.amount, C.account_manager
FROM X
JOIN C ON X.customer_code = C.code
Honest limits
- Attributes and mixed content. XML that leans on attributes, or mixes text and child elements in one node, may flatten in a way you did not expect. Check with
LIMIT 1before trusting the shape. - Very deep nesting produces a lot of columns. Select the ones you want rather than
SELECT *. - Encrypted or unusually encoded Avro may not load. You get an error rather than silently wrong data.
- 50 MB and a million rows per file, as everywhere else.