Counting Elements and Attributes in an Unknown XML File

Someone sends you an XML file and says "can you have a look". It is 400 MB, or 4 MB, and you have never seen it. Opening it in an editor tells you about the first screen. The question you actually have is structural: what are the records, what do they contain, what is always there, what is rare, and how deep does it go. A profile that counts every element and attribute path answers all of those in one pass, and it is the first thing I run on any unfamiliar file.

The script

import sys, collections, time, resource
from lxml import etree

paths = collections.Counter()
attrs = collections.Counter()
maxdepth = 0
stack = []
t = time.time()
for ev, el in etree.iterparse(sys.argv[1], events=("start", "end")):
    if ev == "start":
        stack.append(el.tag)
        p = "/" + "/".join(stack)
        paths[p] += 1
        maxdepth = max(maxdepth, len(stack))
        for a in el.attrib:
            attrs[f"{p}/@{a}"] += 1
    else:
        stack.pop()
        el.clear()
for p, n in sorted(paths.items()): print(f"{n:7}  {p}")
for p, n in sorted(attrs.items()): print(f"{n:7}  {p}")
print("max depth", maxdepth, f"{time.time()-t:.2f}s", "peak MB", resource.getrusage(resource.RUSAGE_SELF).ru_maxrss // 1024)

It asks iterparse for both start and end events. On a start event it pushes the tag onto a stack, builds the full path from the stack, and counts the path and each attribute under it. On an end event it pops the stack and clears the element, so nothing accumulates. Counting the full path, not the bare tag, matters, because name under customer and name under product are different things, and a profile that merges them is misleading. A short note on a detail: at the start event the element's attributes are available but its children and text are not, which is all the script needs.

The profile

      1  /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

Eleven lines for a file with 160,000 elements, in 0.23 seconds, peaking at 21 MB. Here is what each part tells you.

The record. invoice has 20,000 occurrences and a single parent. It is the repeating unit, and the thing a table would have one row for. If you are not sure which element is the record, it is usually the first one whose count is large and whose parent has a count of 1.

What is required. customer, due, @cur and @id have exactly 20,000 occurrences, the same as invoice. A child that appears once for every parent is required in practice, which is as close to a certainty as a sample file offers. (It could still be optional in the format and always present in this export. A schema or documentation settles it.)

What repeats. line has 49,819 occurrences under 20,000 invoices: it repeats, with an average of 2.49 per invoice. That ratio is the first sign that a table needs two levels (flattening repeating XML elements into rows). The amount and the @qty and @sku counts match line exactly, so each line has all three.

What is rare. memo appears 400 times, in 2 percent of invoices. It is optional and sparse, so it belongs in a nullable column and not in anything you rely on. A rare element is also where surprises live: look at the 400 examples before you decide what it means.

How deep. The maximum depth is 4 (invoices, invoice, line, amount). A shallow tree maps onto tables easily, and a depth of 20 tells you the data is a document, not records.

Reading it as a table design

The profile is nearly a table design. Two tables, invoice and invoice_line. The invoice table gets id, cur, customer, due and a nullable memo. The line table gets the invoice id, sku, qty and amount. The counts give you expected row numbers (20,000 and 49,819) to reconcile against after the load. You still need the types and ranges, which the profile does not give, and which the next step supplies by looking at values.

Extending the profile

A few additions are worth having, depending on the file.

Distinct values for low-cardinality fields. For attributes like cur, count the values: collections.Counter(el.get("cur")). A field with five distinct values over 20,000 records is an enumeration, and the count tells you if a value is a typo of another. For this file, the currency counts came out as USD, EUR and VND in the ratio of the generator.

Length and range. Track the maximum text length per path (for sizing varchar columns) and the minimum and maximum of numeric ones. One streaming pass can hold a dictionary of these.

Empty and missing. Count how often an element's text is empty or whitespace. This is the same absent-versus-empty distinction as in handling optional XML elements and missing values, and a profile is the cheapest way to learn which one the producer uses.

Namespaces. el.tag is in Clark notation, {uri}name, so the profile shows the namespace automatically. A path full of {http://...} prefixes is a hint that your queries will need a prefix map.

First and last example. Keep the line number (el.sourceline) of the first occurrence of each path, so you can jump to an example in the file.

Command-line shortcuts

For a small file, XPath on the command line gives a count without a script:

xmllint --xpath 'count(//invoice)' invoices.xml
xmllint --xpath 'count(//memo)' invoices.xml

xmllint builds the whole tree, so this is for files that fit in memory. grep -c '<invoice ' counts lines, not elements, and is wrong when several elements share a line or an element spans lines, so I do not use it for anything I will quote. For big files, the streaming script above or xmllint --stream are the options. The XPath count is useful as a cross-check: for the file above count(//line) returned the same 49,819 as the profile, which is the kind of agreement you want before trusting a profile.

What the profile does not tell you

It says nothing about values. A due element is present in every invoice, and the profile has no idea whether the dates are valid, in one format or the same on every row. It does not say whether the 20,000 ids are unique. It cannot find a record that is malformed in a way that does not change the structure (a negative amount, a currency that is not a currency). Those need a second pass with a different question each time.

It also treats the file as one population. If the first half was exported by one version of a system and the second by another, the averages hide the split. When a count seems off, profile the first and last thousand records separately.

Related reading

The same technique feeds the next step in summarizing a large XML export with a streaming parser, and the hub for this cluster is analysis on XML data when nobody has converted it yet. For the loader that comes after, see importing an XML export from another system. The streaming and clearing idiom is described in the lxml iterparse documentation, and the tag notation in the lxml tutorial on namespaces. Use the XPath tester to try a path before you count it.

When counts are not enough to understand the file

The file is a document. In prose-like XML (DocBook, TEI, a legal text) the structure is a hierarchy of mixed content, and counts of paragraphs and sections say little. Read a section.

The file has several record types. A single profile mixes them. Split by the discriminating attribute or element, and profile each type.

You need correctness, not shape. Check against a schema (validating XML against an XSD schema) or write assertions about the values.

The file is huge and the first records are not representative. Profile a random sample as well, or the whole file if the time is acceptable. A profile of 0.23 seconds for 4.4 MB scales to roughly a minute for a gigabyte on the same machine.