transform

Element specifies transformation that will applied on source file, stream, or database (more precisely, on results of SQL processing). That's the main purpose of transform element.

Results of transformation are then saved into the file, temporary stream or (in some circumstances) into database (see end of this topic). This target is declared by destination element of the script.

If transform element is not defined in a given script, no transformation is applied. These kinds of scripts can perform other operations, like executing SQL scripts on database or copying files.

Example

    
  <?xml version="1.0" ?>
  <xsqlt>
    <script>
      <transform kind="xml|xslt|xpath|csv|json|VBScript|JScript|..." 
                 encoding="output-encoding"
                 namespaces="xml-namespaces"  
                 sel-namespaces="xml-selection-namespaces"
                 indent="0|1"
                 record-tag="xml-record-tag" 
                 include-params="0|1" 
                 flat-text="0|1" 
                 delimiter="csv-delimiter" 
                 quote="csv-quote" 
                 header-row="0|1">                 
        transformation-source-file | transformation-source-stream-identifier |                 
        XPath expression(s) |        
        <![CDATA[
          transformation-source-inline
        ]]>        
      </transform>
    </script>
    ...
  </xsqlt>

Attributes

kind

Defines the kind of transformation. Basic identifiers of kind attribute are listed below, but the list can be extended by any other kind of transformation, that is supported by concrete processor of X-SQLT language.

Transformation kinds:

xml (default) - specifies the format of input/output file (or stream) as XML
csv - specifies the format of input/output file (or stream) as CSV
json - specifies the format of input/output file (or stream) as JSON
xslt - transforms XML-based source by specified XSL template
xpath - selects node set (or value) from XML-based source via XPath expression(s)
jpath - simple data extraction from JSON file (or stream), based on native access paths
csv/xml - converts CSV source into XML format
xml/csv - converts XML source into CSV format
xml/json - converts XML source into JSON format
json/xml - converts JSON source into XML format
json/csv - converts JSON source into CSV format
VBScript, JScript, ... - executes the code in a given scripting language.
Note: Source, destination and transformation interfaces are provided to be used within the script code. In addition to any installed "Windows Script Host" language, you can use pdScript and pdScriptGUI identifiers, that will execute the code via pdScript interpreter (if it is installed on your machine).

Note 2: If you want to use any interactive elements in your WSH script (such as message boxes), add postfix "GUI" to the specified transformation kind (ie. VBScriptGUI, JScriptGUI, etc.).

The following table describes meaning of each transformation kind when used in combination with specified source and destination. The table is based on features of current implementation of X-SQLT Command-line Processor.

source	xsql is used	transformation	destination	description of transformation meaning
database	yes	xml	file \| stream	results of SQL processing will be saved into XML formatted destination
database	yes	xslt	file \| stream	XSL transformation will be applied on results of SQL processing and saved into destination
database	yes	xpath	file \| stream	nodes will be selected from results of SQL processing, by XPath expression(s), and saved into XML formatted destination
database	yes	xpath	file \| stream	value will be selected from results of SQL processing, by XPath expression (that starts with '=' character), and saved into destination
database	yes	csv	file \| stream	results of SQL processing will be converted into CSV format and saved into destination
database	yes	json	file \| stream	results of SQL processing will be converted into JSON format and saved into destination
file \| stream	yes	xml	database	XML formatted data will be imported from source into destination database, by provided SQL statements
file \| stream	yes	csv	database	CSV formatted data will be imported from source into destination database, by provided SQL statements
file \| stream		csv/xml or xml	file \| stream	CSV formatted source will be converted into XML formatted destination
file \| stream		xslt	file \| stream	XSL transformation will be applied on source file (or stream) and its results will be saved into destination
file \| stream		xpath	file \| stream	nodes will be selected from XML formatted source, by XPath expression(s), and saved into XML formatted destination
file \| stream		xpath	file \| stream	a value will be selected from XML formatted source, by XPath expression that starts with '=' character, and saved into destination
file \| stream		xml/csv or csv	file \| stream	XML formatted source will be converted into CSV formatted destination
file \| stream		xml/json or json	file \| stream	XML formatted source will be converted into JSON formatted destination
file \| stream		json/xml	file \| stream	JSON formatted source will be converted into XML formatted destination
file \| stream		json/csv	file \| stream	JSON formatted source will be converted into CSV formatted destination
file \| stream		jpath	file \| stream	data will be extracted from JSON formatted source (based on JavaScript native access paths) and saved into destination
file \| stream		jpath	file \| stream	a new JSON object will be created, based on specified structure (ie. {Customer:["A","B"]}), with possible data extraction from JSON formatted source (see above)
any source		VBScript \| JScript \| ...	any destination	code in a given scripting language will be executed, source and destination interfaces are provided to be used within the code (when source or destination refers to database, interface represents the connection options only, not a programmatic interface)
database	yes	VBScript \| JScript \| ...	any destination	code in a given scripting language will be executed, source and destination interfaces are provided to be used within the code, where source interface represents the result of SQL processing here (when destination refers to database, interface represents the connection options only, not a programmatic interface)
any source		VBScript \| JScript \| ...		code in a given scripting language will be executed, source interface is provided to be used within the code (when source refers to database, interface represents the connection options only, not a programmatic interface)

