2.4. Scalar Functions
2.4. Scalar Functions 2.4.1. Scalar Functions JBoss Data Virtualization provides an extensive set of built-in scalar functions. See Section 2.1, "SQL Support" and Section 3.1, "Supported Types" . In addition, JBoss Data Virtualization provides the capability for user defined functions or UDFs. See Red Hat JBoss Development Guide: Server Development for adding UDFs. Once added, UDFs may be called like any other function. 2.4.2. Numeric Functions Numeric functions return numeric values (integer, long, float, double, biginteger, bigdecimal). They generally take numeric values as inputs, though some take strings. Table 2.1. Numeric Functions Function Definition Data Type Constraint + - * / Standard numeric operators x in {integer, long, float, double, biginteger, bigdecimal}, return type is same as x Note The precision and scale of non-bigdecimal arithmetic function functions results matches that of Java. The results of bigdecimal operations match Java, except for division, which uses a preferred scale of max(16, dividend.scale + divisor.precision + 1), which then has trailing zeros removed by setting the scale to max(dividend.scale, normalized scale). ABS(x) Absolute value of x See standard numeric operators above ACOS(x) Arc cosine of x x in {double, bigdecimal}, return type is double ASIN(x) Arc sine of x x in {double, bigdecimal}, return type is double ATAN(x) Arc tangent of x x in {double, bigdecimal}, return type is double ATAN2(x,y) Arc tangent of x and y x, y in {double, bigdecimal}, return type is double CEILING(x) Ceiling of x x in {double, float}, return type is double COS(x) Cosine of x x in {double, bigdecimal}, return type is double COT(x) Cotangent of x x in {double, bigdecimal}, return type is double DEGREES(x) Convert x degrees to radians x in {double, bigdecimal}, return type is double EXP(x) e^x x in {double, float}, return type is double FLOOR(x) Floor of x x in {double, float}, return type is double FORMATBIGDECIMAL(x, y) Formats x using format y x is bigdecimal, y is string, returns string FORMATBIGINTEGER(x, y) Formats x using format y x is biginteger, y is string, returns string FORMATDOUBLE(x, y) Formats x using format y x is double, y is string, returns string FORMATFLOAT(x, y) Formats x using format y x is float, y is string, returns string FORMATINTEGER(x, y) Formats x using format y x is integer, y is string, returns string FORMATLONG(x, y) Formats x using format y x is long, y is string, returns string LOG(x) Natural log of x (base e) x in {double, float}, return type is double LOG10(x) Log of x (base 10) x in {double, float}, return type is double MOD(x, y) Modulus (remainder of x / y) x in {integer, long, float, double, biginteger, bigdecimal}, return type is same as x PARSEBIGDECIMAL(x, y) Parses x using format y x, y are strings, returns bigdecimal PARSEBIGINTEGER(x, y) Parses x using format y x, y are strings, returns biginteger PARSEDOUBLE(x, y) Parses x using format y x, y are strings, returns double PARSEFLOAT(x, y) Parses x using format y x, y are strings, returns float PARSEINTEGER(x, y) Parses x using format y x, y are strings, returns integer PARSELONG(x, y) Parses x using format y x, y are strings, returns long PI() Value of Pi return is double POWER(x,y) x to the y power x in {double, bigdecimal, biginteger}, return is the same type as x RADIANS(x) Convert x radians to degrees x in {double, bigdecimal}, return type is double RAND() Returns a random number, using generator established so far in the query or initializing with system clock if necessary. Returns double. RAND(x) Returns a random number, using new generator seeded with x. x is integer, returns double. ROUND(x,y) Round x to y places; negative values of y indicate places to the left of the decimal point x in {integer, float, double, bigdecimal} y is integer, return is same type as x SIGN(x) 1 if x > 0, 0 if x = 0, -1 if x < 0 x in {integer, long, float, double, biginteger, bigdecimal}, return type is integer SIN(x) Sine value of x x in {double, bigdecimal}, return type is double SQRT(x) Square root of x x in {long, double, bigdecimal}, return type is double TAN(x) Tangent of x x in {double, bigdecimal}, return type is double BITAND(x, y) Bitwise AND of x and y x, y in {integer}, return type is integer BITOR(x, y) Bitwise OR of x and y x, y in {integer}, return type is integer BITXOR(x, y) Bitwise XOR of x and y x, y in {integer}, return type is integer BITNOT(x) Bitwise NOT of x x in {integer}, return type is integer 2.4.3. Parsing Numeric Data Types from Strings JBoss Data Virtualization provides a set of functions to parse formatted strings as various numeric data types: parseDouble - parses a string as a double parseFloat - parses a string as a float parseLong - parses a string as a long parseInteger - parses a string as an integer For each function, you have to provide the formatting of the string. The formatting follows the convention established by the java.text.DecimalFormat class. See examples below. Input String Function Call to Format String Output Value Output Data Type 'USD25.30' parseDouble(cost, 'USD#,##0.00;(USD#,##0.00)') 25.3 double '25%' parseFloat(percent, '#,##0%') 25 float '2,534.1' parseFloat(total, '#,##0.###;-#,##0.###') 2534.1 float '1.234E3' parseLong(amt, '0.###E0') 1234 long '1,234,567' parseInteger(total, '#,##0;-#,##0') 1234567 integer Note See http://download.oracle.com/javase/6/docs/api/java/text/DecimalFormat.html for more information. 2.4.4. Formatting Numeric Data Types as Strings JBoss Data Virtualization provides a set of functions to convert numeric data types into formatted strings: formatDouble - formats a double as a string formatFloat - formats a float as a string formatLong - formats a long as a string formatInteger - formats an integer as a string For each function, you have to provide the formatting of the string. The formatting follows the convention established by the java.text.DecimalFormat class. See examples below. Input Value Input Data Type Function Call to Format String Output String 25.3 double formatDouble(cost, 'USD#,##0.00;(USD#,##0.00)') 'USD25.30' 25 float formatFloat(percent, '#,##0%') '25%' 2534.1 float formatFloat(total, '#,##0.###;-#,##0.###') '2,534.1' 1234 long formatLong(amt, '0.###E0') '1.234E3' 1234567 integer formatInteger(total, '#,##0;-#,##0') '1,234,567' Note See http://download.oracle.com/javase/6/docs/api/java/text/DecimalFormat.html for more information. 2.4.5. String Functions String functions generally take strings as inputs and return strings as outputs. Unless specified, all of the arguments and return types in the following table are strings and all indexes are one-based. The zero index is considered to be before the start of the string. Important Non-ASCII range characters or integers used by ASCII(x) , CHR(x) , and CHAR(x) may produce different results or exceptions depending on where the function is evaluated (JBoss Data Virtualization vs. source). JBoss Data Virtualization uses Java default int to char and char to int conversions, which operates over UTF16 values. Table 2.2. String Functions Function Definition DataType Constraint x || y Concatenation operator x,y in {string}, return type is string ASCII(x) Provide ASCII value of the left most character in x. The empty string will return null. return type is integer CHR(x) CHAR(x) Provide the character for ASCII value x x in {integer} CONCAT(x, y) Concatenates x and y with ANSI semantics. If x and/or y is null, returns null. x, y in {string} CONCAT2(x, y) Concatenates x and y with non-ANSI null semantics. If x and y is null, returns null. If only x or y is null, returns the other value. x, y in {string} ENDSWITH(x, y) Checks if y ends with x. If only x or y is null, returns null. x, y in {string}, returns boolean INITCAP(x) Make first letter of each word in string x capital and all others lowercase x in {string} INSERT(str1, start, length, str2) Insert string2 into string1 str1 in {string}, start in {integer}, length in {integer}, str2 in {string} LCASE(x) Lowercase of x x in {string} LEFT(x, y) Get left y characters of x x in {string}, y in {integer}, return string LENGTH(x) Length of x return type is integer LOCATE(x, y) Find position of x in y starting at beginning of y x in {string}, y in {string}, return integer LOCATE(x, y, z) Find position of x in y starting at z x in {string}, y in {string}, z in {integer}, return integer LPAD(x, y) Pad input string x with spaces on the left to the length of y x in {string}, y in {integer}, return string LPAD(x, y, z) Pad input string x on the left to the length of y using character z x in {string}, y in {string}, z in {character}, return string LTRIM(x) Left trim x of blank characters x in {string}, return string QUERYSTRING(path [, expr [AS name] ...]) Returns a properly encoded query string appended to the given path. Null valued expressions are omitted, and a null path is treated as ''. Names are optional for column reference expressions. e.g. QUERYSTRING('path', 'value' as "&x", ' & ' as y, null as z) returns 'path?%26x=value&y=%20%26%20' path, expr in {string}. name is an identifier REPEAT(str1,instances) Repeat string1 a specified number of times str1 in {string}, instances in {integer} return string RIGHT(x, y) Get right y characters of x x in {string}, y in {integer}, return string RPAD(input string x, pad length y) Pad input string x with spaces on the right to the length of y x in {string}, y in {integer}, return string RPAD(x, y, z) Pad input string x on the right to the length of y using character z x in {string}, y in {string}, z in {character}, return string RTRIM(x) Right trim x of blank characters x is string, return string SPACE(x) Repeats space x times x in {integer} SUBSTRING(x, y) SUBSTRING(x FROM y) Get substring from x, from position y to the end of x y in {integer} SUBSTRING(x, y, z) SUBSTRING(x FROM y FOR z) Get substring from x from position y with length z y, z in {integer} TO_CHARS(x, encoding) Return a CLOB from the BLOB with the given encoding. BASE64, HEX, and the built-in Java Charset names are valid values for the encoding. Note For charsets, unmappable chars will be replaced with the charset default character. Binary formats, such as BASE64, will error in their conversion to bytes if an unrecognizable character is encountered. x is a BLOB, encoding is a string, and returns a CLOB TO_BYTES(x, encoding) Return a BLOB from the CLOB with the given encoding. BASE64, HEX, and the builtin Java Charset names are valid values for the encoding. x in a CLOB, encoding is a string, and returns a BLOB TRANSLATE(x, y, z) Translate string x by replacing each character in y with the character in z at the same position. Note that the second arg (y) and the third arg (z) must be the same length. If they are not equal, Red Hat JBoss data Virtualization throws this exception: 'TEIID30404 Source and destination character lists must be the same length.' x in {string} TRIM([[LEADING|TRAILING|BOTH] [x] FROM] y) Trim character x from the leading, trailing, or both ends of string y. If LEADING/TRAILING/BOTH is not specified, BOTH is used by default. If no trim character x is specified, a blank space ' ' is used for x by default. x in {character}, y in {string} UCASE(x) Uppercase of x x in {string} UNESCAPE(x) Unescaped version of x. Possible escape sequences are \b - backspace, \t - tab, \n - line feed, \f - form feed, \r - carriage return. \uXXXX, where X is a hex value, can be used to specify any unicode character. \XXX, where X is an octal digit, can be used to specify an octal byte value. If any other character appears after an escape character, that character will appear in the output and the escape character will be ignored. x in {string} 2.4.5.1. Replacement Functions Use REPLACE to replace all occurrences of a given string with another: This will replace all occurrences of y with z in x. (x, y, z are strings and the return value is a string.) REGEXP_REPLACE replaces one or all occurrences of a given pattern with another string: This will replace one or more occurrences of pattern with sub in str. All arguments are strings and the return value is a string. The pattern parameter is expected to be a valid Java regular expression. The flags argument can be any concatenation of any of the valid flags with the following meanings: Table 2.3. Flags Flag Name Meaning g global Replace all occurrences, not just the first. m multiline Match over multiple lines. i case insensitive Match without case sensitivity. Here is how you return xxbye Wxx using the global and case insensitive options: 2.4.6. Date/Time Functions Date and time functions return or operate on dates, times, or timestamps. Parse and format Date/Time functions use the convention established within the java.text.SimpleDateFormat class to define the formats you can use with these functions. Table 2.4. Date and Time Functions Function Definition Datatype Constraint CURDATE() Return current date returns date CURTIME() Return current time returns time NOW() Return current timestamp (date and time) returns timestamp DAYNAME(x) Return name of day in the default locale x in {date, timestamp}, returns string DAYOFMONTH(x) Return day of month x in {date, timestamp}, returns integer DAYOFWEEK(x) Return day of week (Sunday=1, Saturday=7) x in {date, timestamp}, returns integer DAYOFYEAR(x) Return day number x in {date, timestamp}, returns integer EXTRACT(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND FROM x) Return the given field value from the date value x. Produces the same result as the associated YEAR, MONTH, DAYOFMONTH, HOUR, MINUTE, SECOND functions. The SQL specification also allows for TIMEZONE_HOUR and TIMEZONE_MINUTE as extraction targets. In JBoss Data Virtualization, all date values are in the timezone of the server. x in {date, time, timestamp}, returns integer FORMATDATE(x, y) Format date x using format y x is date, y is string, returns string FORMATTIME(x, y) Format time x using format y x is time, y is string, returns string FORMATTIMESTAMP(x, y) Format timestamp x using format y x is timestamp, y is string, returns string FROM_UNIXTIME (unix_timestamp) Return the Unix timestamp (in seconds) as a Timestamp value Unix timestamp (in seconds) HOUR(x) Return hour (in military 24-hour format) x in {time, timestamp}, returns integer MINUTE(x) Return minute x in {time, timestamp}, returns integer MODIFYTIMEZONE (timestamp, startTimeZone, endTimeZone) Returns a timestamp based upon the incoming timestamp adjusted for the differential between the start and end time zones. i.e. if the server is in GMT-6, then modifytimezone({ts '2006-01-10 04:00:00.0'},'GMT-7', 'GMT-8') will return the timestamp {ts '2006-01-10 05:00:00.0'} as read in GMT-6. The value has been adjusted 1 hour ahead to compensate for the difference between GMT-7 and GMT-8. startTimeZone and endTimeZone are strings, returns a timestamp MODIFYTIMEZONE (timestamp, endTimeZone) Return a timestamp in the same manner as modifytimezone(timestamp, startTimeZone, endTimeZone), but will assume that the startTimeZone is the same as the server process. Timestamp is a timestamp; endTimeZone is a string, returns a timestamp MONTH(x) Return month x in {date, timestamp}, returns integer MONTHNAME(x) Return name of month in the default locale x in {date, timestamp}, returns string PARSEDATE(x, y) Parse date from x using format y x, y in {string}, returns date PARSETIME(x, y) Parse time from x using format y x, y in {string}, returns time PARSETIMESTAMP(x,y) Parse timestamp from x using format y x, y in {string}, returns timestamp QUARTER(x) Return quarter x in {date, timestamp}, returns integer SECOND(x) Return seconds x in {time, timestamp}, returns integer TIMESTAMPCREATE(date, time) Create a timestamp from a date and time date in {date}, time in {time}, returns timestamp TIMESTAMPADD(interval, count, timestamp) Add a specified interval (hour, day of week, month) to the timestamp, where intervals can be: SQL_TSI_FRAC_SECOND - fractional seconds (billionths of a second) SQL_TSI_SECOND - seconds SQL_TSI_MINUTE - minutes SQL_TSI_HOUR - hours SQL_TSI_DAY - days SQL_TSI_WEEK - weeks where Sunday is the first day SQL_TSI_MONTH - months SQL_TSI_QUARTER - quarters (3 months), where the first quarter is months 1-3 SQL_TSI_YEAR - years Note The full interval amount based upon calendar fields will be added. For example adding 1 QUARTER will move the timestamp up by three full months and not just to the start of the calendar quarter. The interval constant may be specified either as a string literal or a constant value. Interval in {string}, count in {integer}, timestamp in {date, time, timestamp} TIMESTAMPDIFF(interval, startTime, endTime) Calculates the date part intervals crossed between the two timestamps. interval is one of the same keywords as those used for TIMESTAMPADD. If (endTime > startTime), a positive number will be returned. If (endTime < startTime), a negative number will be returned. The date part difference is counted regardless of how close the timestamps are. For example, '2000-01-02 00:00:00.0' is still considered 1 hour ahead of '2000-01-01 23:59:59.999999'. Note TIMESTAMPDIFF typically returns an integer, however JBoss Data Virtualization returns a long. You will encounter an exception if you expect a value out of the integer range from a pushed down TIMESTAMPDIFF. Note The implementation of TIMESTAMPDIFF in versions returned values based upon the number of whole canonical interval approximations (365 days in a year, 91 days in a quarter, 30 days in a month, etc.) crossed. For example the difference in months between 2013-03-24 and 2013-04-01 was 0, but based upon the date parts crossed is 1. See the System Properties section in Red Hat JBoss Data Virtualization Administration and Configuration Guide for backwards compatibility. Interval in {string}; startTime, endTime in {timestamp}, returns a long. WEEK(x) Return week in year (1-53). see also System Properties for customization. x in {date, timestamp}, returns integer YEAR(x) Returns four-digit year. x in {date, timestamp}, returns integer UNIX_TIMESTAMP (unix_timestamp) Returns the long Unix timestamp (in seconds). unix_timestamp String in the default format of yyyy/mm/dd hh:mm:ss 2.4.7. Parsing Date Data Types from Strings JBoss Data Virtualization does not implicitly convert strings that contain dates presented in different formats, such as '19970101' and '31/1/1996' to date-related data types. You can, however, use the following functions to explicitly convert strings with a different format to the appropriate data type: parseDate parseTime parseTimestamp For each function, you have to provide the formatting of the string. The formatting follows the convention established by the java.text.SimpleDateFormat class. See examples below. Table 2.5. Functions to Parse Dates String Function Call To Parse String '19970101' parseDate(myDateString, 'yyyyMMdd') '31/1/1996' parseDate(myDateString, 'dd''/''MM''/''yyyy') '22:08:56 CST' parseTime (myTime, 'HH:mm:ss z') '03.24.2003 at 06:14:32' parseTimestamp(myTimestamp, 'MM.dd.yyyy ''at'' hh:mm:ss') Note Formatted strings will be based on your default Java locale. 2.4.8. Specifying Time Zones Time zones can be specified in several formats. Common abbreviations such as EST for "Eastern Standard Time" are allowed but discouraged, as they can be ambiguous. Unambiguous time zones are defined in the form continent or ocean/largest city. For example, America/New_York, America/Buenos_Aires, or Europe/London. Additionally, you can specify a custom time zone by GMT offset: GMT[+/-]HH:MM. For example: GMT-05:00 2.4.9. Type Conversion Functions Within your queries, you can convert between data types using the CONVERT or CAST keyword. Also see Section 3.2, "Type Conversions" . Table 2.6. Type Conversion Functions Function Definition CONVERT(x, type) Convert x to type, where type is a JBoss Data Virtualization Base Type CAST(x AS type) Convert x to type, where type is a JBoss Data Virtualization Base Type These functions are identical other than syntax; CAST is the standard SQL syntax, CONVERT is the standard JDBC/ODBC syntax. 2.4.10. Choice Functions Choice functions provide a way to select from two values based on some characteristic of one of the values. Table 2.7. Type Conversion Functions Function Definition Data Type Constraint COALESCE(x,y+) Returns the first non-null parameter x and all y's can be any compatible types IFNULL(x,y) If x is null, return y; else return x x, y, and the return type must be the same type but can be any type NVL(x,y) If x is null, return y; else return x x, y, and the return type must be the same type but can be any type NULLIF(param1, param2) Equivalent to case when (param1 = param2) then null else param1 param1 and param2 must be compatible comparable types Note IFNULL and NVL are aliases of each other. They are the same function. 2.4.11. Decode Functions Decode functions allow you to have JBoss Data Virtualization examine the contents of a column in a result set and alter, or decode, the value so that your application can better use the results. Table 2.8. Decode Functions Function Definition Data Type Constraint DECODESTRING(x, y [, z]) Decode column x using value pairs in y (with optional delimiter, z) and return the decoded column as a set of strings. Warning Deprecated. Use a CASE expression instead. All string DECODEINTEGER(x, y [, z]) Decode column x using value pairs in y (with optional delimiter z) and return the decoded column as a set of integers. Warning Deprecated. Use a CASE expression instead. All string parameters, return integer Within each function call, you include the following arguments: x is the input value for the decode operation. This will generally be a column name. y is the literal string that contains a delimited set of input values and output values. z is an optional parameter on these methods that allows you to specify what delimiter the string specified in y uses. For example, your application might query a table called PARTS that contains a column called IS_IN_STOCK which contains a Boolean value that you need to change into an integer for your application to process. In this case, you can use the DECODEINTEGER function to change the Boolean values to integers: SELECT DECODEINTEGER(IS_IN_STOCK, 'false, 0, true, 1') FROM PartsSupplier.PARTS; When JBoss Data Virtualization encounters the value false in the result set, it replaces the value with 0. If, instead of using integers, your application requires string values, you can use the DECODESTRING function to return the string values you need: SELECT DECODESTRING(IS_IN_STOCK, 'false, no, true, yes, null') FROM PartsSupplier.PARTS; In addition to two input/output value pairs, this sample query provides a value to use if the column does not contain any of the preceding input values. If the row in the IS_IN_STOCK column does not contain true or false, JBoss Data Virtualization inserts a null into the result set. When you use these DECODE functions, you can provide as many input/output value pairs as you would like within the string. By default, JBoss Data Virtualization expects a comma delimiter, but you can add a third parameter to the function call to specify a different delimiter: SELECT DECODESTRING(IS_IN_STOCK, 'false:no:true:yes:null',':') FROM PartsSupplier.PARTS; You can use keyword null in the DECODE string as either an input value or an output value to represent a null value. However, if you need to use the literal string null as an input or output value (which means the word null appears in the column and not a null value) you can put the word in quotes: "null". SELECT DECODESTRING( IS_IN_STOCK, 'null,no,"null",no,nil,no,false,no,true,yes' ) FROM PartsSupplier.PARTS; If the DECODE function does not find a matching output value in the column and you have not specified a default value, the DECODE function will return the original value JBoss Data Virtualization found in that column. 2.4.12. Lookup Function The Lookup function provides a way to speed up access to values in a lookup table (also known as a code table or reference table). The Lookup function caches all key and return column pairs specified in the function for the given table. Subsequent lookups against the same table using the same key and return columns will use the cached values. This caching accelerates response time to queries that use the lookup tables. In the following example, based on the lookup table, codeTable , the following function will find the row where keyColumn has the value, keyValue , and return the associated returnColumn value (or null if no matching key is found). codeTable must be a string literal that is the fully qualified name of the target table. returnColumn and keyColumn must also be string literals and match corresponding column names in codeTable . keyValue can be any expression that must match the datatype of the keyColumn . The return data type matches that of returnColumn . Consider the following example in which the ISOCountryCodes table is used to translate country names to ISO codes: CountryName represents a key column and CountryCode represents the ISO code of the country. A query to this lookup table would provide a CountryName , in this case 'UnitedStates', and expect a CountryCode in response. Note JBoss Data Virtualization unloads these cached lookup tables when you stop and restart JBoss Data Virtualization. Thus, it is best not to use this function for data that is subject to updates or specific to a session or user (including row based security and column masking effects). It is best used for data that does not change over time. See the Red Hat JBoss Data Virtualization Administration and Configuration Guide for more on the caching aspects of the lookup function. Important The key column must contain unique values. If the column contains duplicate values, an exception will be thrown. 2.4.13. System Functions System functions provide access to information in JBoss Data Virtualization from within a query. Function Definition Data Type Constraint COMMANDPAYLOAD([key]) If the key parameter is provided, the command payload object is cast to a java.util.Properties object and the corresponding property value for the key is returned. If the key is not specified, the return value is the command payload toString value. The command payload is set by the TeiidStatement.setPayload method on the Data Virtualization JDBC API extensions on a per-query basis. key in {string}, return value is string ENV(key) Retrieve a system environment property. Note The only key specific to the current session is 'sessionid'. The preferred mechanism for getting the session id is with the session_id() function. Note To prevent untrusted access to system properties, this function is not enabled by default. The ENV function may be enabled via the allowEnvFunction property. key in {string}, return value is string SESSION_ID() Retrieve the string form of the current session id. return value is string USER() Retrieve the name of the user executing the query. return value is string CURRENT_DATABASE() Retrieve the catalog name of the database which, for the VDB, is the VDB name. return value is string TEIID_SESSION_GET(name) Retrieve the session variable. A null name will return a null value. Typically you will use the a get wrapped in a CAST to convert to the desired type. name in {string}, return value is object TEIID_SESSION_SET(name, value) Set the session variable. The value for the key or null will be returned. A set has no effect on the current transaction and is not affected by commit/rollback. name in {string}, value in {object}, return value is object. NODE_ID() This retrieves the node id. This is typically the system property value for jboss.node.name which is not set for Red Hat JDV embedded. The returned value is a string. 2.4.14. XML Functions XML functions allow you to work with XML data. The examples provided for the XML functions use this table structure: The table structure is populated with this example data: Table 2.9. Sample Data CustomerID CustomerName ContactName Address City PostalCode Country 87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland 88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil 89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA XMLCAST Cast to or from XML: The expression or type must be XML. The returned value will be a type. This is the same functionality as XMLTABLE uses to convert values to the desired runtime type, with the exception that array type targets are not supported with XMLCAST. XMLCOMMENT This returns an XML comment. The comment is a string. The returned value is XML. XMLCONCAT This returns XML with the concatenation of the given XML types. If a value is null, it will be ignored. If all values are null, null is returned. This is how you concatenate two or more XML fragments: The content is XML. The returned value is XML. XMLELEMENT Returns an XML element with the given name and content. If the content value is of a type other than XML, it will be escaped when added to the parent element. Null content values are ignored. Whitespace in XML or the string values of the content is preserved, but no whitespace is added between content values. XMLNAMESPACES is used to provide namespace information. NO DEFAULT is equivalent to defining the default namespace to the null URI - xmlns="" . Only one DEFAULT or NO DEFAULT namespace item may be specified. The namespace prefixes xmlns and xml are reserved. If an attribute name is not supplied, the expression must be a column reference, in which case the attribute name will be the column name. Null attribute values are ignored. For example, with an xml_value of <doc/>, returns name and prefix are identifiers. uri is a string literal. content can be any type. Return value is XML. The return value is valid for use in places where a document is expected. XMLFOREST Returns an concatenation of XML elements for each content item. See XMLELEMENT for the definition of NSP. If a name is not supplied for a content item, the expression must be a column reference, in which case the element name will be a partially escaped version of the column name. name is an identifier. content can be any type. Return value is XML. You can use XMLFORREST to simplify the declaration of multiple XMLELEMENTS, XMLFOREST function allows you to process multiple columns at once: XMLAGG XMLAGG is an aggregate function, that takes a collection of XML elements and returns an aggregated XML document. In the XMLElement example, each row in the Customer table generates a row of XML if there are multiple rows matching the criteria. This will be valid XML, but it will not be well formed, because it lacks the root element. Use XMLAGG to correct that: XMLPARSE Returns an XML type representation of the string value expression. If DOCUMENT is specified, then the expression must have a single root element and may or may not contain an XML declaration. If WELLFORMED is specified then validation is skipped; this is especially useful for CLOB and BLOB known to already be valid. expr in {string, clob, blob and varbinary}. Return value is XML. If DOCUMENT is specified then the expression must have a single root element and may or may not contain an XML declaration. If WELLFORMED is specified then validation is skipped; this is especially useful for CLOB and BLOB known to already be valid. Will return a SQLXML with contents: XMLPI Returns an XML processing instruction. name is an identifier. content is a string. Return value is XML. XMLQUERY Returns the XML result from evaluating the given xquery . See XMLELEMENT for the definition of NSP. Namespaces may also be directly declared in the XQuery prolog. The optional PASSING clause is used to provide the context item, which does not have a name, and named global variable values. If the XQuery uses a context item and none is provided, then an exception will be raised. Only one context item may be specified and should be an XML type. All non-context non-XML passing values will be converted to an appropriate XML type. The ON EMPTY clause is used to specify the result when the evaluated sequence is empty. EMPTY ON EMPTY, the default, returns an empty XML result. NULL ON EMPTY returns a null result. xquery in string. Return value is XML. Note XMLQUERY is part of the SQL/XML 2006 specification. See also XMLTABLE. XMLEXISTS Returns true if a non-empty sequence would be returned by evaluating the given xquery. Namespaces may also be directly declared in the xquery prolog. The optional PASSING clause is used to provide the context item, which does not have a name, and named global variable values. If the xquery uses a context item and none is provided, then an exception will be raised. Only one context item may be specified and should be an XML type. All non-context non-XML passing values will be converted to an appropriate XML type. Null/Unknown will be returned if the context item evaluates to null. xquery in string. Return value is boolean. XMLEXISTS is part of the SQL/XML 2006 specification. XMLSERIALIZE Returns a character type representation of the XML expression. datatype may be character (string, varchar, clob) or binary (blob, varbinary). CONTENT is the default. If DOCUMENT is specified and the XML is not a valid document or fragment, then an exception is raised. Return value matches data type. If no data type is specified, then CLOB will be assumed. The encoding enc is specified as an identifier. A character serialization may not specify an encoding. The version ver is specified as a string literal. If a particular XMLDECLARATION is not specified, then the result will have a declaration only if performing a non UTF-8/UTF-16 or non version 1.0 document serialization or the underlying XML has an declaration. If CONTENT is being serialized, then the declaration will be omitted if the value is not a document or element. The following example produces a BLOB of XML in UTF-16 including the appropriate byte order mark of FE FF and XML declaration: XMLTEXT This returns XML text. The text is a string and the returned value is XML. XSLTRANSFORM Applies an XSL stylesheet to the given document. doc and xsl in {string, clob, xml}. Return value is a CLOB. If either argument is null, the result is null. XPATHVALUE Applies the XPATH expression to the document and returns a string value for the first matching result. For more control over the results and XQuery, use the XMLQUERY function. Matching a non-text node will still produce a string result, which includes all descendant text nodes. doc and xpath in {string, clob, xml}. Return value is a string. When the input document utilizes namespaces, it is sometimes necessary to specify XPATH that ignores namespaces. For example, given the following XML, the following function results in 'Hello World'. 2.4.15. JSON Functions JSON functions provide functionality for working with JSON (JavaScript Object Notation) data. JSONTOXML Returns an XML document from JSON. The appropriate UTF encoding (8, 16LE. 16BE, 32LE, 32BE) will be detected for JSON BLOBS. If another encoding is used, see the TO_CHARS function (see Section 2.4.5, "String Functions" ). rootElementName is a string, json is in {clob, blob}. Return value is XML. The result is always a well-formed XML document. The mapping to XML uses the following rules: The current element name is initially the rootElementName , and becomes the object value name as the JSON structure is traversed. All element names must be valid XML 1.1 names. Invalid names are fully escaped according to the SQLXML specification. Each object or primitive value will be enclosed in an element with the current name. Unless an array value is the root, it will not be enclosed in an additional element. Null values will be represented by an empty element with the attribute xsi:nil="true" Boolean and numerical value elements will have the attribute xsi:type set to boolean and decimal respectively. Example 2.1. Sample JSON to XML for jsonToXml('person', x) JSON: XML: <?xml version="1.0" ?><person><firstName>John</firstName><children>Randy</children><children>Judy</children></person> Example 2.2. Sample JSON to XML for jsonToXml('person', x) with a root array. JSON: XML (Notice there is an extra "person" wrapping element to keep the XML well-formed): <?xml version="1.0" ?><person><person><firstName>George</firstName></person><person><firstName>Jerry</firstName></person></person> JSON: Example 2.3. Sample JSON to XML for jsonToXml('root', x) with an invalid name. XML: Example 2.4. Sample JSON to XML for jsonToXml('root', x) with an invalid name. JSONARRAY Returns a JSON array. value is any object convertable to a JSON value (see Section 2.4.16, "Conversion to JSON" ). Return value is a CLOB marked as being valid JSON. Null values will be included in the result as null literals. For example: returns JSONOBJECT Returns a JSON object. value is any object convertable to a JSON value (see Section 2.4.16, "Conversion to JSON" ). Return value is a clob marked as being valid JSON. Null values will be included in the result as null literals. If a name is not supplied and the expression is a column reference, the column name will be used otherwise exprN will be used where N is the 1-based index of the value in the JSONARRAY expression. For example: returns JSONPARSE Validates and returns a JSON result. value is blob with an appropriate JSON binary encoding (UTF-8, UTF-16, or UTF-32) or clob. wellformed is a boolean indicating that validation should be skipped. Return value is a CLOB marked as being valid JSON. A null for either input will return null. JSONARRAY_AGG This creates a JSON array result as a Clob, including a null value. This is similar to JSONARRAY but aggregates its contents into single object. You can also wrap the array: 2.4.16. Conversion to JSON A straightforward specification compliant conversion is used for converting values into their appropriate JSON document form. null values are included as the null literal. values parsed as JSON or returned from a JSON construction function (JSONPARSE, JSONARRAY, JSONARRAY_AGG) will be directly appended into a JSON result. boolean values are included as true/false literals numeric values are included as their default string conversion - in some circumstances if not a number or +-infinity results are allowed, invalid JSON may be obtained. string values are included in their escaped/quoted form. binary values are not implicitly convertible to JSON values and require a specific prior to inclusion in JSON. all other values will be included as their string conversion in the appropriate escaped/quoted form. 2.4.17. Spatial Functions Spatial functions provide functionality for working with geospatial data. Red Hat JBoss Data Virtualization relies on the JTS Topology Suite to provide partial support for the OpenGIS Simple Features Specification For SQL Revision 1.1. Most Geometry support is limited to two dimensions due to the WKB and WKT formats. Important Geometry support is still evolving. There may be minor differences between Data Virtualization and pushdown results that will need to be further refined. Conversion Functions ST_GeomFromText Returns a geometry from a Clob in WKT format. text is a clob, srid is an optional integer. Return value is a geometry. ST_GeomFromWKB/ST_GeomFromBinary Returns a geometry from a blob in WKB format. bin is a blob, srid is an optional integer. Return value is a geometry. ST_GeomFromGeoJSON Returns a geometry from a Clob in GeoJSON format. text is a clob, srid is an optional integer. Return value is a geometry. ST_GeomFromGML Returns a geometry from a Clob in GML2 format. text is a clob, srid is an optional integer. Return value is a geometry. ST_AsText The geom is a geometry. Return value is clob in WKT format. ST_AsBinary The geom is a geometry. Return value is a blob in WKB format. ST_GeomFromEWKB Returns a geometry from a blob in EWKB format. The bin is a blob. Return value is a geometry. Only two dimensions are supported. ST_AsGeoJSON The geom is a geometry. Return value is a clob with the GeoJSON value. ST_AsGML The geom is a geometry. Return value is a clob with the GML2 value. ST_AsEWKT The geom is a geometry. Return value is a clob with the EWKT value. The EWKT value is the WKT value with the SRID prefix. ST_AsKML The geom is a geometry. Return value is a clob with the KML value. The KML value is effectively a simplified GML value and projected into SRID 4326. Operators && Returns true if the bounding boxes of geom1 and geom2 intersect. geom1 and geom2 are geometries. The returned value is a boolean. Relationship Functions ST_CONTAINS Returns true if geom1 contains geom2 contains another. geom1, geom2 are geometries. Return value is a boolean. ST_CROSSES Returns true if the geometries cross. The geom1 and geom2 are geometries. Return value is a boolean. ST_DISJOINT Returns true if the geometries are disjoint. The geom1 and geom2 are geometries. Return value is a boolean. ST_DISTANCE Returns the distance between two geometries. The geom1 and geom2 are geometries. Return value is a double. ST_EQUALS Returns true if the two geometries are spatially equal - the points and order may differ, but neither geometry lies outside of the other. The geom1 and geom2 are geometries. Return value is a boolean. ST_INTERSECTS Returns true if the geometries intersect. The geom1 and geom2 are geometries. Return value is a boolean. ST_OVERLAPS Returns true if the geometries overlap. The geom1 and geom2 are geometries. Return value is a boolean. ST_TOUCHES Returns true if the geometries touch. The geom1 and geom2 are geometries. Return value is a boolean. ST_DWithin Returns true if the geometries are within a given distance of one another. geom1 and geom2 are geometries. dist is a double. The returned value is a boolean. ST_OrderingEquals Returns true if geom1 and geom2 have the same structure and the same ordering of points. geom1 and geom2 are geometries. The returned value is a boolean. ST_Relate Test or return the intersection of geom1 and geom2. The geom1 and geom2 are geometries. Pattern is a nine character DE-9IM pattern string. The returned value is a boolean. ST_Within Returns true if geom1 is completely inside geom2. The geom1 and geom2 are geometries. The returned value is a boolean. Attributes and Tests ST_Area Returns the area of geom. The geom is a geometry. Return value is a double. ST_CoordDim Returns the coordinate dimensions of geom. The geom is a geometry. The returned value is an integer between 0 and 3. ST_Dimension This returns the dimension of geom. The geom is a geometry. The returned value is an integer between 0 and 3. ST_EndPoint This returns the endpoint of the LineString geom. Returns null if geom is not a LineString. The geom is a geometry. The returned value is a geometry. ST_ExteriorRing Returns the exterior ring or shell LineString of the Polygon geom. Returns null if geom is not a Polygon. The geom is a geometry. The returned value is a geometry. ST_GeometryN Returns the nth geometry at the given 1-based index in geom. Returns null if a geometry at the given index does not exist. Non collection types return themselves at the first index. The geom is a geometry. The index is an integer. The returned value is a geometry. ST_GeometryType Returns the type name of geom as ST_name, where the name will be LineString, Polygon, Point and so forth. The geom is a geometry. The returned value is a string. ST_HasArc Tests if the geometry has a circular string. Will currently only report false as curved geometry types are not supported. The geom is a geometry. The returned value is a geometry. ST_InteriorRingN Returns the nth interior ring LinearString geometry at the given 1-based index in geom. Returns null if a geometry at the given index does not exist or if geom is not a Polygon. The geom is a geometry. The index is an integer. The returned value is a geometry. ST_IsClosed Returns true if LineString geom is closed. Returns false if geom is not a LineString The geom is a geometry. The index is an integer. The returned value is a boolean. ST_IsEmpty Returns true if the set of points is empty. The geom is a geometry. The returned value is a boolean. ST_IsRing Returns true if the LineString geom is a ring. Returns false if geom is not a LineString. The geom is a geometry. The returned value is a boolean. ST_IsSimple Returns true if the geom is simple. The geom is a geometry. The returned value is a boolean. ST_IsValid Returns true if the geom is valid. The geom is a geometry. The returned value is a boolean. ST_Length Returns the length of a (Multi)LineString otherwise 0. The geom is a geometry. The returned value is a double. ST_NumGeometries Returns the number of geometries in the geom. Will return 1 if it is not a geometry collection. The geom is a geometry. The returned value is an integer. ST_NumInteriorRings Returns the number of interior rings in the Polygon geom. Returns null if geom is not a Polygon. The geom is a geometry. The returned value is an integer. ST_NunPoints Returns the number of points in a geom. The geom is a geometry. The returned value is an integer. ST_PointOnSurface Returns a point that is guaranteed to be on the surface of the geom. The geom is a geometry. The returned value is a point geometry. ST_Perimeter Returns the perimeter of the (Multi)Polygon geom. It ill return 0 if the geom is not a (multi)polygon. The geom is a geometry. The returned value is a double. ST_PointN Returns the nth Point at the given 1-based index in geom. Returns null if a point at the given index does not exist or if the geom is not a LineString. The geom is a geometry. The index is an integer. The returned value is a geometry. ST_SRID Returns the SRID for the geometry. The geom is a geometry. Return value is an integer. A 0 value rather than null will be returned for an unknown SRID on a non-null geometry. ST_SetSRID Set the SRID for the given geometry. The geom is a geometry. The srid is an integer. The returned value is a geometry. Only the SRID metadata for the geometry is modified. ST_StartPoint Returns the start Point of the LineString geom. Returns null if geom is not a LineString. The geom is a geometry. The returned value is a geometry. ST_X Returns the X ordinate value, or null if the point is empty. It throws an exception if the geometry is not a point. The geom is a geometry. The returned value is a double. ST_Y Returns the Y ordinate value, or null if the point is empty. It throws an exception if the geometry is not a point. The geom is a geometry. The returned value is a double. ST_Z Returns the Z ordinate value, or null if the point is empty. It throws an exception if the geometry is not a point. It will typically return null as three dimensions are not fully supported. The geom is a geometry. The returned value is a double. Miscellaneous Functions ST_Boundary Computes the boundary of the given geometry. The geom is a geometry. The returned value is a geometry. ST_Buffer Computes the geometry that has points within the given distance of a geom. The geom is a geometry. The distance is a double. The returned value is a geometry. ST_Centroid Computes the geometric center point of a geom. The geom is a geometry. The returned value is a geometry. ST_ConvexHull Return the smallest convex polygon that contains all of the points in a geom. The geom is a geometry. The returned value is a geometry. ST_Difference Computes the closure of the set of the points contained in geom1 that are not in geom2. The geom1 and geom2 are the geometry. The returned value is a geometry. ST_Envelope Computes the 2D bounding box of the given geometry. The geom is a geometry. The returned value is a geometry. ST_Force_2D Removes the z coordinate value if it is present. The geom is a geometry. The returned value is a geometry. ST_Intersection Computes the point set intersection of the points contained in geom1 and geom2. The geom1 and geom2 are the geometry. The returned value is a geometry. ST_Simplify Simplifies a geometry using the Douglas-Peucker algorithm, but may oversimplify to an invalid or empty geometry. The geom is a geometry. distanceTolerance is a double. The returned value is a geometry. ST_SimplifyPreserveTopology Simplifies a Geometry using the Douglas-Peucker algorithm. This always returns a valid geometry. The geom is a geometry. distanceTolerance is a double. The returned value is a geometry. ST_SnapToGrid Snaps all of the points in the geometry to a grid of a given size. The geom is a geometry. Size is a double. The returned value is a geometry. ST_SymDifference Return the part of geom1 that does not intersect with geom2 and vice versa. The geom1 and geom2 are the geometry. The returned value is a geometry. ST_Transform Transforms the geometry value from one coordinate system to another. The geom is a geometry. srid is an integer. Return value is a geometry. The srid value and the srid of the geometry value must exist in the SPATIAL_REF_SYS view. ST_Union Returns a geometry that represents the point set containing all of geom1 and geom2. The geom1 and geom2 are the geometry. The returned value is a geometry. Aggregate Functions ST_Extent Computes the 2D bounding box around all of the geometric values. All values should have the same srid. The geom is a geometry. The returned value is a geometry. Construction Functions ST_Point Returns the point for the given coordinates. The x and y are doubles. The returned value is a point geometry. ST_Polygon Returns the polygon for the given shell and srid. The geom is a linear ring geometry and the srid is an integer. The returned value is a polygon geometry. 2.4.18. Security Functions Security functions provide the ability to interact with the security system. HASROLE Whether the current caller has the JBoss Data Virtualization data role roleName . roleName must be a string, the return type is boolean. The two argument form is provided for backwards compatibility. roleType is a string and must be 'data'. Role names are case-sensitive and only match JBoss Data Virtualization data roles (see Section 7.1, "Data Roles" ). JAAS roles/groups names are not valid for this function, unless there is corresponding data role with the same name. 2.4.19. Miscellaneous Functions array_get Returns the object value at a given array index. array is the object type, index must be an integer, and the return type is object. One-based indexing is used. The actual array value must be a java.sql.Array or Java array type. An exception will be thrown if the array value is the wrong type of the index is out of bounds. array_length Returns the length for a given array. array is the object type, and the return type is integer. The actual array value must be a java.sql.Array or Java array type. An exception will be thrown if the array value is the wrong type. uuid Returns a universally unique identifier. The return type is string. Generates a type 4 (pseudo randomly generated) UUID using a cryptographically strong random number generator. The format is XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX where each X is a hex digit. 2.4.20. Nondeterministic Function Handling JBoss Data Virtualization categorizes functions by varying degrees of determinism. When a function is evaluated and to what extent the result can be cached are based upon its determinism level. Deterministic - the function will always return the same result for the given inputs. Deterministic functions are evaluated by the engine as soon as all input values are known, which may occur as soon as the rewrite phase. Some functions, such as the lookup function, are not truly deterministic, but is treated as such for performance. All functions not categorized below are considered deterministic. User Deterministic - the function will return the same result for the given inputs for the same user. This includes the hasRole and user functions. User deterministic functions are evaluated by the engine as soon as all input values are known, which may occur as soon as the rewrite phase. If a user deterministic function is evaluated during the creation of a prepared processing plan, then the resulting plan will be cached only for the user. Session Deterministic - the function will return the same result for the given inputs under the same user session. This category includes the env function. Session deterministic functions are evaluated by the engine as soon as all input values are known, which may occur as soon as the rewrite phase. If a session deterministic function is evaluated during the creation of a prepared processing plan, then the resulting plan will be cached only for the user's session. Command Deterministic - the result of function evaluation is only deterministic within the scope of the user command. This category include the curdate , curtime , now , and commandpayload functions. Command deterministic functions are delayed in evaluation until processing to ensure that even prepared plans utilizing these functions will be executed with relevant values. Command deterministic function evaluation will occur prior to pushdown; however, multiple occurrences of the same command deterministic time function are not guaranteed to evaluate to the same value. Nondeterministic - the result of function evaluation is fully nondeterministic. This category includes the rand function and UDFs marked as nondeterministic. Nondeterministic functions are delayed in evaluation until processing with a preference for pushdown. If the function is not pushed down, then it may be evaluated for every row in its execution context (for example, if the function is used in the select clause).
REPLACE(x, y, z) REGEXP_REPLACE(str, pattern, sub [, flags]) regexp_replace('Goodbye World', '[g-o].', 'x', 'gi') SELECT DECODEINTEGER(IS_IN_STOCK, 'false, 0, true, 1') FROM PartsSupplier.PARTS; SELECT DECODESTRING(IS_IN_STOCK, 'false, no, true, yes, null') FROM PartsSupplier.PARTS; SELECT DECODESTRING(IS_IN_STOCK, 'false:no:true:yes:null',':') FROM PartsSupplier.PARTS; SELECT DECODESTRING( IS_IN_STOCK, 'null,no,"null",no,nil,no,false,no,true,yes' ) FROM PartsSupplier.PARTS; LOOKUP(codeTable, returnColumn, keyColumn, keyValue) lookup('ISOCountryCodes', 'CountryCode', 'CountryName', 'UnitedStates') TABLE Customer ( CustomerId integer PRIMARY KEY, CustomerName varchar(25), ContactName varchar(25) Address varchar(50), City varchar(25), PostalCode varchar(25), Country varchar(25), ); XMLCAST(expression AS type) XMLCOMMENT(comment) XMLCONCAT(content [, content]*) SELECT XMLCONCAT( XMLELEMENT("name", CustomerName), XMLPARSE(CONTENT ' <a> b </a>' WELLFORMED) ) FROM Customer c WHERE c.CustomerID = 87; ========================================================== <name> Wartian Herkku </name> <a> b </a> XMLELEMENT([NAME] name [, <NSP>] [, <ATTR>][, content]*) ATTR:=XMLATTRIBUTES(exp [AS name] [, exp [AS name]]*) NSP:=XMLNAMESPACES((uri AS prefix | DEFAULT uri | NO DEFAULT))+ XMLELEMENT(NAME "elem", 1, '<2/>', xml_value) <elem>1&lt;2/&gt;<doc/><elem/> SELECT XMLELEMENT("name", CustomerName) FROM Customer c WHERE c.CustomerID = 87; ========================================================== <name>Wartian Herkku</name> "Multiple Columns" SELECT XMLELEMENT("customer", XMLELEMENT("name", c.CustomerName), XMLELEMENT("contact", c.ContactName)) FROM Customer c WHERE c.CustomerID = 87; ========================================================== <customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer> "Columns as Attributes" SELECT XMLELEMENT("customer", XMLELEMENT("name", c.CustomerName, XMLATTRIBUTES( "contact" as c.ContactName, "id" as c.CustomerID ) ) ) FROM Customer c WHERE c.CustomerID = 87; ========================================================== <customer> <name contact="Pirkko Koskitalo" id="87">Wartian Herkku</name> </customer> XMLFOREST(content [AS name] [, <NSP>] [, content [AS name]]*) SELECT XMLELEMENT("customer", XMLFOREST( c.CustomerName AS "name", c.ContactName AS "contact" )) FROM Customer c WHERE c.CustomerID = 87; ========================================================== <customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer> XMLAGG XMLAGG is an aggregate function, that takes a collection of XML elements and returns an aggregated XML document. XMLAGG(xml) From above example in XMLElement, each row in the Customer table table will generate row of XML if there are multiple rows matching the criteria. That will generate a valid XML, but it will not be well formed, because it lacks the root element. XMLAGG can used to correct that "Example" SELECT XMLELEMENT("customers", XMLAGG( XMLELEMENT("customer", XMLFOREST( c.CustomerName AS "name", c.ContactName AS "contact" ))) FROM Customer c ========================================================== <customers> <customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer> <customer><name>Wellington Importadora</name><contact>Paula Parente</contact></customer> <customer><name>White Clover Markets</name><contact>Karl Jablonski</contact></customer> </customers> XMLAGG(xml) SELECT XMLELEMENT("customers", XMLAGG( XMLELEMENT("customer", XMLFOREST( c.CustomerName AS "name", c.ContactName AS "contact" ))) FROM Customer c ========================================================== <customers> <customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer> <customer><name>Wellington Importadora</name><contact>Paula Parente</contact></customer> <customer><name>White Clover Markets</name><contact>Karl Jablonski</contact></customer> </customers> XMLPARSE((DOCUMENT|CONTENT) expr [WELLFORMED]) SELECT XMLPARSE(CONTENT '<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>' WELLFORMED); <customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer> XMLPI([NAME] name [, content]) XMLQUERY([<NSP>] xquery [<PASSING>] [(NULL|EMPTY) ON EMPTY]] PASSING:=PASSING exp [AS name] [, exp [AS name]]* XMLEXISTS([<NSP>] xquery [<PASSING>]] PASSING:=PASSING exp [AS name] [, exp [AS name]]* XMLSERIALIZE([(DOCUMENT|CONTENT)] xml [AS datatype] [ENCODING enc] [VERSION ver] [(INCLUDING|EXCLUDING) XMLDECLARATION]) XMLSERIALIZE(DOCUMENT value AS BLOB ENCODING "UTF-16" INCLUDING XMLDECLARATION) XMLTEXT(text) XSLTRANSFORM(doc, xsl) XPATHVALUE(doc, xpath) <?xml version="1.0" ?> <ns1:return xmlns:ns1="http://com.test.ws/exampleWebService">Hello<x> World</x></return> xpathValue(value, '/*[local-name()="return"]) JSONTOXML(rootElementName, json) { "firstName" : "John" , "children" : [ "Randy", "Judy" ] } <?xml version="1.0" ?><person><firstName>John</firstName><children>Randy</children><children>Judy</children></person> [{ "firstName" : "George" }, { "firstName" : "Jerry" }] <?xml version="1.0" ?><person><person><firstName>George</firstName></person><person><firstName>Jerry</firstName></person></person> {"/invalid" : "abc" } <?xml version="1.0" ?> <root> <_u002F_invalid>abc</_u002F_invalid> </root> JSONARRAY(value...) jsonArray('a"b', 1, null, false, {d'2010-11-21'}) ["a\"b",1,null,false,"2010-11-21"] JSONARRAY(value [as name] ...) jsonObject('a"b' as val, 1, null as "null") {"val":"a\"b","expr2":1,"null":null} JSONPARSE(value, wellformed) jsonParse('"a"') SELECT JSONARRAY_AGG(JSONOBJECT(CustomerId, CustomerName)) FROM Customer c WHERE c.CustomerID >= 88; ========================================================== [{"CustomerId":88, "CustomerName":"Wellington Importadora"}, {"CustomerId":89, "CustomerName":"White Clover Markets"}] SELECT JSONOBJECT(JSONARRAY_AGG(JSONOBJECT(CustomerId as id, CustomerName as name)) as Customer) FROM Customer c WHERE c.CustomerID >= 88; ========================================================== {"Customer":[{"id":89,"name":"Wellington Importadora"},{"id":100,"name":"White Clover Markets"}]} ST_GeomFromText(text [, srid]) ST_GeomFromWKB(bin [, srid]) ST_GeomFromGeoJson(text [, srid]) ST_GeomFromGML(text [, srid]) ST_GeomAsText(geom) ST_GeomAsBinary(geom) ST_GeomFromEWKB(bin) ST_GeomAsGeoJSON(geom) ST_GeomAsGML(geom) ST_AsEWKT(geom) ST_AsKML(geom) geom1 && geom2 ST_CONTAINS(geom1, geom2) ST_CROSSES(geom1, geom2) ST_DISJOINT(geom1, geom2) ST_DISTANCE(geom1, geom2) ST_EQUALS(geom1, geom2) ST_INTERSECT(geom1, geom2) ST_OVERLAPS(geom1, geom2) ST_TOUCHES(geom1, geom2) ST_DWithin(geom1, geom2, dist) ST_OrderingEquals(geom1, geom2) ST_Relate(geom1, geom2, pattern) ST_Within(geom1, geom2) ST_Area(geom) ST_CoordDim(geom) ST_Dimension(geom) ST_EndPoint(geom) ST_ExteriorRing(geom) ST_GeometryN(geom, index) ST_GeometryType(geom) ST_HasArc(geom) ST_InteriorRingN(geom, index) ST_IsClosed(geom) ST_IsEmpty(geom) ST_IsRing(geom) ST_IsSimple(geom) ST_IsValid(geom) ST_Length(geom) ST_NumGeometries(geom) ST_NumInteriorRings(geom) ST_NunPoints(geom) ST_PointOnSurface(geom) ST_Perimeter(geom) ST_PointN(geom, index) ST_SRID(geom) ST_SetSRID(geom, srid) ST_StartPoint(geom) ST_X(geom) ST_Y(geom) ST_Z(geom) ST_Boundary(geom) ST_Buffer(geom, distance) ST_Centroid(geom) ST_ConvexHull(geom) ST_Difference(geom1, geom2) ST_Envelope(geom) ST_Force_2D(geom) ST_Intersection(geom1, geom2) ST_Simplify(geom, distanceTolerance) ST_SimplifyPreserveTopology(geom, distanceTolerance) ST_SnapToGrid(geom, size) ST_SymDifference(geom1, geom2) ST_Tranform(geom, srid) ST_Union(geom1, geom2) ST_Extent(geom) ST_Point(x, y) ST_Polygon(geom, srid) hasRole([roleType,] roleName) array_get(array, index) array_length(array) uuid()