Abstract
This specification defines an extension of XSL Transformations (XSLT) . It describes a set of XSLT extension elements and functions, designed to provide access to the data stored in relational databases.
Using the database access extensions, XSLT stylesheets may implement various data transformation algorithms that involve data stored in XML documents and in relational databases. For instance, XSLT stylesheets may be designed to create XML documents from selections of data stored in databases, or to import XML documents into databases. More sophisticated data transformations typical for modern Web-based applications may be implemented as well.
Status of this document
This is the second working draft of the specification. It was developed as part of Unicorn XSL World (UXSL) programme.
Comments on this specification may be sent to [email protected]
Table of contents
1 Introduction
This specification defines an extension of XSL Transformations (XSLT). It describes a set of XSLT extension elements and functions, designed to provide access to the data stored in relational databases. These elements and functions belong to a specific namespace and are integrated into XSLT transformation environment using the extension mechanism specified in W3C XSLT recommendation.
Extension elements defined by this specification may be embedded in XSLT stylesheets and intermixed with the standard XSLT instructions. The special version of XSLT processor is required to process such stylesheets. When an instruction corresponding to the database extension element is encountered, it is instantiated as described in this specification. During the instantiation, the appropriate data access operations are performed. Various extension instructions are responsible for connecting to data sources, preparing and executing SQL statements, scrolling result sets.
This specification also defines the set of extension functions designed to access results of the execution of SQL statements. These functions may be used in XSLT expressions and attribute value templates in all contexts where functions are allowed. Values returned by data access extension functions may be inserted into generated content or used as intermediate parameters during the transformation.
2 Extension Namespace
The extension namespace assigned to all XSLT database access extension elements and functions is:
https://www.unicorn-enterprises.com/XSLT/Extensions/SQL/1.0
In this specification, the prefix sql: is used for referring to database extension elements and functions. Authors of XSLT stylesheets are free to use any other prefix, provided that the appropriate namespace declarations are supplied.
3 Extension Elements
Extension elements defined by this specification are used to implement various database access operations. They may be embedded in XSLT stylesheets and intermixed with the standard XSLT instructions.
The following extension elements implement extension instructions:
- sql:connect
- sql:prepare
- sql:execute
- sql:for-each
- sql:batch
- sql:source
- sql:transact
When any of these instructions is encountered, it is instantiated as described in this specification. During the instantiation, the transformation engine performs appropriate data access operations.
The following extension elements are not treated as instructions:
- sql:param
- sql:with-param
These elements are used to supply additional information for database access extension instructions.
3.1 Connecting to a Data Source
The connection to one or more data sources should be established before attempting any database access operation. The extension element sql:connect is used to request such a connection. When this element is instantiated, the connection to the specified data source is established. The connection is active while the instantiation of the parent element of sql:connect is in progress. The connection is closed when instantiation of that parent element is complete.
Each active connection is assigned a connection identifier, which may be an arbitrary string. Some elements that describe data access operations must specify connection identifiers.
Connection identifiers need not to be unique. When several connections assigned the same connection identifier are active at some point, this connection identifier is referencing the connection that was the most recently established. Empty strings are allowed as connection identifier values.
A connection identifier corresponding to an active connection may be referenced by stylesheet elements that need not to be descendants of the parent of the sql:connect element that had defined this identifier.
The sql:connect extension element has the following syntax:
The required attribute source is used to specify the data source. The source attribute is interpreted as an attribute value template.
The optional attribute user is used to provide the user identification. The source attribute is interpreted as an attribute value template.
The optional attribute authentication is used to specify the authentication string (e.g., password). The authentication attribute is interpreted as an attribute value template. It is, of course, not recommended to keep authentication information hard-coded within a stylesheet. Instead, if needed, authentication information may be passed to a stylesheet as a parameter.
The optional attribute type identifies the type of the data source and software platform used to establish the connection. The only standard value defined by this specification is "odbc", which means that connection to ODBC data source is requested. This value is used as default when the type attribute is not specified.
The syntax and semantics of the values of source, user and authentication attributes depend on the type of the data source.
The optional attribute connection-id is used to specify the connection identifier. If this attribute is not specified, the empty string is assumed as the connection identifier value.
The optional attribute commit-mode is used to specify the transaction commit mode. This attribute is interpreted as an attribute value template. If it evaluates to auto, then auto-commit mode is assumed. In this mode, each SQL statement is interpreted as a separate transaction, which is automatically committed. If the attribute value evaluates to manual, then manual-commit mode is assumed. In this mode, transactions should be controlled explicitly using sql:transact extension elements. If this attribute is not specified, the auto-commit mode is assumed.
3.2 Preparing a Statement
To improve the performance, SQL statements may be prepared prior to the execution. The extension element sql:prepare is used to request preparing of a statement. When this element is instantiated, the supplied SQL code is processed by the underlying data access software, and the prepared statement is created. The prepared statement exists while the instantiation of the parent element of sql:prepare is in progress. The prepared statement is destroyed when instantiation of that parent element is complete.
Each prepared statement is assigned a statement identifier, which may be an arbitrary non-empty string. Elements that describe operations performed using prepared statements must specify the appropriate statement identifiers.
Statement identifiers need not to be unique. When several statements assigned the same statement identifier exist at some point, this statement identifier is referencing the statement that was the most recently assigned that identifier.
A statement identifier corresponding to an existing statement may be referenced by stylesheet elements that need not to be descendants of the parent of the sql:prepare element that had defined this identifier.
The sql:prepare extension element has the following syntax:
The optional attribute connection-id is used to identify the active connection that should be used to execute the statement. If this attribute is not specified, the empty string is assumed as the connection identifier value.
The required attribute sql is used to provide the SQL code describing the statement. The SQL code may contain parameter markers to specify placeholders for parameter values that will be supplied at the execution time. Only input parameters are allowed. Output parameters (e.g., those returned by stored procedures) are not supported by this specification. Statements that generate result sets may contain column markers to specify columns returned in the result sets. The sql attribute is interpreted as an attribute value template.
The required attribute statement-id is used to specify the statement identifier that should be assigned to the prepared statement.
Some data sources require specification of the data type for each parameter for SQL statements that contain parameter markers. To provide such specification, the sql:prepare must contain sql:param elements as children. If sql:param children are present, their number should be equal to the number of parameter markers.
3.3 Executing a Statement
The extension element sql:execute is used to request execution of a statement that does not generate a result set. When this element is instantiated, the corresponding SQL statement is executed by the underlying data access software. There are two different forms of statement execution: direct execution and prepared execution.
In the case of direct execution, the SQL code is supplied as an attribute on the sql:execute element. When this element is instantiated, the SQL code is executed directly.
In the case of prepared execution, the statement identifier of the statement prepared during the instantiation of the sql:prepare element is supplied as an attribute on the sql:execute element. When this element is instantiated, the corresponding prepared statement is executed.
There are two forms of the sql:execute element:
The first form corresponds to the direct execution.
The optional attribute connection-id is used to identify the active connection that should be used to execute the statement. If this attribute is not specified, the empty string is assumed as the connection identifier value. This attribute is allowed only if the sql:execute element corresponds to the statement which is executed directly.
The attribute sql is used to provide the SQL code describing the statement. Parameter markers and column markers are not allowed. This attribute is allowed only if the sql:execute element corresponds to the statement which is executed directly. The attribute is required in this case.
The second form corresponds to the prepared execution.
The attribute statement-id is used to identify the existing prepared statement created during the prior instantiation of the sql:prepare element. This attribute is allowed only if the sql:execute element corresponds to the prepared statement. The attribute is required in this case.
If the prepared statement contains parameter markers, the actual parameter values should be supplied using sql:with-param children of the sql:execute element. The number of sql:with-param children should be equal to the number of parameter markers.
3.4 Scrolling a Result Set
The extension element sql:for-each is used to request execution of a statement that generates a result set. When this element is instantiated, first the corresponding SQL statement is executed by the underlying data access software. The result set is created that contains zero or more rows. Then the template which forms content of the sql:for-each element is instantiated in a loop, once for each row of the result set. During the content instantiation, the column values of the current row of the result set may be accessed using the sql:fetch extension function.
During the instantiation, each sql:for-each element is assigned a cursor name, which may be an arbitrary string. The cursor name may be used within the stylesheet to refer the current row of the result set.
The cursor name is valid while the instantiation of the corresponding sql:for-each element is in progress. Cursor names need not to be unique. When instantiation of the several sql:for-each elements assigned the same cursor name is in progress at some point, this cursor name is referencing the element corresponding to the most recently started instantiation. Empty strings are allowed as cursor names.
A cursor name corresponding to the sql:for-each element, whose instantiation is in progress, may be referenced by stylesheet elements that need not to be descendants of the parent of the sql:for-each element that had defined this name.
There are two different forms of statement execution: direct execution and prepared execution.
In the case of direct execution, the SQL code is supplied as an attribute on the sql:for-each element. When this element is instantiated, the SQL code is executed directly.
In the case of prepared execution, the statement identifier of the statement prepared during the instantiation of the sql:for-each element is supplied as an attribute on the sql:for-each element. When this element is instantiated, the corresponding prepared statement is executed.
There are two forms of the sql:for-each element:
The first form corresponds to the direct execution.
The optional attribute connection-id is used to identify the active connection that should be used to execute the statement. If this attribute is not specified, the empty string is assumed as the connection identifier value. This attribute is allowed only if the sql:for-each element corresponds to the statement which is executed directly.
The attribute sql is used to provide the SQL code describing the statement. The SQL code may contain column markers. Parameter markers are not allowed. This attribute is allowed only if the sql:for-each element corresponds to the statement which is executed directly. The attribute is required in this case.
It is an error if two sql:for-each elements associated with the same prepared statement are executed at the same time. In the other words, it is not allowed to initiate execution of any prepared statement while the instantiation of some sql:for-each element that referenced this statement is in progress.
The second form corresponds to the prepared execution.
The attribute statement-id is used to identify the existing prepared statement created during the prior instantiation of the sql:prepare element. This attribute is allowed only if the sql:for-each element corresponds to the prepared statement. The attribute is required in this case.
The optional attribute cursor-name is used to specify the cursor name that should be assigned to the cursor associated with the statement. If this attribute is not supplied, the empty string is assumed as the cursor name. The attribute cursor-name may be used with both forms of the sql:for-each element.
If the prepared statement contains parameter markers, the actual parameter values should be supplied using sql:with-param children of the sql:for-each element. The number of sql:with-param children should be equal to the number of parameter markers. The sql:with-param elements must precede all other children of the sql:for-each element.
3.5 Batch Processing
The sql:batch extension element can be used to execute a single prepared statement with multiple sets of parameter values.
The sql:batch extension element has the following syntax:
The required attribute statement-id is used to identify the existing prepared statement created during the prior instantiation of the sql:prepare element.
The value of the required attribute select should be an expression. This expression is evaluated during instantiation of the sql:batch element; the result should be a node-set. All nodes from this node-set, in document order, form a selection list. For each node from the selection list a separate set of parameter values is created. Total number of parameter sets is, therefore, equal to the number of nodes in the result list. Parameter values corresponding to each node in the result list are evaluated based on sql:with-param elements that form the content of the sql:batch element. When sql:with-param elements are processed for a particular node from the selection list, this node is used as the current node, and the selection list is used as the current node list.
The prepared statement is executed using the array of parameter sets. The effect is similar to the execution of the statement in a loop, once for each result set in the array. If the data source does not provide native support for multiple parameter sets, the stylesheet processor should emulate this functionality.
3.6 Executing Statements from an External File
The sql:source extension element can be used to execute a sequence of SQL statements contained in a separate text file.
It is assumed that the file contains one or more SQL statements, separated using the ; (semicolon) character. The last SQL statement in the file may be optionally followed by ; character.
The file may contain comments. There are two forms of comments. Comments of the first form are started with -- (double dash) and span till the end of line. Comments of the second form are started with /* and span till the nearest following occurrence of */. Nested comments of the second form are not allowed, in the other words, if /* fragment is encountered inside such comment, the error must be signaled.
The sql:source extension element has the following syntax:
The optional attribute connection-id is used to identify the active connection that should be used to execute statements. If this attribute is not specified, the empty string is assumed as the connection identifier value.
The required attribute uri-refernce contains an attribute value template. This template is evaluated and the result is interpreted as URI reference to the external file containing statements to be executed. Relative URI references are resolved using the base URI of the sql:source stylesheet element.
The optional attribute encoding specifies the encoding of text in the external file. The attribute value is interpreted case-insensitive. If this attribute is not present, the value of iso-8859-1 is assumed.
3.7 Declaring Transactions
The sql:transact extension element can be used to encapsulate database access extension elements that must be executed within a single transaction. Each sql:transact element references a certain connection and affects only statements that are related to this connection.
The effect of sql:transact instantiation depends on the commit mode declared for the corresponding connection.
In auto-commit mode, the content of sql:transact is instantiated. No special transaction processing is performed, since in this mode each statement is treated as a separate transaction.
In manual-commit mode, when instantiation of sql:transact begins, the new transaction on the specified connection is started. If another uncommitted transaction on this connection is present, it is implicitly committed prior to starting the new transaction. The content of sql:transact is instantiated. The stylesheet processor commits the transaction when instantiation is successfully completed.
If an unrecoverable error is encountered during the stylesheet processing, the stylesheet processor must rollback all uncommitted transactions.
If the data source does not support transactions, the stylesheet processor must silently ignore requests for unsupported transaction operations and continue processing without issuing error or warning messages.
The sql:transact extension element has the following syntax:
The optional attribute connection-id is used to identify the active connection associated with the transaction. If this attribute is not specified, the empty string is assumed as the connection identifier value.
3.8 Declaring Parameters
When a prepared statement containing parameter markers is executed, the underlying data access engine might need to have knowledge about the data types of parameters. Many data sources provide all necessary information to the data access engine. However, some data sources do not provide the parameter information. For these data sources the parameter data types should be declared explicitly within the stylesheet.
The sql:param extension elements are used to declare parameter types of prepared SQL statements that contain parameter markers. These elements are included in the stylesheet as children of the sql:prepare elements. If sql:param children are present, their number should be equal to the number of parameter markers in the corresponding SQL statement.
The stylesheet processor may determine automatically whether the data source provides the parameter information. If the parameter information is provided by the data source, the stylesheet processor may ignore sql:param elements. In this case the stylesheet processor in not required to validate the type information declared by sql:param elements.
The sql:param extension element has the following syntax:
The required attribute type is used to specify the SQL type of the corresponding parameter. The following values are allowed:
- BIGINT
- BINARY
- BIT
- CHAR
- DATE
- DECIMAL
- DOUBLE
- FLOAT
- INTEGER
- LONGVARBINARY
- LONGVARCHAR
- NUMERIC
- REAL
- SMALLINT
- TIME
- TIMESTAMP
- TINYINT
- VARBINARY
- VARCHAR
The values of the type attribute may be supplied in both upper and lower case.
Each particular data source, as well as each particular implementation of the stylesheet processor, may support only some of the values listed above.
The optional attribute precision is used to specify the generic precision of the parameter. The interpretation of the precision depends on the parameter type.
If the parameter type is CHAR, VARCHAR, LONGVARCHAR, BINARY, VARBINARY, LONGVARBINARY, TIME or TIMESTAMP, the precision is set to the maximum column length.
If the parameter type is DECIMAL, NUMERIC, FLOAT, REAL or DOUBLE, the precision is set to the precision of the corresponding column.
For other data types, the precision is not required.
The optional attribute scale is used to specify the generic scale of the parameter. The interpretation of the scale depends on the parameter type.
If the parameter type is TIME or TIMESTAMP, the scale is set to the precision of the corresponding column.
If the parameter type is DECIMAL or NUMERIC, the scale is set to the scale of the corresponding column.
For other data types, the scale is not required.
Each particular data source may ignore precision and/or scale values for some data types, even if these values are defined as required by this specification.
3.9 Specifying Parameter Values
When the prepared statement containing parameter markers is executed, the actual parameter values should be supplied. The sql:with-param extension element is used to describe the actual parameter values.
The sql:with-param elements may be included in a stylesheet as children of sql:execute and sql:for-each elements. The number of sql:with-param children should be equal to the number of parameter markers in the corresponding SQL statement. If sql:with-param elements are children of the sql:for-each element, they must precede all other children that constitute the sql:for-each element content.
The sql:with-param extension element has the following syntax:
The required value attribute is the attribute value template that specifies the value of the corresponding parameter. Parameter values are supplied as strings. The underlying data access engine is responsible for conversion of string parameters to the appropriate SQL types.
The optional null attribute has a string value which serves as the NULL value indicator. If this attribute is supplied, and the attribute value template assigned to value attribute evaluates to a string that is equal to the NULL value indicator, the corresponding parameter is assigned the NULL value instead of the value specified by value attribute. This attribute was introduced in order to provide a way for passing NULL parameters to statements. For instance, null attribute may be assigned an empty string value if it is desired to treat parameter values evaluated to an empty string as NULL values.
4 Extension Functions
Extension functions may appear in XSLT expressions and attribute value templates in all contexts where functions are allowed. These functions are typically used to access results returned by SQL queries.
4.1 Fetching Columns from a Result Set
While the instantiation of the sql:for-each element is in progress, the columns of the current row of the corresponding result set may be accessed using the extension function sql:fetch. This function can be used in XSLT expressions, attribute value templates, etc. The values fetched from the result set columns may be therefore placed in the generated content or used as intermediate parameters during the transformation.
The sql:fetch function has the following syntax:
The first argument is required. It specifies the name of the column in the result set. The column name is treated case-insensitive. It is an error if the specified column is not found in the given result set.
The second argument is optional. Is specifies the cursor name. If this argument is not supplied, the empty string is assumed as the cursor name.
If any argument has the type other than string, it is converted to string prior to the function evaluation.
The sql:fetch function fetches the value of the specified column from the current row of the result set associated with the specified cursor name. This value is converted to string by the underlying data access engine and returned as the result of sql:fetch function. The NULL values are returned as empty strings.
4.2 Testing the Status of Columns from a Result Set
While the instantiation of the sql:for-each element is in progress, the function sql:is-null may be used to test whether any particular column of the current row of the corresponding result set has the NULL value.
The sql:is-null function has the following syntax:
The first argument is required. It specifies the name of the column in the result set. The column name is treated case-insensitive. It is an error if the specified column is not found in the given result set.
The second argument is optional. Is specifies the cursor name. If this argument is not supplied, the empty string is assumed as the cursor name.
If any argument has the type other than string, it is converted to string prior to the function evaluation.
The sql:is-null function tests the value of the specified column from the current row of the result set associated with the specified cursor name. This function returns true if this column has the NULL value and false otherwise.
5 Notation
The specification of each database access extension element contains a summary of its syntax. The notation is similar to that used in XSL Transformations (XSLT) Version 1.0 recommendation.
The names of required attributes are given in bold. Strings that occur in place of attribute values specify the value type of those attributes. Strings surrounded by curly braces indicate that the corresponding attribute values are interpreted as attribute value templates. Elements allowed not to be empty contain comments specifying the allowed content.
If an extension element allows several forms that have different semantics, these forms are described separately.
A References
B Element Syntax Summary
C Reference Implementation
The software product developed by Unicorn Enterprises SA, Unicorn XSLT Processor, Database Edition serves as the reference implementation for this specification.
The recent version of this product is available at https://www.unicorn-enterprises.com .
D Examples
This section contains fragments of XSLT stylesheets that demonstrate usage of database access extension elements defined in this specification. The full working versions of these and other examples can be found in the recent distribution of Unicorn XSLT Processor, Database Edition available at https://www.unicorn-enterprises.com .
All examples in this section are processing data stored in a single table. This table provides a model of the product catalog of an imaginary company that is selling computers using the e-commerce application. This is very simple model, however, it is sufficient to demonstrate basic functionality of XSLT database access extensions.
This table can be created using the following SQL statement:
CREATE TABLE MODEL (
CODE CHAR(64),
CATEGORY CHAR(64),
NAME CHAR(64),
CPU CHAR(64),
RAM CHAR(64),
HARD_DISK CHAR(64),
MONITOR CHAR(64),
OS CHAR(64),
PRICE REAL)
Example 1.
This example demonstrates the fragment of a stylesheet that reads data from the XML file and creates records in the relational database table. It is assumed in this example that the source XML file has the following structure:
<root>
<model>
<code>...</code>
<category>...</category>
<name>...</name>
<cpu>...</cpu>
<ram>...</ram>
<hard-disk>...</hard-disk>
<monitor>...</monitor>
<os>...</os>
<price>...</price>
</model>
...
</root>
Following is the stylesheet fragment:
<xsl:template match='/'>
<root>
<sql:connect
source="{$source}"
user="{$user}"
authentication="{$authentication}"
type="odbc"
connection-id="dbc1"/>
<sql:prepare
connection-id="dbc1"
sql="INSERT INTO MODEL (CODE, CATEGORY, NAME, CPU, RAM,
HARD_DISK, MONITOR, OS, PRICE)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
statement-id="stmt1">
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="REAL"/>
</sql:prepare>
<xsl:apply-templates/>
</root>
</xsl:template>
<xsl:template match="model">
<sql:execute statement-id="stmt1">
<sql:with-param value="{code}"/>
<sql:with-param value="{category}"/>
<sql:with-param value="{name}"/>
<sql:with-param value="{cpu}"/>
<sql:with-param value="{ram}"/>
<sql:with-param value="{hard-disk}"/>
<sql:with-param value="{monitor}"/>
<sql:with-param value="{os}"/>
<sql:with-param value="{price}"/>
</sql:execute>
</xsl:template>
This fragment is using prepared execution of SQL statements. Note that the output document generated by this stylesheet is not important; it contains just a single root element.
Example 2.
This example demonstrates the same functionality as the previous one, but the direct execution is used here:
<xsl:template match='/'>
<root>
<sql:connect
source="{$source}"
user="{$user}"
authentication="{$authentication}"
type="odbc"
connection-id="dbc1"/>
<xsl:apply-templates/>
</root>
</xsl:template>
<xsl:template match="model">
<sql:execute
connection-id="dbc1"
sql="INSERT INTO MODEL (CODE, CATEGORY, NAME, CPU, RAM,
HARD_DISK, MONITOR, OS, PRICE)
VALUES ('{code}', '{category}', '{name}', '{cpu}', '{ram}',
'{hard-disk}', '{monitor}', '{os}', '{price}')" />
</xsl:template>
Example 3.
In this example, the stylesheet is used to select all records with the given value of the CATEGORY column and to form the output document that contains the XML representation of selected data. The category value used for selection may be supplied as an external stylesheet parameter.
<xsl:param name="category" select="'Desktop'"/>
<xsl:template match='/'>
<root>
<sql:connect
source="{$source}"
user="{$user}"
authentication="{$authentication}"
type="odbc"
connection-id="dbc1"/>
<sql:prepare
connection-id="dbc1"
sql="SELECT CODE, CATEGORY, NAME, CPU, RAM,
HARD_DISK, MONITOR, OS, PRICE FROM MODEL
WHERE CATEGORY=? ORDER BY CODE"
statement-id="stmt1">
<sql:param type="CHAR" precision="50"/>
</sql:prepare>
<sql:for-each statement-id="stmt1">
<sql:with-param value="{$category}"/>
<model>
<code><xsl:value-of select="sql:fetch('CODE')"/></code>
<category><xsl:value-of select="sql:fetch('CATEGORY')"/></category>
<name><xsl:value-of select="sql:fetch('NAME')"/></name>
<cpu><xsl:value-of select="sql:fetch('CPU')"/></cpu>
<ram><xsl:value-of select="sql:fetch('RAM')"/></ram>
<hard-disk><xsl:value-of select="sql:fetch('HARD_DISK')"/></hard-disk>
<monitor><xsl:value-of select="sql:fetch('MONITOR')"/></monitor>
<os><xsl:value-of select="sql:fetch('OS')"/></os>
<price><xsl:value-of select="sql:fetch('PRICE')"/></price>
</model>
</sql:for-each>
</root>
</xsl:template>
This fragment is using prepared execution of SQL statements. Note that the source XML document is not important; it may contain just a single root element.
Example 4.
This example demonstrates the same functionality as the previous one, but the direct execution is used here:
<xsl:param name="category" select="'Desktop'"/>
<xsl:template match='/'>
<root>
<sql:connect
source="{$source}"
user="{$user}"
authentication="{$authentication}"
type="odbc"
connection-id="dbc1"/>
<sql:for-each
connection-id="dbc1"
sql="SELECT CODE, CATEGORY, NAME, CPU, RAM,
HARD_DISK, MONITOR, OS, PRICE FROM MODEL
WHERE CATEGORY='{$category}' ORDER BY CODE">
<model>
<code><xsl:value-of select="sql:fetch('CODE')"/></code>
<category><xsl:value-of select="sql:fetch('CATEGORY')"/></category>
<name><xsl:value-of select="sql:fetch('NAME')"/></name>
<cpu><xsl:value-of select="sql:fetch('CPU')"/></cpu>
<ram><xsl:value-of select="sql:fetch('RAM')"/></ram>
<hard-disk><xsl:value-of select="sql:fetch('HARD_DISK')"/></hard-disk>
<monitor><xsl:value-of select="sql:fetch('MONITOR')"/></monitor>
<os><xsl:value-of select="sql:fetch('OS')"/></os>
<price><xsl:value-of select="sql:fetch('PRICE')"/></price>
</model>
</sql:for-each>
</root>
</xsl:template>
Example 5.
This example demonstrates the fragment of a stylesheet that reads the source XML document containing list of product categories and corresponding discount values. It modifies the value of the PRICE column of records that belong to the categories specified in the source XML document, subtracting the corresponding discount value.
This fragment is used for demonstration purposes only and may be very inefficient in the real life. The stored procedures are normally used in such cases in real database applications; however, the techniques demonstrated here may be used in these cases as well.
It is assumed that the source XML document has the following structure:
<root>
<update>
<category>...</category>
<discount>...</discount>
</update>
...
</root>
Following is the stylesheet fragment:
<xsl:template match='/'>
<root>
<sql:connect
source="{$source}"
user="{$user}"
authentication="{$authentication}"
type="odbc"
connection-id="dbc1"/>
<sql:prepare
connection-id="dbc1"
sql="SELECT CODE, PRICE FROM MODEL WHERE CATEGORY=?"
statement-id="stmt1">
<sql:param type="CHAR" precision="64"/>
</sql:prepare>
<sql:prepare
connection-id="dbc1"
sql="UPDATE MODEL SET PRICE=? WHERE CODE=?"
statement-id="stmt2">
<sql:param type="REAL"/>
<sql:param type="CHAR" precision="64"/>
</sql:prepare>
<xsl:apply-templates/>
</root>
</xsl:template>
<xsl:template match="update">
<sql:for-each statement-id="stmt1">
<sql:with-param value="{category}"/>
<sql:execute statement-id="stmt2">
<sql:with-param value="{sql:fetch('PRICE') - discount}"/>
<sql:with-param value="{sql:fetch('CODE')}"/>
</sql:execute>
</sql:for-each>
</xsl:template>
This fragment is using prepared execution of SQL statements. Note that the output document generated by this stylesheet is not important; it contains just a single root element.
Example 6.
This example demonstrates the same functionality as the previous one, but the direct execution is used here:
<xsl:template match='/'>
<root>
<sql:connect
source="{$source}"
user="{$user}"
authentication="{$authentication}"
type="odbc"
connection-id="dbc1"/>
<xsl:apply-templates/>
</root>
</xsl:template>
<xsl:template match="update">
<sql:for-each
connection-id="dbc1"
sql="SELECT CODE, PRICE FROM MODEL WHERE CATEGORY='{category}'">
<sql:execute
connection-id="dbc1"
sql="UPDATE MODEL
SET PRICE='{sql:fetch('PRICE') - discount}'
WHERE CODE='{sql:fetch('CODE')}'"/>
</sql:for-each>
</xsl:template>
Example 7.
This example demonstrates the fragment of a stylesheet that is using batch processing to read data from the XML file and to create records in the relational database table. It uses the same source XML data and produces the same result as the stylesheet described in the first example of this section.
<xsl:template match='/'>
<root>
<sql:connect
source="{$source}"
user="{$user}"
authentication="{$authentication}"
type="odbc"
connection-id="dbc1"/>
<sql:prepare
connection-id="dbc1"
sql="INSERT INTO MODEL (CODE, CATEGORY, NAME, CPU, RAM,
HARD_DISK, MONITOR, OS, PRICE)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
statement-id="stmt1">
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="CHAR" precision="64"/>
<sql:param type="REAL"/>
</sql:prepare>
<sql:batch statement-id="stmt1" select="/root/model">
<sql:with-param value="{code}"/>
<sql:with-param value="{category}"/>
<sql:with-param value="{name}"/>
<sql:with-param value="{cpu}"/>
<sql:with-param value="{ram}"/>
<sql:with-param value="{hard-disk}"/>
<sql:with-param value="{monitor}"/>
<sql:with-param value="{os}"/>
<sql:with-param value="{price}"/>
</sql:batch>
</root>
</xsl:template>
E Changes From the Previous Working Draft
The following is the summary of changes since the previous working draft.
The commit-mode attribute on sql:connect element was introduced to specify the transaction commit mode for the connection.
The sql:batch element was introduced to provide support for batch statement execution.
The sql:source element was introduced to provide support for execution of SQL statements located in external files.
The sql:transact element was introduced to provide support for transactions.
The null attribute on sql:with-param element was introduced to provide support for NULL parameter values.
The sql:is-null function was introduced to test whether the result set column has NULL value.