Map XML to SQLite

[pritaeas]

I'm looking to build a command-line tool that will enable me to read an arbitrary XML file combined with a mapping to fill an existing (arbitrary) SQLite database with data. What I'm looking for are conceptual ideas on how to solve this. I've got a possible theoretical solution in mind, but am looking for other ideas to get this done. Hopefully someone will confirm my idea will work, or show me I'm thinking about it in the wrong way. I won't share what I think at this time, to prevent pushing my thought process onto yours.

[Reverend Jim]

Before I start thinking about this (whoops, it's already too late) do you have any initial ideas on how to map the hierarchical structure of XML onto a relational structure (SQLite)? Are there any restrictions on the level of nesting of the XML source?

[pritaeas]

do you have any initial ideas on how to map the hierarchical structure of XML onto a relational structure

What is missing from my statement above is that I do not need the full XML to map automatically. I want to be able to choose which part (node) of the XML should be inserted into a specific table.

Example:

<invoice currency="EUR" type="invoice">
    <invoiceNumber>32</invoiceNumber>
    <invoiceDate>2020-07-20</invoiceDate>
    <dueDate days="30">2020-08-19</dueDate>
    <invoiceIssuer>
        <vatID>NL123456789B01</vatID>
        <address addressId="B">
            <name1>Example Worldwide B.V.</name1>
            <street1>Sample Parc 211</street1>
            <city zip="5758 AS">Neerkant</city>
            <country>NL</country>
        </address>
    </invoiceIssuer>
    <moas>
        <moa type="total" currency="EUR">17.370</moa>
        <moa type="taxable" currency="EUR">0.00</moa>
        <moa type="taxfree" currency="EUR">17.37</moa>
        <moa type="tax" currency="EUR">0.00</moa>
    </moas>
</invoice>

Table/columns

Invoices
- Currency
- Type
- InvoiceNumber
- InvoiceDate
- DueDate
- IssuerVatId
- IssuerAddressId
- IssuerAddressName
- IssuerAddressStreet
- IssuerAddresPostalCode
- IssuerAddressCity
- IssuerAddressCountryCode

Moas
- InvoiceNumber
- Type
- Currency
- Amount

Everything within the invoice (element values and attributes) can map to a single table (nothing is 1-N). The exception here is the moas node, which should be inserted into it's own table, so it should get 4 records.

For the first draft I do not need links between the tables, but if you have something in mind, it is welcomed. That's why I added InvoiceNumber to the Moas table.

All SQLite columns are text/varchar, I do not need specific types at the moment.

Should I create an IssuerAddress table, I want to be able to put the Issuer data into that table.