Ever wanted to go directly from an sql to xml files. I do 🙂 Here is a little tool I built a while back. It takes an xml (or any character structure) and an sql and returns one file per row returned result.
For example:

sqltofile -t "%user%%timestamp%" -s "SELECT user, timestamp FROM user" -h mysqlite.db

Will output (one file per row):

row0:
niklas1363615417
row1:
miew1363615493
row2:
emma1363615604
...

The words surrounded with % – signs in the -t argument above gets substituted by the values in the columns with the same name (minus the % -sign ).

Options to use:
-t Is the template string that contains the substitution variables surrounded by % – signs
-s This is the sql to run agains the database (* can be used)
-h Is the filename of the sqlite3 database to run the query agains
-o Outputs the result to stdout (optional)
-b Writes all lines to one file called row0 (optional)

You can find the source and an brief explanation of sqltofile here

sqltofile is a little tool I created a while back to solve the problem of creating XML files fast and efficient from the result of a SQL query. It is build in C++ and currently supports Sqlite3. In the source there is also an Interface class for building support for more databases in the future. I will here explain briefly how it is built

We start with the interface class

#ifndef DATABASEINTERFACE_H
#define DATABASEINTERFACE_H
 
#include <vector>
#include <string>
 
using namespace std;
 
class DatabaseInterface {
 
   public:		
      virtual ~DatabaseInterface(){}
 
      virtual int connect(string &filename, string &user, string &password) = 0;
      virtual vector<vector<pair<string, string> > > query(string &sql) = 0;		
      virtual int disconnect() = 0;
 
};
#endif

The connect function takes hostname/filename, username and password as arguments. This function should be called first to establish a connection to the database
The query function runs the SQL against the database and returns a vector of vectors of pairs where the ‘first’ member variable contains the column name och the ‘second’ has the column value
The disconnect function is to be called last to clean up and disconnect from the database

And here is the main program

#include <string>
#include <stdio.h>
#include <stdlib.h>
#include <fstream>
#include <iostream>
 
#include <getopt.h>
 
#include "database.h"
 
// Forward declarations
void findAndReplace(string &subject, const string &search, const string &replace);
void saveToFile(string str, int row);
void printUsage();
 
int main(int argc, char const *argv[]) {
 
	// Variables
	string searchchr = "%";
	string searchstr = "";
	string batchstr = "";
	string tmplstr = "";
	string sql = "";
	string host = "";
	string user = "";
	string password = "";
	string result = "";
 
	bool batch = false;
	bool output = false;
 
	int option = 0;
	int conn;
 
	// Objects
	Database *db = new Database();
	vector<vector<pair<string, string> > > data;
 
	if (argc &lt; 3) {
		cout << "Too few arguments" << endl;
		printUsage();
		exit(EXIT_FAILURE);
	}
 
	// Handle arguments
	while ((option = getopt(argc, (char **) argv, "bot:s:h:")) != -1) {		
        switch (option) {
            case 'b' : batch = true;
                break;
            case 'o' : output = true;
                break;
            case 't' : tmplstr = optarg; 
                break;
            case 's' : sql = optarg;
                break;
            case 'h' : host = optarg; 
            	break;	   
            default: printUsage(); 
                exit(EXIT_FAILURE);
        }
    }
        // Connect to database	
	conn = db->connect(host, user, password);
 
        // Query database
	data = db->query(sql);
 
	// For every result row
	for (size_t n = 0; n < data.size(); n++) {
		result = tmplstr;
		// For every column in result row
		for (size_t m = 0; m < data[n].size(); m++) {
			searchstr = searchchr + data[n][m].first + searchchr;		
			findAndReplace(result, searchstr, data[n][m].second);				
		}
 
		// If batch save row to string (newline at end)
		if (batch) {			
			batchstr += result;
			batchstr += "\n";
		}
		else { // If not batch write to new file			
			saveToFile(result, n);
		}
 
		// If output print to screen
		if (output) {
			cout << result << endl;
		}
	}
        // If batch mode and we have recieved any rows - write to file
	if (batch && batchstr.length() > 0) {
		saveToFile(batchstr, 0);
	}
 
        // Disconnect from database and clean up
	db->disconnect();
 
	return 0;
}

First we have a bunch of variable declarations. I’m mostly using the std:string here for easy string handling. Then I take care of all the arguments using the getopt function – all pretty basic stuff. Now we get to the core of the program. It works something like this:

1. Call connect() with host/filename, user and password details. Since my database class is made for Sqlite the connect function calls sqlite3_open_v2() with the SQLITE_OPEN_READONLY option (sqltofile only supports SELECT – statements)
2. Call query() with our SQL query. Results are returned as an vector of vectors of pairs. This might need some more explanations. I use std:pair to hold the column name (first member variable) and value (second member variable). Every pair is then put into a vector representing a result row, and finally the result row is placed into another vector representing the whole result set
3. Loop thought the result set and perform variable substitutions. In the templete string there should be “variables” surrounded by % -signs. These get substitutet with the value that belongs to the column with the same name as the “variable” without the % – sign
4. If batch is true we place the result row into a container to write to file later (all rows into a single file)
5. If batch is not true (default) we immediately write the row to a file (one file per row)
6. If output is true the result row is printed to stdout
7. Disconnect from database and clean up all handles

That was a brief description of sqltofile. The full source code can be found here

Tested on OSX 10.7.5 with gcc version 4.2.1 and Sqlite 3.7.11