Notes:

The term "results of SQL processing" means usually a temporary XML formatted data file (or stream) produced by SELECT SQL statements executed on source database. This is because XML is widely used as an input format in most of available transformations (ie XSLT, XPath, etc.). A draft specification for this data file is available inside the xsqlt element help topic. For simple kinds of transformations (eg CSV), it is obvious, that more efficient is to create the resulting format directly from selected data cursors, so please, do not take the words "... will be converted to ..." literally.

For XSL transformation you can refer to desired template by specifying a filename (or temporary stream identifier) in the text of transform element (a path can be relative to the project file or current directory), or by placing the whole XSL template inside the CDATA section of transform element. The same applies to XPath expressions and scripting languages code.

For node set selections you can define more XPath expressions at once (each on one row of transform element text). For value selection, the XPath expression must start with '=' character and there can be only one expression defined. Value selection produces simple text output, in opposite to a node set selection, that results to an xml output.

encoding

This optional attribute can be used to define character encoding of resulting file (or stream) that is specified as an output of transformation. For importing (into database) purposes, this attribute specifies character encoding of an input file (or stream).

You can use three virtual encodings here - "ansi", "dos" and "oem". The virtual encoding will be substituted by current ANSI, DOS or OEM encoding installed on the PC (where X-SQLT project is running).

namespaces

Use this attribute to specify output namespaces for XML documents created by simple transformations like "xpath" or "xml" and for XML documents created by (X)SQL processing. Defined namespaces will be assigned to the root element. See example below.


  ...
  <transform kind="xpath"
             namespaces="xmlns='www.contoso.com' xmlns:nw='http://www.northwind.com' xmlns:aw='http://www.adventure-works.com'"
             sel-namespaces="xmlns:aw='http://www.adventure-works.com'"
             record-tag="nw:filtered_orders">
    //aw:PurchaseOrder                 
  </transform>
  ...

"namespaces" attribute is ignored for transformations like "xslt", that should declare resulting namespaces by itself.

sel-namespaces

Use this attribute to set a list of namespaces for selecting fragments (node sets or values) from XML document by XPath. See an example above.

indent

Allows you to produce an indented XML and JSON results. Default value is 0 (no indent).

record-tag

This multi-purpose attribute can be used for the following situations:

When converting from XML to another format:
You can specify a tag name (or path fragment) that identifies the data record in XML document. The path fragment could be recognized in any level.
Examples: "Order", "Root/Orders/Order", etc.
When importing XML data into database:
You can specify a tag name (or path fragment) that identifies the data record in XML document. The path fragment could be recognized in any level.
Examples: "Order", "Root/Orders/Order", etc.
When converting from JSON to another format:
You can filter JSON data by the given path and you can also redefine the ancestors structure by specifying new parent elements (see the path in braces below). The path has to be specified from the top level of JSON data structure.
Examples: "Customers.Customer", "{Customer:Customers.Customer[2]}", "{Customers:{Customer:Customers.Customer}}", etc.
When converting from flat file format to XML format (ie from CSV to XML):
You can define ancestor elements for your data fields in resulting XML document.
Examples: "Catalog/Orders/Order", "Orders/Order", "Order", etc.
When filtering XML document by XPath expression:
You can define ancestor elements for the selected XML nodeset in resulting XML document.
For example: "Root/Orders", "nw:filtered_orders", etc.
When specifying the default record tag name of (X)SQL processing results (this means results stored into XML, JSON or other structured formats):
If you specify more than one tag name (means names separated by slash) for that purpose, the last name will be used as a default record tag name, and all the prior names will be used as ancestor elements of script datasets. X-SQLT declares default document element name as "xsqlt_data", and default record tag name as "r".
See also "record-tag" attribute specification for (X)SQL command elements.

"record-tag" is an optional attribute. If it is not specified, default values should be used when creating output documents (eg "xsqlt_data/dataset/r"), and to detect data records of input documents, some smart parsers could be used (as for example in X-SQLT Command-line Processor).

