Joining XML Derived Tables to Your Own Data Safely

You parsed the XML into a table. Now the interesting part: combine it with what you already know about the world. Which region did each order come from? Which segment is this customer in? The join is one line of SQL. It is also where reports lose rows without a trace, because an inner join that fails to match leaves the row out. This post runs the join, shows what went missing and how to find it, and gives the habit that catches it every time.

The two tables

The first comes from the XML: one row per order line, with the customer's name copied from the order, a quantity and a decimal price. The second is a customer reference, a CSV maintained by someone else:

customer,region
customer-0,north
...
Customer-42 ,east
Customer-43 ,west
...

Customers 42 and 43 are spelled Customer-42 and Customer-43 , with a capital letter and a trailing space. A person reading the file sees nothing wrong. A join does.

The join that looks fine

Load the lines into DuckDB (see using DuckDB to read XML through Python), load the CSV and join:

SELECT count(*), sum(l.qty*l.price) FROM lines l JOIN cust c ON l.customer = c.customer;

And first, the baseline for the XML-derived table alone:

lines [(300251, Decimal('751472561.35'))]
naive inner join: [(298971, Decimal('748233254.05'))]

The join returned 298,971 rows and 748,233,254.05 where the table has 300,251 rows and 751,472,561.35. It lost 1,280 lines and 3,239,307.30 of revenue, about 0.4 percent, with no error, no warning and a result that looks perfectly reasonable. If this number goes into a regional report, every region's revenue is slightly low and nobody knows. This is the standard failure of inner joins: a row that does not match is not an error, it is an absence.

Find what did not match

A left join keeps every row from the left table and shows nulls for missing matches. Filter to the nulls and group by the key:

SELECT l.customer, count(*)
FROM lines l LEFT JOIN cust c ON l.customer = c.customer
WHERE c.customer IS NULL
GROUP BY 1 ORDER BY 1;

unmatched customers: [('customer-42', 638), ('customer-43', 642)]

Two customers, with 638 and 642 lines. 638 + 642 = 1,280, the number of lost rows. The sums reconcile exactly with what vanished, which is how you know the explanation is complete and not just plausible. Now look at both spellings side by side, with the characters made visible (SELECT customer, length(customer) or printing repr), and the difference is plain: a capital C and a trailing space.

This query, the anti-join, belongs in every pipeline that joins data from two sources. Run it, report the count of unmatched rows and keys, and fail the job above a threshold you choose. A join that reports "1,280 lines unmatched in 2 customers" is a join you can reason about.

Normalize the keys

Fix it by normalizing both sides to the same form before comparing:

SELECT count(*), sum(l.qty*l.price)
FROM lines l JOIN cust c ON lower(trim(l.customer)) = lower(trim(c.customer));

normalized join: [(300251, Decimal('751472561.35'))]

All 300,251 rows and the full 751,472,561.35. The by-region report now adds up:

[('east', Decimal('197891719.30')), ('north', Decimal('178350782.24')), ('south', Decimal('177836364.50')), ('west', Decimal('197393695.31'))]

The four regions sum to the same 751,472,561.35 as the XML table alone, which is the property you want from a join that only attaches an attribute: row counts and totals must not change. Make that a test. After any join intended only to add columns, assert that the row count and the key sums equal those before the join. A violation means either lost rows (an inner join dropped them) or duplicated rows (the right table has two rows for a key).

Where keys drift

Case and whitespace are the common ones. Others to look for:

• Leading zeros. 00123 in one source and 123 in another, after a spreadsheet touched one. Compare as text with a fixed width, or as integers, consistently.

• Different identifiers for one entity. A customer number in the XML and an email in the CSV. No normalization helps; you need a mapping table.

• Unicode forms. An accented letter as one character or as a base letter plus a combining mark. They look identical and compare unequal. Normalize to NFC.

• Trailing and doubled whitespace, non-breaking spaces. trim removes ordinary spaces and not necessarily  . Replace it first.

• Duplicates in the reference table. If cust had two rows for one customer, the join would return two rows for every line of that customer and inflate the sum. The check for duplicate keys in the reference table is SELECT customer, count(*) FROM cust GROUP BY 1 HAVING count(*) > 1.

• Time-varying attributes. A customer who changed region last year. A join on the key alone attaches today's region to old orders; a correct join also uses the date.

Normalize once, and record it

Putting lower(trim(...)) in every query is a repair that spreads. Better: add a normalized key column when you load, customer_key, computed in one place with a documented rule, and join on that. Keep the original text beside it. Then a change to the rule is a change in the loader, the join is ON l.customer_key = c.customer_key everywhere, and the unmatched-keys report names the originals so a human can fix the source.

If the unmatched ones are real (a customer who is not in the reference table), the answer is not normalization. It is a decision: report them as a category called "unknown region", exclude them with a count, or send them to whoever owns the reference table. What you must not do is let them vanish.

Related reading

This post is part of answering questions from XML data. The parsing half is in flattening repeating XML elements into rows. For the same hazard with spreadsheets, see how CSV quoting works and the CSV hub everything a CSV file does and does not promise you. DuckDB documents joins and read_csv in its SQL reference, and Unicode normalization in the Unicode Standard Annex 15. Inspect a record in the XML formatter.

When a join cannot be repaired by normalizing keys

The keys are different identifiers. Case and whitespace repairs do not help when one source says C-42 and the other says an email. Build and maintain a mapping.

The reference table is wrong. If customers are missing or duplicated there, the join exposes it and the fix is in the source.

The match is fuzzy. Company names with abbreviations and typos need record linkage tools with a threshold and a review step. Then a match is a probability, and a report should say so.

You cannot tell which side is right. When an XML value and your own table disagree on a fact, such as a region, decide a rule for precedence and record it rather than choosing in each query.