How to Automatically Generate XML Language_en_US Files

[hazel16]

I really appreciated Pazyryk's time-saving SQL tricks here. After automatically generating the 'TXT_KEY' fields in SQL, I wanted to figure out a way to automatically generate the Language_en_US XML files too. The following takes a few steps but will save time if you have 50+ buildings or units to add. It will also reduce the possibility of typing mistakes.

In order to follow these steps, you must have already created your buildings (or units, or civilizations) in XML/SQL and loaded your mod in-game.

1. Open your Civ5DebugDatabase.db (Documents\My Games\Sid Meier's Civilization 5\cache\Civ5DebugDatabase.db) in SQLLiteSpy

NOTE: you can download SQLLiteSpy here. I'd think these steps would work in any SQL Lite viewer but I'm not familiar with others.​

2. Paste the following code into the statement box. Replace the blue text with the table you want to create your XML file from and the red text with the prefix you used in your 'Type' column.

CREATE TABLE XML_Merge (text_XML string);

INSERT INTO XML_Merge (text_XML)
SELECT
	'<Row Tag="' || Description || '"><Text>' 
	|| LOWER(REPLACE(REPLACE(Type, '[COLOR="Red"]BUILDING_[/COLOR]',''),'_',' '))
	|| '</Text></Row>' 
	|| '<Row Tag="' || Help || '"><Text>'
	|| LOWER(REPLACE(REPLACE(Type, '[COLOR="red"]BUILDING_[/COLOR]',''),'_',' '))
	|| ' help</Text></Row>'
	|| '<Row Tag="' || Strategy || '"><Text>'
	|| LOWER(REPLACE(REPLACE(Type, '[COLOR="red"]BUILDING_[/COLOR]',''),'_',' '))
	|| ' strategy</Text></Row>'
	|| '<Row Tag="' || Civilopedia || '"><Text>'
	|| LOWER(REPLACE(REPLACE(Type, '[COLOR="red"]BUILDING_[/COLOR]',''),'_',' '))
	|| ' civilopedia</Text></Row>' FROM [COLOR="Blue"]Buildings[/COLOR];

Spoiler :

3. Go to Execute -> Execute SQL (F9).

2. Go to XML_Merge table and copy contents. Click on a row, press Control-A to select all and then copy.

Spoiler :

3. Go to http://titlecapitalization.com/, paste your content in the text box. It will automatically capitalize it. Then copy text back from the box.

Spoiler :

4. Paste inside new XML file that's set up like this:

<GameData>
	<Language_en_US>
	[COLOR="red"]--paste here--[/COLOR]
	</Language_en_US>
</GameData>

5. Delete the top line of the pasted code ("text_XML")

Spoiler :

6. This file would be easier to read if there were more line breaks between rows. Go to find/replace (Control-H). Check the box 'Use Regular Expressions'. Do the following:

Find: \/Row\> \<Row Replace: \/Row\>\n\n\t\t\<Row
Find: \/Row\>\<Row Replace: \/Row\>\n\t\t\<Row​

Spoiler :

7. Your file should be ready to use.

Spoiler :

NOTE: If you're doing this more than once, you need to run following SQL code prior to starting.

DROP TABLE XML_Merge;

 

[bane_]

This... is so, beautiful.

 

[Nutty]

I suggest you move this to the Tutorials & Reference subforum (just click Report Post to notify a moderator).

 

[hazel16]

bane -- thanks, hope it's helpful

nutty -- done, thanks for suggestion

 

[Rob (R8XFT)]

Moderator Action: Moved to Tutorials and Reference sub-forum.

 

[Pazyryk]

Nice idea. Sadly, it won't do all the international characters we use in our mod:

<Row Tag="TXT_KEY_EA_EPIC_VOLUSPA">
<Text>Völuspá</Text>
</Row>

Probably most other modders could update these one-by-one if they don't occur too often..

 

[Belzhorash]

Just giving this thread a little bump because it saves hours and hours using this method...

Great idea!

 

[localdisk51]

Hopefully this isn't too far off from the type of automating you've brought up, but figured what I'm doing to deal with language entries might be a useful alternative for some people. I'm only using short text strings for all my text key entries, so I've skipped the XML thing entirely. Since I'm not too great at explaining things, I'll just give an example.

I'm adding a ton of units where each civilization gets a unique unit type of a given unit class. Here is a statement that adds a different infantry unit for each civ, where I want the localized Text name of the unit to be preceded by that civilizations adjective. I've cut out a bunch of parts to hopefully make it easier to read:

INSERT INTO Units ( Type,	Description, IconAtlas,	UnitFlagAtlas )
SELECT	('UNIT_' || UPPER((SELECT Text FROM Language_en_US WHERE Tag = Civilizations.Adjective)) || '_INFANTRY'), ((SELECT Text FROM Language_en_US WHERE Tag = Civilizations.Adjective) || ' Infantry'), Units.IconAtlas, Units.UnitFlagAtlas		
FROM Civilizations, Units WHERE Units.Type = 'UNIT_PROTOTYPE_INFANTRY' AND Civilizations.Type NOT IN ('CIVILIZATION_MINOR','CIVILIZATION_BARBARIAN')

Instead of leaving out the description and using UPDATE to concatenate the TXT_KEY prefix, I'm just adding the actual text I want as a description. This makes it a lot easier to add into the localization database, because I can just transfer the Description column straight into the Text column, then concatenate the TXT_KEY prefix to the unit type for my Tag entry like so:

INSERT INTO Language_en_US ( Tag, Text ) 
SELECT    ('TXT_KEY_' || Type), Description FROM Units

Then update the Description column the same old way you were doing it with:

UPDATE Units SET Description = 'TXT_KEY_' || Type;

Although for the time being I'm just copying the definition text into _HELP, _PEDIA, and _STRATEGY. So my insert into the localization table looks more like this:

INSERT INTO Language_en_US (   Tag,									Text		) 
SELECT						 ('TXT_KEY_' || Type),					Description		FROM Units UNION ALL
SELECT						 ('TXT_KEY_' || Type || '_HELP'),		Description		FROM Units UNION ALL
SELECT						 ('TXT_KEY_' || Type || '_PEDIA'),		Description		FROM Units UNION ALL
SELECT						 ('TXT_KEY_' || Type || '_STRATEGY'),	Description		FROM Units;

Of course this only works in English, and if you're like me, absolutely hate using XML. But by doing this in SQL, it's possible automate the other entries by using templates for groups of similar units or buildings or whatever, then using string formatting functions to customize each entry. Although it would probably be easier to just use another language for that.

On a side note, I have no idea how you guys manage to format your code on this site. Direct copy and paste looks pretty aweful.