xsql

This element is applicable only there, where the database source or database destination is used. It declares SQL script, that will be executed over the given source (or destination) database. This could be a standard SQL script or extended SQL script.

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.


In X-SQLT projects you can use the following markups of SQL scripts:

In extended SQL syntax the structure of xml elements declares the master-detail relationship between bounded SQL commands. Tag names of xml elements specify an extended SQL command identifiers, that can be referenced from child or subsequent commands. And attributes of xml elements define other processing options for given statements.


Example 

    
  <?xml version="1.0" ?>
  <xsqlt>
    <parameters>    
      <parameter1 alias="parameter-title" kind="string|text|integer|float|date|time|datetime|binary|boolean">value</parameter1>
      <parameter2>...</parameter2>
      <source-param1 alias="..." kind="..."/>
      ...
    </parameters>
    <script>
      <source> ... </source>
      <xsql kind="file|inline|stream" 
            one-transaction="0|1" 
            cmd-id="sql-command-identifier">
        (x)sql-script-filename | (x)sql-stream-identifier |
        inline-sql-script |
        <sqlCommandDDL after-open="0|1">SET NULL OFF;</sqlCommandDDL>        
        <sqlCommand1 no-write="0|1">SELECT * FROM TableA WHERE id=:parameter1 ...</sqlCommand1>
        <sqlCommand2 test=":sqlCommand1$id=0">
          SELECT id,name FROM TableB ...
          <sqlCommand3 record-tag="xml-record-tag">SELECT * FROM TableC WHERE id=:sqlCommand2$id ...</sqlCommand3>
        </sqlCommand2>
        <sqlCommandCSV>UPDATE TableC SET info=:source$info WHERE id=:source$1</sqlCommandCSV>
        <sqlCommandXML>UPDATE TableC SET info=:source$info$data WHERE id=:source$id</sqlCommandXML>        
        ...       
        <sqlCommandDDL2 before-close="0|1">SET NULL ON;</sqlCommandDDL2>
      </xsql>
      <transform> ... </transform>
      <destination> ... </destination>
    </script>
    <script>
      ...
    </script>
    ...
  </xsqlt>

Importing data by (X)SQL

When importing data into database, the whole (X)SQL script declared under "xsql" element will be executed for each record of source data file (or stream). To refer to the values of currently processed record you can use, so called, source parameters (described below).

Transform element, in this context, holds the format options of source file (or stream).

If the file (or stream) you want to import does not meet some of your requirements (or requirements of (X)SQL syntax), you can simply transform it by xslt, or xpath, or by any other kind of transformation.


Extracting data by (X)SQL

Result of (X)SQL processing is usually an XML document, that follows the structure and identifiers of commands declared in extended SQL script. There are two default identifiers (tag names) that are used for storing results - xsqlt_data (as a document element) and r (as a record element). You can override them by using record-tag attributes of sqlCommand elements, or of the tranform element. 

    
  <?xml version="1.0"?>
  <xsqlt_data>
    <sqlCommand2>
      <r>
        <id>100</id>
        <name>Gambardella, Matthew</name>
        <sqlCommand3>
          <r>
            <author>Ralls, Kim</author>
            <title>Midnight Rain</title>
            <genre>Fantasy</genre>
          </r>
        </sqlCommand3>
      </r>
      <r>
        <id>101</id>
        <name>Corets, Eva</name>
        <sqlCommand3>
          <r>
            <author>Corets, Eva</author>
            <title>Maeve Ascendant</title>
            <genre>Fantasy</genre>
          </r>
          <r>
            <author>Corets, Eva</author>
            <title>Oberon's Legacy</title>
            <genre>Fantasy</genre>
          </r>
        </sqlCommand3>
      </r>
    </sqlCommand2>
    <sqlCommandX>
      <r> ... </r>
      <r> ... </r>
    </sqlCommandX>
  </xsqlt_data>

As you can see, there are no data type specifications, no DTD schemas and no XSD schemas in (X)SQL result. This is by design, because X-SQLT is a free form language, and because these schemas and specifications could be always added by an appropriate XSL transformation template in some intermediate step of data exchange processing.


But XML is not the only format you can get from (X)SQL processing. The resulting format may vary based on defined transformation. So it can be a CSV file, JSON formatted stream, or any other format supported by concrete X-SQLT processor.


Attributes of xsql element

