SQLite cannot import XML directly with its built-in .import command. Parse the XML into records first, then insert those records into SQLite. For small files, Python’s xml.etree.ElementTree can load the document as a tree; for large files, iterparse() lets you process records incrementally.
Why XML needs a parsing step
SQLite’s command-line .import command is for CSV or similarly delimited data, not XML. SQLite describes it as a way “to import CSV (comma separated value) or similarly delimited data into an SQLite table.” See the SQLite Command Line Shell documentation. XML must first be parsed and mapped to rows and columns.
A reliable workflow is to identify the XML records and fields, design a matching schema, convert values to appropriate SQLite types, and insert them with parameterized statements. This makes field mapping explicit and avoids treating XML markup as if it were delimited text.
Plan the table structure before importing
Map repeating elements to rows
Inspect the document to find the element that repeats for each record. For example, if each <country> element represents one record, its attributes and scalar child elements can become columns in a country table.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →#1 Best Overall
Put repeated nested elements in a related table
Scalar values belong in the parent table. If a record contains a collection—such as multiple phone numbers or addresses—store those entries in a separate child table with a foreign key pointing to the parent row. Flattening a collection into one text column makes individual child values harder to validate and query.
Choose explicit types and constraints
Define columns with suitable SQLite types and add a primary key, uniqueness rules, and indexes that reflect how the data will be queried. Decide how to handle missing or optional XML fields: a missing value generally maps to SQL NULL, while text can be trimmed and dates or numbers converted before insertion. Resolve namespace-qualified element names deliberately rather than assuming unqualified names.
Rank #2
Keep the original XML only when it is needed for audit purposes or when fields have not yet been modeled. Otherwise, storing the parsed values in a clear relational structure makes later queries simpler.
Import a small XML file with Python
Python’s standard-library xml.etree.ElementTree can parse a file with ET.parse() or an in-memory string with ET.fromstring(). The example below assumes a document whose root contains repeated country elements with a name attribute and optional year and rank children.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Rank #3
import sqlite3
import xml.etree.ElementTree as ET
con = sqlite3.connect('data.db')
con.execute('''
CREATE TABLE IF NOT EXISTS country (
id INTEGER PRIMARY KEY,
name TEXT,
year INTEGER,
rank INTEGER
)
''')
rows = []
for country in ET.parse('country_data.xml').getroot().findall('country'):
year_text = country.findtext('year')
rank_text = country.findtext('rank')
rows.append((
country.get('name'),
int(year_text) if year_text else None,
int(rank_text) if rank_text else None,
))
with con:
con.executemany(
'INSERT INTO country(name, year, rank) VALUES (?, ?, ?)',
rows,
)
con.close()The XML names and schema in this example are illustrative; adapt the element names, types, and constraints to the actual file. The explicit column list in the INSERT statement makes the mapping visible and avoids relying on table column order. Python’s sqlite3 module supports parameterized data-modification statements and executemany() for repeated inserts; see the Python sqlite3 documentation.
The with con: block runs the inserts in a transaction: successful work is committed when the block completes, and an exception causes the transaction to be rolled back. Use placeholders such as ? and pass values separately; do not construct SQL by concatenating XML text.
Import a large XML file incrementally
ET.parse() builds the full XML tree in memory. For a large file made up of repeated records, use ET.iterparse() to process completed elements and clear them after extracting their values. ElementTree documents both whole-file and incremental parsing in its official documentation.
import sqlite3
import xml.etree.ElementTree as ET
con = sqlite3.connect('data.db')
con.execute('''
CREATE TABLE IF NOT EXISTS country (
id INTEGER PRIMARY KEY,
name TEXT,
year INTEGER,
rank INTEGER
)
''')
with con:
for event, elem in ET.iterparse('country_data.xml', events=('end',)):
if elem.tag == 'country':
year_text = elem.findtext('year')
rank_text = elem.findtext('rank')
row = (
elem.get('name'),
int(year_text) if year_text else None,
int(rank_text) if rank_text else None,
)
con.execute(
'INSERT INTO country(name, year, rank) VALUES (?, ?, ?)',
row,
)
elem.clear()
con.close()Processing on the end event ensures the record’s children are available for extraction. The example inserts one row per completed record. If throughput matters, accumulate a bounded batch of rows and call executemany() for each batch rather than retaining every row until the document ends. The record comparison assumes unqualified tags; namespaced XML requires matching the namespace-qualified tag or using ElementTree’s namespace support.
Free tools Windows power users keep installed
One-click scans. No signup required.
Best Value
Represent nested XML as relational rows
Suppose each parent record has multiple child items. Create a parent table with a primary key and a child table with a foreign key. Insert the parent first, obtain its generated key, then insert each child with that key. This preserves the one-to-many relationship instead of losing it in a flattened value.
CREATE TABLE person (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE phone (
id INTEGER PRIMARY KEY,
person_id INTEGER NOT NULL REFERENCES person(id),
number TEXT NOT NULL
);During parsing, extract the parent’s scalar fields and child collection separately. Insert the parent, retrieve its identifier with SQLite’s last-insert mechanism, then insert the child values using parameterized statements. Keep related inserts in the same transaction so an incomplete record does not leave orphaned or partial data.
Validate the import
Do not treat a successful script run as proof that every record was mapped correctly. Check the source and destination counts, verify required fields and uniqueness rules, and inspect representative parent-child joins.
- Compare the number of source record elements with the number of inserted parent rows.
- Check for missing values in fields that should be required and confirm that optional fields became
NULLas intended. - Look for duplicate values where a uniqueness constraint should apply.
- Query a few parent records together with their related child rows to confirm that nested values were associated correctly.
Choosing a parsing and import approach
| Approach | Best fit | Trade-off |
|---|---|---|
Python with ET.parse() |
Small XML files where a complete tree fits comfortably in memory | Simple traversal, but the parsed document occupies memory as a tree |
Python with ET.iterparse() |
Large documents with records that can be handled incrementally | Lower memory use when processed elements are cleared; requires event-based parsing logic |
sqlite-utils |
Cases where a higher-level tool can reduce custom glue code | It is external software, not a built-in SQLite XML feature; its documented XML import route uses ElementTree. See sqlite-utils XML import documentation. |
Custom Python is a good fit when the schema, validation, and relationships need to be explicit. A higher-level tool can reduce implementation work when its mapping matches the file, but it does not remove the need to understand the XML structure and verify the resulting rows.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsQuick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
