Building a database from XML or JSON

Tools → Recreate DB from XML / JSON takes an XML or JSON 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 export — a product catalogue, a medical or pharmaceutical dataset, an API dump, a partner's data file — and you want it as a real database rather than as a file.

Four formats are accepted: .xml, .json, and line-delimited JSON as .jsonl or .ndjson. The format is picked from the file extension; everything after the initial analysis is shared, so the preview, the generated DDL and the load behave the same whichever you started from.

The three steps

1. Setup

• XML / JSON 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 the structure is derived

From XML

Repeating elements become tables. Attributes and element text become columns. An element that appears only once inside its parent is folded into that parent as prefixed columns — a <meta><author>…</author></meta> block becomes a meta_author column rather than a one-row table of its own.

From JSON

The same idea, expressed in JSON's vocabulary:

In the document	In the database
Key holding a scalar	Column on that object's table
Key holding an object	Folded into the parent as prefixed columns
Key holding an array	Child table with a foreign key to the parent
Bare scalar inside an array	The table's _text column

JSON states one thing more clearly than XML can: an array means "many" even when this particular file happens to contain a single element, so it becomes a table regardless of its length. XML has to infer the same thing from a tag repeating, which means a one-off child element looks identical to a collection that happens to have one member.

A top-level array is treated as a list of root records. A top-level object is a single root record. With .jsonl / .ndjson, every line is one root record and blank lines are skipped.

Since JSON has no root tag to name things after, the file name supplies the root table name — catalog.json produces a catalog table.

How it works

Three things about the implementation matter in practice:

• Two-pass parsing. The first pass determines the structure, the second loads the data.
• Memory. XML and line-delimited JSON are streamed, so file size is limited by disk rather than by RAM. A plain .json file is a single JSON value and has to be parsed as a whole, so a very large one is bounded by memory. If you control the export, ask for .ndjson.
• 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. When a field 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 source file.

Booleans are a common surprise: JSON true / false are stored as 1 / 0 so the column comes out as an integer rather than as text.