xml2sql

Program that transforms XML files into relational database

Requirements

• At least python 3.9
• pip install -r requirements.txt

Usage

usage: xml2sql.py [-h] [--files FILES [FILES ...]] [--config CONFIG_FILE] [-o OUTPUT_FILE]

Python program to transform XML into relational database (SQLite)

options:
  -h, --help            show this help message and exit
  -f FILES [FILES ...], --files FILES [FILES ...]
  --config CONFIG, -c CONFIG
  -o OUTPUT, --output OUTPUT

Example : python xml2sql.py -f input/example.xml -c config/example.ini -o output.db

Config file

Exemple from example.ini

[DEFAULT]
; Ignoring a markup means not creating any table for it.
; ignore = markup1 markup2 markup3
ignore = Environments Parameters Properties Applications Jobs

; Each markup will be joined to the primary keys of its
; parent. Therefore it's important that each markup
; that has children and that is not ignored have a primary key.
; In this case, the primary key is either chosen attributes of 
; the markup or a primary key generated by default.
[primary_keys]
; markup = attribute1 attribute2 attribute3

How it works

Each markup name as will have its own table and for each markup, the relation child-parent will be represented as a primary key-foreign key relation. For example, the following XML file :

<APP name="app1">
  <job name="job1">text</job>
</APP>
<app name="app2">
  <job name="job2"></job>
</app>

Will generate these two SQL tables :

• app with the columns sql_index (default primary key), name (the attribute), xml_text (the text inside markups) and xml_file(the file name)
• job with the columns sql_index, app_sql_index (foreign key), name, xml_text, xml_file

These tables will have the following values :

app:

sql_index    name   xml_text   xml_file
-----------------------------------------
1            app1     ' '     example.xml
2            app2     ' '     example.xml

job:

sql_index  app_sql_index  name   xml_text   xml_file
-------------------------------------------------------
1            1            job1   'text'     example.xml
2            2            job2   ''         example.xml

Technical details

• Every markup name will be in eventually changed in lowercase.
• Each table will have a column to specify the file where it originates from.
• If two markup with the same name have different attributes, all the attributes will be present in the table and won't have any value if it was not specified.
• Each node has a unique ID which can be an attribute if defined in the [primary_keys] of the config file or an autoincrementing index by default sql_index.