Building a database from XML

Tools → Recreate DB from XML takes an XML document, works out the relational structure hiding in it, and builds a database from it — tables, columns, types, relationships and data.

This is for the case where a system hands you a large XML export — a product catalogue, a medical or pharmaceutical dataset, a partner's data dump — and you want it as a real database rather than as a file.

The three steps

1. Setup

• XML file — pick the document.
• Target connection — where the database will be created.
• Target database — the database name. For SQLite this is the connection's database file.

2. Preview

NabuSQL analyses the document and shows what it found: the tables it will create, their columns and the inferred types. The generated DDL can be inspected before anything runs.

Read this step properly. It is where you find out that a field you expected to be numeric was inferred as text because one record in fifty thousand contains a stray character.

3. Run

The import runs with per-table progress. When it finishes you get a per-table result: rows loaded, and any errors.

How it works

Two things about the implementation matter in practice:

• Streaming, two-pass parsing. The first pass determines the structure, the second loads the data. The document is never held in memory in full, so file size is limited by disk rather than by RAM.
• Foreign keys are applied after the data is loaded. Records are frequently ordered so that a child arrives before its parent; creating the constraints up front would reject perfectly good rows. The constraints go on at the end, once every row is in place.

Afterwards

• Check the inferred types in Tables and columns and tighten anything that came out wider than it needs to be.
• Open the ER diagram to see the structure that was derived.
• Add indexes for the columns you will actually query — the import creates keys and constraints, not the indexes your reporting needs.

If a table comes out wrong

The structure follows the document. Repeating elements become tables, attributes and child elements become columns. When an element is inconsistent across records, the analysis widens the type to accommodate every value it saw. Fixing that after the load — one ALTER TABLE on a known-good dataset — is usually quicker than reshaping the XML.