I see no need for the complexity of a fully relational database. Relational databases exist to make complex searching operations easy, as well as handle searching over vast datasets. If all you're doing in-game is indexing one of these every time an enemy spawns, you are using an incredibly overcomplicated tool to do so.

In short, SQLite is overkill.

At which point, the question isn't SQLite vs. XML. It's how you're storing it on disk and how you want to represent the data in-game. If the only kinds of queries you do in game is to search by name, a simple std::map would be sufficient. Indeed, you could stick them in a std::vector and sort them after loading for fast searching.

For the on-disk representation, you should be most concerned with the format that makes it easiest to edit, not to read. Reading is something you can do easily enough in code; editing you have to do once per enemy. When you're designing new enemies, you'll have to edit their data a lot.

To me, the dichotomy is between XML and Lua, which is perfectly serviceable as a data-description language. For example, the example XML file could be represented in Lua as:

return
{
    {
        name = "Red Dragon", type="BaseEnemy", level=56, displayname="Red Dragon",
        stats = {
            HP=55000, MP=2500, SP=2500, Strength=212, Vitality=125,
            Magic=200, Spirit=162, Skill=111, Speed=109,
            Evasion=100, MgEvasion=100, Accuracy=100, Luck=55
        },
        status_resistances = {
            {name="Sleep", value=100},
            {name="Stop", value=100},
        },
        element_resistances = {
            {name="Fire", value=75},
        },
        loot_table = {
            {name="Elixir", rate=0.03, count=1},
        },
        drop_table = {
            {name="Elixir", rate=0.03, count=1},
        },
        script = "BasicBehaviour.py",
        xp = 4800,
    },
    {
        name="Gaveric 1", type="HumanoidEnemy", level=33, displayname="Gaveric",
        --Same Stuff as above here
        equipment = {
            weapon = "Dark Eclipse",
            armor = "Terra Defense",
            accessory = "Ribbon",
        }
    }
}

The biggest advantage to a Lua-script based approach is that... it's Lua. While it can be pure data, it does not have to be. So if you have some repetitions of stat blocks, you can easily have the Lua script generate these data for you. All this is is a Lua script that returns a table; the table is what you read into your in-memory data structures.

The disadvantage to this approach is mainly if you are not using a tool to write these files. If you have a tool that you use to edit these enemy files, then the Lua approach is fine. But if you're hand-editing them, then that means you loading code that walks the Lua table needs to verify the input. It needs to check that the necessary fields are there, that each number value is a valid number, etc.

And while that's not exactly difficult code to write, it is exceedingly tedious. The advantage with XML is that you can validate it with a RelaxNG or WXS schema. There are even XML editors that have schema-guided editing, so that it becomes impossible to write an invalid XML file. If you add a new field, just adjust your schema, and you're fine.

Of course, you have to know how to write schemas, and you have to have a schema-driven XML format. Otherwise, the Lua script will be no worse off, since you have to validate the data either way. And the Lua script is arguably easier to parse through, since it allows you to query the data directly. Given a Lua table that contains an enemy definition, you can query for specific "elements" by name. With an XML parser, you have to process the elements as they come to you, which is generally a bit more complex to write.

can't query for arbitrary enemies, so will likely need to keep all enemies in memory.

... so? The data structures, taken from your example XML, would consume (with no attempts at compression:

• 14 stats * 4 bytes per = 56 bytes.
• Internal name: 32-byte string.
• Display name: 64-byte string.
• type: part of the data structure; does not need to be stored.

Each weapon can also be a 32-byte string. So a grand total in the worst case of... 248 bytes. You can store more than 3500 of those in half of a MB of RAM. I wouldn't worry about it.

This would mean I need to restart the game to load changed enemy data as well

Why? That's up to you. There's nothing that says you can't dump the enemy data and reload it in the middle of a game. If you can't do that, it would only be because you did not properly structure your code to make that possible.