Click or drag to resize

XSLT Extension Functions

SQL Enlight analysis rules support several XPath extension functions and modules in order to refine XPath queries and enhance the programming power and flexibility of XPath.

This topic contains the following sections:

EXSLT Extensions - http://www.exslt.org/

Date and time related functions:

Namespace: http://exslt.org/dates-and-times

Reference: http://www.exslt.org/date

 

Math functions:

Namespace: http://exslt.org/math

Reference: http://www.exslt.org/math/

 

Random number helper functions:

Namespace: http://exslt.org/random

Reference: http://www.exslt.org/random/

 

Regular expression functions:

Namespace: http://exslt.org/regular-expressions

Reference: http://www.exslt.org/regexp/

 

Node-sets manipulation functions:

Namespace: http://exslt.org/sets

Reference: http://www.exslt.org/set/

 

String helper functions:

Namespace: http://exslt.org/strings

Reference: http://www.exslt.org/str/

Microsoft XPath Extension Functions

Microsoft provided XPath extension functions:

Namespace: urn:schemas-microsoft-com:xslt

Reference: http://msdn.microsoft.com/en-us/library/ms256453.aspx

XPath Functions

Standard XPath functions:

Reference: http://msdn.microsoft.com/en-us/library/ms256453.aspx

Ubitsoft XPath Extension Functions

Namespace: urn:ubitsoft-exslt-strings:xslt

This namespace contains string manipulation functions.

Function

Description

string str2:capiralize-case(string target) Upper case first char of the string.
number str2:compare(string first, string second,boolean ignoreCase) Performs string comparison.
boolean str2:contains(string source, string value,boolean ignoreCase) Checks whether the first argument string contains the second argument string.
boolean str2:ends-with(string target, string value) Returns true if the target string ends with the given value.
number str2:index-of(string target, string anyOf) Reports the index of the first occurrence of any character of the anyOf string inside the target string.
number str2:index-of(string target, string anyOf, number startIndex) Reports the index of the first occurrence of any character of the anyOf string inside the target string. The check starts at startIndex position in the target string.
number str2:index-of-string(string target, string value,boolean ignoreCase) Returns the position of the first occurrence of the value string inside the target string.
number str2:last-index-of(string target, string anyOf) Reports the index of the last occurrence of any character of the anyOf string inside the target string.
number str2:last-index-of(string target, string anyOf, number startIndex) Reports the index of the last occurrence of any character of the anyOf string inside the target string. The check starts at startIndex position in the target string.
number str2:last-index-of-string(string target, string value,boolean ignoreCase) Returns the position of the last occurrence of the value string inside the target string.
string str2:lower-case(string target) Converts string to lower case.
boolean str2:starts-with(string target, string value) Checks if the target strings starts with the given value string.
string str2:upper-case(string target) Converts string to upper case.

Namespace: urn:ubitsoft-exslt-sql:xslt

This namespace contains SQL identifiers handling helper functions.

Function

Description

string sql:delimit-identifier(string name) Create square brackets delimited single part identifier.
string sql:delimit-multipart-identifier(string name) Create multipart delimited identifier.
string sql:escape-bracket(string s) Escape sql closing bracket.
string sql:escape-quotes(string s) Escape quotes.
string sql:undelimit-identifier(string name) Undelimit identifier.
string sql:unescape-bracket(string s) Unescape bracket.
string sql:unescape-quotes(string s) Unescape quotes.

Namespace: urn:ubitsoft-exslt-context:xslt

This namespace contains SQL identifiers handling helper functions.

Function

Description

object cmn:if-true-else(boolean condition,object true-result,object false-result) Helper extension method that to return one of the two values depending on whether the condition is true or false.

Namespace: urn:ubitsoft-exslt-common:xslt

This namespace contains general XSLT and XPATH helper functions.

Function

Description

node-set ctx:execute-query(string server, string sql)

Executes the provided SQL code and returns result as node set.

The extension method uses Integrated Security to connect to the database and requires that the query uses the FOR XML clause.

The script is executed inside a explicit transaction having isolation level READ UNCOMMITTED.

