Metadata-Version: 2.4
Name: xml2db
Version: 0.15.1
Summary: Load complex XML files into a relational database, with tables generated from the XSD schema
Author-email: Commission de régulation de l'énergie <opensource@cre.fr>
Project-URL: Documentation, https://cre-dev.github.io/xml2db
Project-URL: Repository, https://github.com/cre-dev/xml2db
Project-URL: Issues page, https://github.com/cre-dev/xml2db/issues
Keywords: xml,xsd,xml-schema,etl,elt,data-engineering,sql,ddl,sqlalchemy,postgresql,mysql,mariadb,sql-server,duckdb
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: Information Technology
Classifier: Intended Audience :: Science/Research
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Programming Language :: SQL
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Topic :: Database
Classifier: Topic :: Scientific/Engineering :: Information Analysis
Classifier: Topic :: Text Processing :: Markup :: XML
Requires-Python: >=3.11
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: sqlalchemy>1.4
Requires-Dist: xmlschema>=3.3.2
Requires-Dist: lxml>=5.1.0
Requires-Dist: pyyaml>=6.0
Provides-Extra: docs
Requires-Dist: mkdocs-material>=9.5.34; extra == "docs"
Requires-Dist: mkdocstrings-python>=1.11.1; extra == "docs"
Provides-Extra: tests
Requires-Dist: pytest>=7.0; extra == "tests"
Dynamic: license-file

# Load XML files into a SQL database with Python

`xml2db` is a Python package that parses and loads XML files into a relational database. It handles complex 
XML files which cannot be denormalized to flat tables, and works out of the box, without any custom mapping rules.

It fits naturally into an [Extract, Load, Transform](https://docs.getdbt.com/terms/elt) pipeline: it 
loads all the content of XML files into a relational data model that stays close to the source data while remaining
easy to query as flat database tables. Parsing XML is slow compared to SQL, so you do not need to decide upfront what
to extract: explore the loaded data, then build more user-friendly tables with [dbt](https://www.getdbt.com/), SQL
views, or stored procedures, and change them later without parsing the files again.

Starting from an XSD schema which represents a given XML structure, `xml2db` builds a data model, i.e. a set of database 
tables linked to each other by foreign keys relationships. Then, it allows parsing and loading XML files into the 
database, and getting them back from the database into XML format if needed.

This package uses `sqlalchemy` to interact with the database, so it should work with different database backends. 
Automated integration tests run against PostgreSQL, MySQL, MS SQL Server and DuckDB. You may have to install additional 
packages to connect to your database (e.g. `psycopg2` or `psycopg` for PostgreSQL, `pymysql` or `mysqlclient` for
MySQL, `pyodbc` for MS SQL Server, or `duckdb-engine` for DuckDB). Through DuckDB, the loaded tables can also be
transformed and exported to Parquet files, Delta Lake or Iceberg.

**Please read the [package documentation website](https://cre-dev.github.io/xml2db) for all the details!**

## When to use xml2db

| Situation | Usually enough | What xml2db adds |
|---|---|---|
| A few fields from flat, record-like XML | `pandas.read_xml`, a short `lxml` script, or the XML functions of your database | Little: these are simpler |
| One repeated record element with shallow nesting | XML readers that turn each record into a row, such as Spark's XML data source or DuckDB XML extensions | Normalized tables, with no nested columns to explode |
| Deep nesting, repeated elements at several levels, large XSD | Hand-written mappings, one per element | Tables, keys and loading code generated from the XSD, no mapping to maintain |
| Recurring feed of files following the same schema | A custom script per feed | The same load for every file, with redundant content stored once |
| Needs not settled yet: which fields, which aggregations | Extracting the fields you expect to need, and parsing the files again when needs change | Everything loaded once: new questions are answered with SQL |
| All the data must be kept, or written back to XML | Hand-written mappings, with the risk of dropping fields | Every element and attribute kept, with a round trip back to XML |

`xml2db` needs an XSD schema. See the documentation for an [example](https://cre-dev.github.io/xml2db/#from-xml-to-tables)
and for database-specific guides: [PostgreSQL](https://cre-dev.github.io/xml2db/databases/postgresql/),
[SQL Server](https://cre-dev.github.io/xml2db/databases/sql-server/),
[MySQL and MariaDB](https://cre-dev.github.io/xml2db/databases/mysql/),
[DuckDB](https://cre-dev.github.io/xml2db/databases/duckdb/),
[export to files, lakehouses and other databases](https://cre-dev.github.io/xml2db/databases/export/). To transform the loaded
data, see [Transform with dbt](https://cre-dev.github.io/xml2db/dbt/). To only generate SQL tables from an XSD, see
[XSD to SQL tables](https://cre-dev.github.io/xml2db/xsd-to-sql/).

## Installation

The package can be installed, preferably in a virtual environment, using `pip`:

``` bash
pip install xml2db
```

## CLI

After installation, `xml2db` is available as a command-line tool with three subcommands.

Explore your XSD schema and configure the data model interactively in a browser:

```bash
xml2db serve path/to/schema.xsd
```

This opens a page with an Entity Relationship Diagram, source/target tree views, DDL output, and a live YAML config 
editor with autocomplete.

Import an XML file directly from the command line:

```bash
xml2db import file.xml schema.xsd \
    --connection-string "postgresql+psycopg2://user:pw@host/db" \
    --config model_config.yml
```

Render the ERD, trees, or DDL to stdout or a file without starting a server:

```bash
xml2db render schema.xsd --format erd
xml2db render schema.xsd --format ddl --db-type postgresql
```

See the [CLI reference](https://cre-dev.github.io/xml2db/cli/) for all options.

## Python API

Loading XML files into a relational database with `xml2db` can be as simple as:

```python
from xml2db import DataModel

# Create a data model of tables with relations based on the XSD file
data_model = DataModel(
    xsd_file="path/to/file.xsd", 
    connection_string="postgresql+psycopg2://testuser:testuser@localhost:5432/testdb",
)
# Parse an XML file based on this XSD
document = data_model.parse_xml(
    xml_file="path/to/file.xml"
)
# Insert the document content into the database
document.insert_into_target_tables()
```

## Testing

After cloning the repo, install the package with its development dependencies, plus DuckDB and `pytz` to run the
database tests locally:

```bash
pip install -e .[tests,docs] duckdb_engine pytz
```

Run all tests, including database integration tests, against an in-memory DuckDB database:

```bash
TZ="Europe/Paris" DB_STRING="duckdb:///:memory:" python -m pytest
```

Round-trip tests compare datetimes read back from the database in the `Europe/Paris` time zone, so `TZ` must be set to
it.

Integration tests use the database given by the `DB_STRING` connection string. To run them against another database,
for instance PostgreSQL:

```bash
TZ="Europe/Paris" DB_STRING="postgresql+psycopg2://user:pw@localhost:5432/testdb" python -m pytest
```

Continuous integration runs them against DuckDB, PostgreSQL, MySQL and MS SQL Server. To run only the tests that do
not need a database:

```bash
TZ="Europe/Paris" python -m pytest -m "not dbtest"
```

## Contributing

`xml2db` is developed and used at the [French energy regulation authority (CRE)](https://www.cre.fr/) to process complex 
XML data.

Contributions are welcome, as well as bug reports, starting on the project's 
[issue page](https://github.com/cre-dev/xml2db/issues).
