Loop import XML files

Question

I'm using MariaDB to import some files in XML. Here is a snippet of the code I'm using:

CREATE TABLE invoices (
    InvoiceNumber VARCHAR(20),
    InvoiceStatus CHAR (1),
    InvoiceDate CHAR (10),
    Period CHAR (2)
)
;

DROP TABLE if EXISTS temptbl;

create table temp02 (
    InvoiceNumber VARCHAR(20) xpath='InvoiceNo',
    InvoiceStatus CHAR(1) xpath='DocumentStatus/InvoiceStatus',
    InvoiceDate CHAR (10) xpath='InvoiceDate',
    Period CHAR (2) xpath='Period'
)
engine=CONNECT table_type=XML file_name='..\\importmaridb\\month01.xml'
tabname='AuditFile' option_list='rownode=SourceDocuments/SalesInvoices/Invoice';

INSERT INTO invoices
SELECT * FROM temptbl;

I then repeat 12x the code of importing to table "temptbl" changing only the file name to reflect the other months.

I would like to have a loop to iterate the files each time. I believe part of the solution would be to create a table with the file names and an auto increment column, where I would loop through the numbers. I've attempted to define a variable and substituting in the code, like file_name=@path. But MariaDB gives me an error a syntax error.

| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME |
+---------------+----------------+---------------+--------------------+
| path          | ..\importmaridb| VARCHAR       | utf8mb4            |
|               | \month01.xml   |               |                    |

Can someone give me some pointers, and even if this is possible?

Loop import XML files

0 Answers0