include-params

Optional attribute that specifies if parameter values will be included into results of (X)SQL processing. It is intended for XML, JSON and other markup formats only. Default value of "include-params" is "1" (parameter values are included).

flat-text

Defines how the text of "data fields" (child nodes of the specified record node - see "record-tag" above) from structured data sources (such as xml) will be handled. If flat-text is 1 (default) then only a text of the field element will be taken into account, otherwise all the text from all sub nodes of the field will be used (merged). "flat-text" is designed mainly for flat-file conversions (ie. from xml to csv), not for database processing. Attribute is optional.

indent

This optional attribute turns on / off indentation of xml and json results. Set the value to 1 if you want to indent the output. Default value is 0 (no indent).

delimiter

Defines the field delimiter for CSV formatted sources and destinations. Default value is semicolon (;). Attribute is optional and designed for CSV handling only.

quote

Defines the quote character for CSV formatted sources and destinations. Default values is quoatation mark ("). Attribute is optional and designed for CSV handling only.

header-row

Indicates that CSV formatted source (or destination) contains header row (a row with field names). Default value is 1. Attribute is optional and designed for CSV handling only.

Child elements

text

For XSL transformations (kind = xslt) the text should contain a template filename or stream identifier (if the template is not declared inline, in CDATA section - see below). Filename path can be relative to the project file or current directory.

The same applies to codes written in any of supported scripting language.

When using XPath selections (kind = xpath) the text can contain valid XPath expression or stream identifier (where an expression is stored), or name of the file with XPath expression(s), where the path can be relative to the project file or current directory. XPath expression(s) could be placed also into CDATA section of transform element (see below).
For node set selections you can define more XPath expressions at once (each on one row of transform element text). For value selection, the XPath expression must start with '=' character and there can be only one expression defined. Value selection produces simple text output, in opposite to a node set selection, that results to an xml output.

CDATA

You can place a whole XSL template inside the first CDATA section of transform element, instead of specifying a template filename. This applies also for XPath expression(s) and scripting languages.

CDATA [2,3,...]

Into subsequent CDATA sections (second, third, etc.) of transform element you can store some additional resources, used by scripting languages. For example, it can be a "Delphi Form", which is the GUI definition for Pascal Script language, executed by pdScript interpreter (if pdScriptGUI transformation kind is used).

Parent element

script: See script.

Parameterized XSL transformations

You can pass parameters from X-SQLT project into XSL transformation style-sheet, simply by including <xsl:param> element into your style-sheet and by declaring the parameter element inside your project.

Parameter values can be also passed directly to the executed X-SQLT project, for example from the command-line. Typical example is the transformation of XML file by defined XSL template and with passed parameter values.

Transformations and database destination

When database is defined as a destination of the script, then there are some restrictions about using the transform element, that has a different meaning for each of three kinds of transformations in this context:

Scripting Languages (VBScript, JavaScript, ...)
There are no restrictions, X-SQLT script is processed in the usuall way.
Declarative Languages (XSLT, XPath, ...)
They are not allowed in this context. It means that results of this kind of transformation can be stored into the file or stream, but not into database. This restriction is described below.
Format Specifiers (XML, CSV, ...)
They are allowed only in combination with xsql element. In this context, they specify the importing options, such as format of source file (or stream), its encoding, and other properties. (X)SQL script will be then processed on the destination database for each data source record.

These restrictions are defined by design, because they simplify implementation of X-SQLT processors, as well as the language syntax. They were designed to support correct processing of data imported into database, that should be performed via SQL statements (the standardized way), and thus, because the SQL section (xsql element) could be already used in a given script (for selecting data from a source database) together with the transformation element that applies to these SQL results, there is no place for another xsql and transform elements in a given script (unless we extend the syntax, which is counterproductive in compared to solution with two standalone scripts mentioned below).

Scripting languages are the only one exception here, because in this context X-SQLT declares the usage of some code, but it has no control over it (as opposed to declarative languages, that have an input and output clearly defined). So, if the scripting language can connect to database and can write some data, we have to allow this combination of transform and destination elements.

The task, when the source needs to be transformed and results have to be stored into database, can be easily defined also by using declarative languages. This can be done by linking two scripts via named streams:

First script can define the requested source, desired transformation and the stream as a destination.
Second script should define previously declared stream as a source, and the desired database as a destination. Transform element is listed in this script too, but in this context it holds the importing options, not transforming options.

See also:

X-SQLT OLE Automation Interface