How to Convert XML INSERT statements to SQL with SQLite Studio

[isau]

I just had to convert a bunch of code from XML to SQL for a conversion project, and after converting a bunch of it by hand, decided there had to be a better a way to do the conversion. I figured one out using SQLite Studio. Whether it is hard or easy will depend on how hard it is to find the data in the tables. There may be other ways to do this, but this is how I've been approaching it.


To do the conversion:

1. Put the XML you want to convert into a mod. (The XML much actually function for this to work--you can't convert bad code).
2. Load the mod. Wait til you get past the loading screen.
3. In SQLite, go to the table the XML wrote to. Right click it and select Edit table.

4. Select the Data tab at the top to see a list of the Data in the table.

5. In the Data fields, select the range of cells you want to convert.
6. Right click and select Generate query for selected cells > INSERT.

7. A window will load with your results. Copy and paste them into your mod.

There are a LOT of additional uses for this BTW. You can actually use it to free-design new Units, Buildings, and so on, right click, and create an instant INSERT (or UPDATE) statement. You can also use it for generating quick SELECT statements.​

 

[Infixo]

SQLite Studio is an amazing tool.
I used the method you described but it produces such a bad-looking code unfortunately. Especially when there are many rows with several columns each. So, I exported the data to .csv, put it into Excel and created insert statements with concatenation function. Fairly simple, and the code is compact and neat.

 

[Fimbulvetr]

What I really hate is the way the insert statement is formatted, reversed column order, plus one line for each, how did anyone think that's a good idea?
It should really be
INSERT INTO table (column1, column2, ...) VALUES
(value1_c1,value1_c2, ...),
(value2_c1,value2_c2, ...);

 

[Infixo]

reversed column order

How so? You can put columns in any order you like, even omit some (then defaults will be applied). It's really flexible (as all sql commands). And one line for each - it's the most efficient way. You simply cannot have less text to do so.