node-set ctx:execute-query(string server, string database, string user, string password, string sql)

Executes the provided SQL code and returns result as node set.

The extension method uses SQL Server Authentication to connect to the server and requires that the query uses the FOR XML clause.

The script is executed inside a explicit transaction having isolation level READ UNCOMMITTED.

number ctx:execute-non-query(string server, string sql)

Executes the provided SQL code and returns the number of affected records.

The extension method uses Integrated Security to connect to the server and requires that the query uses the FOR XML clause.

The script is executed without explicit transaction.

number ctx:execute-non-query(string server, string database, string user, string password, string sql)

Executes the provided SQL code and returns the number of affected records.

The extension method uses SQL Server Authentication to connect to the server and requires that the query uses the FOR XML clause.

The script is executed without explicit transaction.

node-set ctx:execute-show-plan(node-set batch,string server,string database, string sql)

Returns the XML query execution plan for the provided SQL code as node set.

The extension method uses Integrated Security to connect to the database and sets the SHOWPLAN_XML ON option before executing the SQL code.

The script is executed inside a explicit transaction having isolation level READ UNCOMMITTED.

The changes made inside the transaction are not committed, but discarded.

node-set ctx:execute-show-plan(node-set batch,string server, string database, string user, string password, string sql)

Returns the XML query execution plan for the provided SQL code as node set.

The extension method uses SQL Server Authentication to connect to the database and sets the SHOWPLAN_XML ON option before executing the SQL code.

The script is executed inside a explicit transaction having isolation level READ UNCOMMITTED.

The changes made inside the transaction are not committed, but discarded.

node-set ctx:execute-show-plan(node-set batch,string server,string database, string sql,string mode)

Returns the XML query execution plan for the provided SQL code as node set.

Because the CREATE/ALTER PROCEDURE / VIEW T-SQL statements do not produce XML execution plans, the 'mode' parameter is provided to control whether the execute-show-plan function to attempt rewriting these statements in a way that it to be possible for an execution plan to be generated.

The rewriting basically extracts the statement body (e.g. the SELECT statement from CREATE VIEW or the stored procedure body from CREATE PROCEDURE) and uses it to get the XML plan.

The 'mode' parameter can have the following values:

  • expandviews - Only CREATE/ALTER VIEW statements are expanded before generating execution plan.
  • expandprocedures - Only CREATE/ALTER PROCEDURE statements are expanded before generating execution plan.
  • expandall - Both types of CREATE/ALTER VIEW and CREATE/ALTER PROCEDURE statements are expanded before generating execution plan.
  • noexpand - Query execution plan is generated without modifying the statements.

The extension method uses Integrated Security to connect to the database and sets the SHOWPLAN_XML ON option before executing the SQL code.

The script is executed inside a explicit transaction having isolation level READ UNCOMMITTED.

The changes made inside the transaction are not committed, but discarded.

node-set ctx:execute-show-plan(node-set batch,string server, string database, string user, string password, string sql,string mode)

Returns the XML query execution plan for the provided SQL code as node set.

Because the CREATE/ALTER PROCEDURE / VIEW T-SQL statements do not produce XML execution plans, the 'mode' parameter is provided to control whether the execute-show-plan function to attempt rewriting these statements in a way that it to be possible for an execution plan to be generated.

The rewriting basically extracts the statement body (e.g. the SELECT statement from CREATE VIEW or the stored procedure body from CREATE PROCEDURE) and uses it to get the XML plan.

The 'mode' parameter can have the following values:

  • expandviews - Only CREATE/ALTER VIEW statements are expanded before generating execution plan.
  • expandprocedures - Only CREATE/ALTER PROCEDURE statements are expanded before generating execution plan.
  • expandall - Both types of CREATE/ALTER VIEW and CREATE/ALTER PROCEDURE statements are expanded before generating execution plan.
  • noexpand - Query execution plan is generated without modifying the statements.

The extension method uses Integrated Security to connect to the database and sets the SHOWPLAN_XML ON option before executing the SQL code.

The script is executed inside a explicit transaction having isolation level READ UNCOMMITTED.

The changes made inside the transaction are not committed, but discarded.