This guide is designed to be applicable to both Civ V and Civ VI.
All too often I hear people decry SQL as being an inferior language to code in on the basis that the errors reported in the Database log aren't descriptive enough. While I won't deny that XML errors allow for interpretation at a glance, this simply reduces the time it takes to track down the issue; and compared to SQL's numerous benefits over XML, this hinderance pales in comparison.
Last month I explained how the Database log can be used in conjunction with the Stopwatch(V)/Modding(VI) log to limit down the culprit files behind an error. The next task therefore will be to determine where in the specified files the error lies. The fundamental principle we're following here is that the game interprets the SQL in a file up until it reaches a statement containing an error; it will then undo all actions performed on the database by this statement in accordance to the ACID principles and cease interpreting the file. Therefore, in order to locate an error in an SQL file we should look for the first piece of code that doesn't run.
There are two ways to see what code has been interpreted by the game and what code hasn't. The first would be to look to see what's actually in game - the Civilopedia would be a good place to start with this approach. However, a far superior method would be to read the database itself: this would not only save waiting for the game to go through its processes but also allows you to see things that aren't as visible to the Player, such as inserts to mod support tables. Each game has a selection of databases for you to look in, depending on the error: for Civ V, text is stored in Localization-Merged.db while everything else is in Civ5DebugDatabase.db; for VI you have DebugConfiguration for errors prefixed by [Configuration], DebugGameplay for errors marked as [Gameplay], and DebugLocalization for those labeled [Localization]. For both cases these databases can be found in your Cache folder, adjacent to your Logs and MODS folders.
To read these database files a variety of programs can be used: Kael suggests using a Firefox addon, although if memory serves this plugin may have been discontinued; WHoward (and I) recommend SQLSpy (the installation of which is explained
here); I also once found a Chrome extension that is presumably fully capable for these purposes, but the main message is that the tool doesn't matter so long as it lets you view the database. Once you have opened the database of interest in your viewer of choice, the task is simply to read your way through the file in question and verify that each statement has been completed; once you find the point where the statements stop being reflected in the database for no expected reason, your error has been found. The error message should then be referred to, and with our search area reduced to a small number of lines around the point where the file stops being interpreted the cause should be deducable with ease. If you're struggling to locate the error still, keep in mind that "NEAR" is a relative term; SQL interpretation ignores white space, and so NEAR will be referring to the last character or recognisable keyword in most cases even if this and the error are separated by a good number of blank lines. If your error continues to evade, your database viewer should allow you to execute SQL into the database; using this to input statement blocks individually can help you determine which ones work in isolation and which one continues to fail.
Hopefully this explanation can help inform modders on how they can debug their SQL code, and encourage XML coders to use the far more versatile option without fear of becoming lost. The beauty of code is that every syntax error can be corrected, and every logic error can be resolved; hopefully now locating these issues should be a stepping stone rather than the challenge itself.