kind
Attribute named kind defines the storage of SQL script:
  • file (default) - text of the element represents the SQL script filename.
  • inline - SQL script is defined directly inside the xsql element.
  • stream - SQL script is stored inside a named temporary stream, which identifier is specified in the text of the source element.

Script filename can be relative to the project file or current directory.

"Named stream" must be created as a result of any previous script. You have to use the destination element to instantiate such a stream. SQL content itself can be made by transformation written in any scripting language or in XSL template.

SQL file (or stream) can contain standard SQL script or extended SQL script with elements defined in comments: 

    
  /* <sqlCommand1> */
  SELECT * FROM TableA WHERE id=:parameter1 ...
  /* </sqlCommand1> */
  
  /* <sqlCommand2 test=":sqlCommand1$id=0"> */
  SELECT id,name FROM TableB ...

    /* <sqlCommand3> */ 
    SELECT * FROM TableC WHERE id=:sqlCommand2$id ...
    /* </sqlCommand3> */

  /* </sqlCommand2> */
  ...

Inline SQL script can be included in the standard text format or as an extended SQL script with elements defined in xml form (as stated in the first example in this topic).

cmd-id
For non extended scripts you can use cmd-id attribute to identify the SQL statement. By this way you identify the simple SQL statement, instead of enclosing it into "sqlCommand" element (see below). This identifier will be used, for example, for dataset element of resulting XML or JSON file (or stream).
one-transaction
Allows you to process a whole script in one transaction. Values can be 0 (default, means individual transactions for each SQL statement) or 1 (means one transaction for all SQL statements). It is suitable for processing a huge amount of data, or when importing data from file into database (the whole transaction will be canceled if your import will fail).

sqlCommand elements

All elements under xsql are considered as SQL commands. There are no restrictions for naming these elements, except the XML conventions. The element name declares an extended SQL command identifier, that can be referenced from child or subsequent commands (see also Parameters section below).

The structure of sqlCommand elements declares the master-detail relationship between commands. This means, that subordinated SQL commands will be processed for each row of master SQL command result.


sqlCommand attributes

test
As you can see in our example, you can use test attribute to set up a condition for processing individual sql commands. An expression of the condition can contain parameters (see bellow).
no-write
By default, when extracting data from database, the SELECT SQL statements write their output into resulting stream (destination file, in-memory buffer for transformation, etc.). You can prevent this behavior by using no-write attribute with value of "1".
after-open
,
before-close
"sqlCommand" elements can be dedicated to execute only "after the connection is opened" or "before the connection is closed". For these purposes you can use after-open and before-close attributes. Such sql scripts do not produce any results into destination files, as well as they are not processed for any record from source files (during importing). You can use them mainly for setting some database options, or for preparing the database for import / export.
record-tag
Default record tag name, specified by X-SQLT language is "r". This default name could be overridden by related transform element (by its "record-tag" attribute). And at last, "record-tag" attribute of sqlCommand defines a specific record tag name for its results.

Parameters

In SQL script you can use parameters. (X)SQL recognizes three kinds of parameters - global, referenced and source parameters.

Global parameters are those, that are declared in project's parameters element (their values can be passed to X-SQLT processor, when executing the project, eg from the command-line). They should be stated as :parameter in SQL commands.

Referenced parameters refer to the fields of current record of any parent (or previous) SQL command. They should be stated as :sqlCommand$field.

Source parameters refer to the current record of source file (or stream) that is beeing imported into database. For example, if you are processing CSV file as a source, the parameter can be stated as :source$id or :source$2, where "source" is a mandatory identifier and "id" is the name of CSV file column (and where "2" is an index of CSV file column). For xml source files, you can also refer to the path of desired value - ie. :source$info$data, where "info$data" identifies the path to xml element under the given xml record element (record-tag/info/data).

Sometimes the database engine cannot resolve data type for given source parameter. This situation depends on many aspects, like the way you declare your SQL command, database engine features, source file type, and so on. To prevent this kind of problems, you can declare the source parameter kind in parameters element of your X-SQLT project. Such a parameter should be declared with "source-" prefix (ie. source-id, source-info-data, etc.). As you can see, the tag name of an element is similar to parameter name used in SQL command, except it uses '-' character (dash) instead of '$' (dollar sign). This is because '$' cannot be used for XML tag name and '-' cannot be used for SQL parameter identifier.


Parent element

script
A parent element of xsql is script.