X-SQLT Language Overview

    
  <?xml version="1.0" ?>
  <xsqlt name="Introductory example">
    <script>     
      <source kind="oledb">
        <connection-string>
          Provider=Microsoft.Jet.OLEDB.4.0;Data Source=dbdemos.mdb
        </connection-string>
      </source>
      <xsql kind="inline">
        <Customers>
          SELECT * FROM CUSTOMER
          <Orders record-tag="Order">
            SELECT * FROM ORDER WHERE CustNo=:Customers$CustNo
          </Orders>          
        </Customers>
      </xsql>
      <transform kind="xml" record-tag="Root/Customer" />
      <destination>customers.xml</destination>
    </script>
  </xsqlt>

X-SQLT (Extended SQL Transformation) is a free-form, declarative, XML-based language used to describe a series of commands for data exchange and transformations, that can be performed on databases, data files or structured documents. It is designed to allow easy selection, conversion and transformation of data using SQL, XML, XSLT, XPath and scripting languages (VBScript, JavaScript, etc).

X-SQLT is based on a simple, but generic idea of serialization of basic data exchange operations, where each operation consists of the following elements: source, (x)sql, transformation and destination. By combining these elements and their kinds (see below), you can define various types of data exchange operations.

Basic Elements

The top level element of X-SQLT language is called project. Each project consists of one or more scripts, that represent the basic data exchange operations (mentioned above).

The project can be defined as a series of independent scripts, but it is possible (and very usable) to link results of desired scripts into the complex chain. By using this technique you can easily produce more complicated transformations, that cannot be done by individual scripts in one step. X-SQLT instantiates temporary buffers (called "named streams") for these purposes. Of course, you can make the chaining also by referring to files.

Parameters element is used to define global parameters of the project. They can be referenced in XSLT templates and/or in SQL statements inside any script of the project. You can learn more about parameters in X-SQLT Reference topic.

Script Elements

As stated above, in each script you can define source, (x)sql, transformation and destination elements. Detailed description of these elements can be found in the X-SQLT Reference topic and you can also take a look at the Schema of components for complex overview in the graphical form.

In short:

The source element can point to a file, database or stream.
SQL scripts can be defined in (x)sql element, that can refer to a simple SQL script or to an extended SQL script, stored in referenced file (or in the named stream), or inside the X-SQLT project.
Transformation element can contain XSLT, XPath, VBScript, JScript or any other kind of transformation, that is supported by concrete processor of X-SQLT language (like XML, CSV or JSON transformations implemented in command-line processor). As SQL script, also transformation template (or code) can be stored in a referenced file, named stream or inside the X-SQLT project.
And at last, destination element can point to a file, database or stream, similarly to the source element.

(X)SQL

In order to support different schemas of input and output data, X-SQLT extends the syntax of standard SQL scripts in many ways, such as getting results of structured SQL statements (eg in XML or JSON format), repeatable master-detail processing, conditional processing of SQL statements based on previous results, etc. All that without affecting the native database syntax of SQL scripts, because X-SQLT language expects the extended markup inside SQL comments (/* ... */).

Some of mentioned extensions are already supported in modern database engines, but some are not, and for a lot of database engines (like Visual Fox Pro, DBase, etc.), these extensions are a must. In the terminology of X-SQLT, the extended SQL syntax is called (X)SQL.