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 an 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 an 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 an 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 an 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 an 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 an 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 attempts rewriting these statements in a way that it is 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 an explicit transaction having isolation level READ UNCOMMITTED.

The changes made inside the transaction are not committe, 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 attempts rewriting these statements in a way that it is 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 an execution plan.
  • expandprocedures – Only CREATE/ALTER PROCEDURE statements are expanded before generating an execution plan.
  • expandall – Both types of CREATE/ALTER VIEW and CREATE/ALTER PROCEDURE statements are expanded before generating an execution plan.
  • noexpand – A 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 an explicit transaction having isolation level READ UNCOMMITTED.

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