You can use the following functions when working with XQuery in Process Designer or in any field that uses a formula to set a value.
For information about XQuery functions that are available in the Expression Editor but are not described below, see http://www.xqueryfunctions.com/xq/alpha.html. Note that Application Integration doesn't support all functions documented in the URL.
Note: Application Integration does not support inline functions in the Expression Editor. The Expression Editor comes with powerful functions that can invoke operating system features. You must review the contents passed into the functions before using them.
Atomic Values of All Types
You can use the following functions in processes and service connectors:
•boolean
•empty
•exists
•false
•nilled
•not
•number
•string
•true
Attachments for Processes
The functions described in the following table are available for handling attachments in processes.
In case of a process, the $cid value in the attachment functions refers to the process field (Input, Output or Temp). For example, if the input parameter is called customerPhoto (of type attachment), to get the attachment size, use:
sff:GetAttachmentSize($input.customerPhoto)
Note: Be sure that your field names do not have spaces so they can be easily referenced in XQuery.
For more examples of using the attachment functions, see Attachments.
Function
Syntax
Description
createAttachmentFromBase64
sff:createAttachmentFromBase64($contentName as xs:string, $encodedContent as xs:string, $mimeType as xs:string)
Creates an attachment from the base64-encoded content.
getAttachmentContentType
sff:getAttachmentContentType($cid as xs:string?)
Returns the content-type.
getAttachmentCount
sff:getAttachmentCount($cid as xs:long)
Returns the number of attachments.
getAttachmentName
sff:getAttachmentName($cid as xs:string?)
Returns the attachment (file) name if available.
getAttachmentProperty
sff:getAttachmentProperty($cid as xs:string?, $attribute as xs:string)
Returns the attachment attribute, given the mime header name such as 'content-type'.
getAttachmentSize
sff:getAttachmentSize($cid as xs:long)
Returns the attachment size in bytes.
getBase64FromAttachment
sff:getBase64FromAttachment($cid as xs:string)
Returns base64-encoded attachment content from the variable, which has the attachment type.
hasAttachment
sff:hasAttachment($cid as xs:boolean)
Checks if an attachment exists.
setAttachmentContentType
sff:setAttachmentContentType($cid as xs:string, $val as xs:string)
Sets the attachment content-type.
setAttachmentName
sff:setAttachmentName($cid as xs:string, $val as xs:string)
Sets the attachment name.
setAttachmentProperty
sff:setAttachmentProperty($cid as xs:string, $attribute as xs:string, $val as xs:string)
Adds a specified amount to one part of a datetime value, and returns a date in the same format as the date you pass to the function. For more information, see addToDate.
adjust-date-to-timezone
fn:adjust-date-to-timezone(arg, timezone)
Adjusts a date value to a specific time zone, or removes the time zone component from the date value. For more information, see adjust-date-to-timezone.
adjust-dateTime-to-timezone
fn:adjust-dateTime-to-timezone(arg, timezone)
Adjusts a dateTime value to a specific time zone, or removes the time zone component from the dateTime value. For more information, see adjust-dateTime-to-timezone.
adjust-time-to-timezone
fn:adjust-time-to-timezone(arg, timezone)
Adjusts a time value to a specific time zone, or removes the time zone component from the date value. For more information, see adjust-time-to-timezone.
current-date
fn:current-date()
Returns the current date. For more information, see current-date.
current-dateTime
fn:current-dateTime()
Returns the current dateTime with time zone. For more information, see current-dateTime.
current-time
fn:current-time()
Returns the current time. For more information, see current-time.
dateDiff
date:dateDiff(date, date, format)
Returns the length of time between two dates in the format specified. For more information, see dateDiff.
dateTime
fn:dateTime(arg1, arg2)
Constructs a dateTime from a date and time. For more information, see dateTime.
dateToMillis
date:dateToMillis($date)
Converts the time to milliseconds.
day-from-date
fn:day-from-date(arg)
Returns the day portion of a date. For more information, see day-from-date.
day-from-dateTime
fn:day-from-dateTime(arg)
Returns the day portion of a date/time. For more information, see day-from-dateTime.
days-from-duration
fn:days-from-duration(arg)
Returns the number of days in a duration. For more information, see days-from-duration.
getDatePart
date:getDatePart(date, format)
Returns the specified part of a date as an integer value. For more information, see getDatePart.
getLocale
date:getLocale()
Returns a string representing the current locale the process is running in. For more information, see getLocale.
getTimeZone
date:getTimeZone()
Returns a string that specifies the timezone ID where the process is running or where the user is running it. For more information, see getTimeZone.
hours-from-dateTime
fn:hours-from-dateTime(arg)
Returns the hour portion of a date/time. For more information, see hours-from-dateTime.
hours-from-duration
fn:hours-from-duration(arg)
Returns the hours in a duration. For more information, see hours-from-duration.
hours-from-time
fn:hours-from-time(arg)
Returns the hour portion of a time. For more information, see hours-from-time.
implicit-timezone
fn:implicit-timezone()
Returns the value of the implicit timezone property. For more information, see implicit-timezone.
lastDay
date:lastDay(date)
Passes the date for which you want to return the last day of the month. You can enter any valid expression that evaluates to a date. For more information, see lastDay.
toDate
date:toDate(date, format)
Converts a character string to a Date data type. You use the toDate format strings to specify the format of the source strings. For more information, see toDate.
millisToDate
date:millisToDate($millis)
Converts the current time from milliseconds. For more information, see millisToDate.
minutes-from-dateTime
fn:minutes-from-dateTime(arg)
Returns an xs:integer value between 0 and 59, both inclusive, representing the minute component in the localized value of $arg. For more information, see minutes-from-dateTime.
minutes-from-duration
fn:minutes-from-duration(arg)
Returns an xs:integer representing the minutes component in the value of $arg. For more information, see minutes-from-duration.
minutes-from-time
fn:minutes-from-time(arg)
Returns an xs:integer value between 0 and 59, both inclusive, representing the minute component in the localized value of $arg. For more information, see minutes-from-time.
month-from-date
fn:month-from-date(arg)
Returns an xs:integer between 1 and 12, both inclusive, representing the month component in the localized value of $arg. For more information, see month-from-date.
month-from-dateTime
fn:month-from-dateTime(arg)
Returns an xs:integer between 1 and 12, both inclusive, representing the month component in the localized value of $arg. For more information, see month-from-dateTime.
months-from-duration
fn:months-from-duration(arg)
Returns an xs:integer representing the minutes component in the value of $arg. The result is obtained by casting $arg to an xs:dayTimeDuration. For more information, see months-from-duration.
now
date:now()
Returns the current time in milliseconds.
seconds-from-duration
fn:seconds-from-duration(arg)
Returns an xs:decimal representing the seconds component in the value of $arg. The result is obtained by casting $arg to an xs:dayTimeDuration. For more information, see seconds-from-duration.
seconds-from-dateTime
fn:seconds-from-dateTime(arg)
Returns an xs:decimal value greater than or equal to 0 and less than 60, representing the seconds and fractional seconds in the localized value of $arg. For more information, see seconds-from-dateTime.
seconds-from-time
fn:seconds-from-time(arg)
Returns an xs:decimal value greater than or equal to 0 and less than 60, representing the seconds and fractional seconds in the localized value of $arg. For more information, see seconds-from-time.
timezone-from-date
fn:timezone-from-date(arg)
Returns the timezone component of $arg, if any. For more information, see timezone-from-date .
timezone-from-dateTime
fn:timezone-from-dateTime(arg)
Returns the timezone component of an xs:dateTime. For more information, see timezone-from-dateTime.
timezone-from-time
fn:timezone-from-time(arg)
Returns the timezone component of an xs:time. For more information, see timezone-from-time.
toChar
date:toChar(date, format)
Converts the date passed to a string based on the specified format string. For more information, see toChar.
trunc
date:trunc(xs:dateTime('date'), 'format')
Truncates dates to a specific year, month, day, hour, minute, second, or millisecond. For more information, see trunc (Dates).
year-from-date
fn:year-from-date(arg)
Returns an xs:integer representing the year in the localized value of $arg. The value might be negative. For more information, see year-from-date.
year-from-dateTime
fn:year-from-dateTime(arg)
Returns an xs:integer representing the year component in the localized value of $arg. For more information see year-from-dateTime.
years-from-duration
fn:years-from-duration(arg)
Returns an xs:integer representing the years component in the value of $arg. The result is obtained by casting $arg to an xs:yearMonthDuration. For more information, see years-from-duration.
Digital Signatures
The functions described in the following sections are available for use in digital signatures.
HMAC Functions
The following functions enable you to generate a digital signature by calculating a keyed-hash message authentication code (HMAC):
Function
Syntax
Description
hmacSignature
dsig:hmacSignature($data as xs:string, $key as xs:string, $algorithm as xs:string, $encoding as xs:string?) as xs:string
Generates an HMAC signature using the specified algorithm such as HMACSHA256 or HMACSHA512 and the optional encoding where $encoding is one of the following values:
- Base64 (default)
- Base64Url
- Hex
- Hex64
hmacSHA1signature
dsig:hmacSHA1signature($data as xs:string, $key as xs:string, $encoding as xs:string?) as xs:string
Calculates an HMAC using the SHA1 algorithm and the optional encoding where$encoding is one of the following values:
- Base64 (default)
- Base64Url
- Hex
- Hex64
$data consist of four parts, including a JSON string in base64 encoding.
To ensure that you get hex binary and not base64 when you use the hash:hash function on the payload, use the following expression:
let $md5hex := hash:hash($jsonPayload,"MD5") return xs:base64Binary(xs:hexBinary($md5hex))
hmacSHA256signature
dsig:hmacSHA256signature($data as xs:string, $key as xs:string, $encoding as xs:string?) as xs:string
Calculates an HMAC using the SHA256 algorithm and the optional encoding where $encoding is one of the following values:
- Base64 (default)
- Base64Url
- Hex
- Hex64
hmacSHA512signature
dsig:hmacSHA512signature($data as xs:string, $key as xs:string, $encoding as xs:string?) as xs:string
Generates an HMAC SHA512 signature using the optional encoding where $encoding is one of the following values:
- Base64 (default)
- Base64Url
- Hex
- Hex64
hmacSHA256signatureForList
dsig:hmacSHA256signatureForList($data as xs:string, @delimiter as xs:string, $key as xs:string, $encoding as xs:string?) as xs:string
Generates an HMAC SHA512 signature using the optional encoding where $encoding is one of the following values:
The following functions enable you to generate digital signatures based on private keys:
Function
Syntax
Description
signWithKeyFile
dsig:signWithKeyFile($messageToSign as xs:string, $pathToKey as xs:string, $encryptionAlgorithm as xs:string, $digestAlgorithm as xs:string, $encoding as xs:string) as xs:string
Generates a signature using an asymmetric algorithm and a private key, specified in a PKCS8 file.
Arguments:
- $digestAlgorithm: SHA1 or SHA256
- $encryptionAlgorithm: RSA (common) or DSA (if using SHA1 for $digestAlgorithm).
- $pathToKey: The PKCS8 certificate as a Base64-encoded string (-----BEGIN PRIVATE KEY----- ..... n-----END PRIVATE KEY-----\n) or a binary private key file.
- $encoding(optional), which may be:
- Base64 (default)
- Hex64
- Base64Url
signWithKeyString
dsig:signWithKeyString($messageToSign as xs:string, $key as xs:string, $encryptionAlgorithm as xs:string, $digestAlgorithm as xs:string, $encoding as xs:string) as xs:string
Generates a signature using an asymmetric algorithm and a private key, specified in a PKCS8 certificate encoded string.
Arguments:
- $digestAlgorithm: SHA1 or SHA256
- $encryptionAlgorithm: RSA (common) or DSA (if using SHA1 for $digestAlgorithm).
- $key: The PKCS8 certificate as a Base64-encoded string (-----BEGIN PRIVATE KEY----- ..... n-----END PRIVATE KEY-----\n) or a binary private key.
- $encoding(optional), which may be:
- Base64 (default)
- Hex64
- Base64Url
signWithCertificate
dsig:signWithCertificate($messageToSign as xs:string, $pathToCertificate as xs:string, $keyRecoveryPassword as xs:string, $encryptionAlgorithm as xs:string, $digestAlgorithm as xs:string, $encoding as xs:string $keyStorePassword as xs:string, $aliasName as xs:string, $keyStoreType as xs:string) as xs:string
Generates a signature using a PKCS12 certificate.
Arguments:
- $pathToCertificate: File location on the agent that contains either a PKCS12 or JKS (Java keystore) certificate.
- $keyRecoveryPassword: Password to access the key in the certificate.
- $keyStorePassword: Password to open the key store. If empty, assumes the keystore is not password-protected.
- $aliasName: Optional. Alias of entry in the keystore on the Secure Agent which contains the key. If the alias is not supplied (or empty), the first entry is used.
- $keyStoreType: Type of keystore, which may be:
- PKCS12 (default)
- JKS (Java keystore)
Hashing Functions
The following functions enable you to generate a message hash string:
Function
Syntax
Description
hash
hash:hash($string, $alg)
Generates a hash string for a message using the specified algorithm (optional), where $alg is one of the following:
- MD5 (default)
- SHA1
- SHA256
List Functions
The list functions support only the native Saxon functions. The following functions allow you to work with lists:
Function
Syntax
Description
append
list:append($objectlist, $newItem)
Appends a new item to a list.
count
list:count($objectlist)
Counts the items in a list.
head
list:head($objectlist)
Returns the first item in a list.
list
list:list($sequence)
Converts a sequence of IDs into a semicolon-separated list of IDs for an object list.
remove
list:remove($objectlist, $position)
Removes the item at the specified position from the list.
replace
list:replace($objectlist, $position, $newItem)
Replaces an existing item in a list with a new value.
sequence
list:sequence($objectlistFieldName)
Converts a semicolon-separated list to a sequence.
When an object list field is inserted into formula and content, the field is wrapped in the list:sequence(object_field_name) XQuery function. For object lists in hosted objects, this converts the semicolon-separated list of IDs in the object list into a sequence. For object lists for process objects, the value is already a sequence and the function returns the list unchanged.
tail
list:tail($objectlist)
Return all items from the list with the exception of the first item.
List Function Examples
Perhaps the most common example of using the list functions is to get an object from a list each time a step is invoked, possibly from within a repeated Process or Service step. For example:
let mylist.Current:=list:head(mylist.List) let mylist.List:=list:tail(mylist.List)
The following example converts an object list in a semicolon-separated list, then iterates over each item:
for $objectid in list:sequence($objectlist) ...
The following example converts a sequence of IDs into a semicolon-separated list of IDs for an object list for hosted objects. For an object list for process objects, it returns a sequence of values:
let $mergedObjectLists := ( as:sequence($objectlist1), as:sequence($objectlist2) ) return list:list($mergedObjectLists)
The following miscellaneous functions are available:
Function
Syntax
Description
aesEncryption
util:aesEncryption(key, dataToEncrypt)
Encrypts the specified data with the provided key using the Advanced Encryption Standard (AES) algorithm. For more information about the function, see aesEncryption.
aesDecryption
util:aesDecryption(key, dataToDecrypt)
Decrypts the specified data with the provided key using the Advanced Encryption Standard (AES) algorithm. You must use the same key that you had used for encrypting the data in the aesEncryption function. For more information about the function, see aesDecryption.
base64Decode
util:base64Decode(data, charSet)
Returns the base64-decoded version of the input string provided based on the character set specified in the charSet argument. This function is typically used for attachments.
Application Integration supports the character sets that Azul JDK supports for encoding. For example, US-ASCII, ISO-8859-1, UTF-8, UTF-16BE, UTF-16LE, and UTF-16.
Default for the charSet argument is UTF-8.
base64Encode
util:base64Encode(data, charSet)
Returns the base64-encoded version of the input string provided based on the character set specified in the charSet argument.
Application Integration supports the character sets that Azul JDK supports for encoding. For example, US-ASCII, ISO-8859-1, UTF-8, UTF-16BE, UTF-16LE, and UTF-16.
Default for the charSet argument is UTF-8.
base64EncodeURL
util:base64EncodeUrl(str, charSet)
Returns a base64-encoded version of the string provided, safe to use in a URL.
Any "+" and "/" characters are replaced with "-" and "_". Any "=" characters are removed.
Searches a field for the specified value. For more information, see decode.
error
fn:error(error, description, error-object)
Raises a custom error.
escapeJsonString
util:escapeJsonString(str)
Escapes special characters in the provided string to make it valid for usage in JSON. See JSON standard for details. For more information about the function, see escape-Json-String .
exactly-one
fn:exactly-one(arg)
Returns a sequence if it contains exactly one item, otherwise returns errors.
- timezoneId: Optional. The time zone ID. If you do not use a stored value (typically returned by a call to date:getTimeZone()), you can omit this argument as Process Designer will call date:getTimeZone().
- locale: Optional. The locale. If you do not use a stored value (typically returned by a call to date:getLocale()), you can omit this argument as Process Designer will call date:getLocale().
If you use a locale argument but do not use a timezoneId argument, you must add the comma that would follow timezoneId. For example:
util:format("789","##00.00","",date:getLocale())
If the pattern argument contains a date, use lowercase letters to denote the year format. For example, use yyyy. If you use uppercase letters, errors might occur.
generate-random-string
util:generate-random-string(length as xs:integer)
Generates a random string of the specified length.
generateUUID
util:generateUUID()
Generates a universally unique identifier.
getAssetLocation
util:getAssetLocation()
Returns the location where the process or the guide that uses the function is stored. For more information, see getAssetLocation.
getAssetName
util:getAssetName()
Returns the name of the process or the guide that uses the function. For more information, see getAssetName.
getCatalogResource
util:getCatalogResource()
Returns the resource based on a catalog location URL within the organization. Takes any catalog resource in the Informatica Cloud organization as its parameter and returns an element node.
To display the element data, add a wrapper function. For example:
Returns the start time of the running instance of the specified process or guide. For more information, see getInstanceStartTime.
getOrganizationId
util:getOrganizationId()
Returns a string that is the organization Id of the context of the currently executing process. For more information, see getOrganizationId.
getOrganizationName
util:getOrganizationName()
Returns the organization name in the context of the executing process, guide, service connector, or data access service connector. For more information, see getOrganizationName.
getProcessId
util:getProcessId()
Returns the process Id of the currently executing or completed process.
getUserName
util:getUserName()
Returns a string that is the login name or ID of the authenticated User running the process .
getUserSystem
util:getUserSystem()
Returns a string that is the name of the system that authenticated the user running the process.
iif
util:iif(condition, val1, val2)
Returns val1 if the condition is true. Else, returns val2. For more information, see iif.
in
util:in(valueToSearch, values, caseFlag)
Matches input data with a list of values. By default, the match is case sensitive. For more information, see in.
isNull
util:isNull(value)
Checks whether the input passed is empty. For more information, see isNull.
one-or-more
fn:one-or-more
Returns a sequence if it contains one or more items, otherwise returns errors.
parseJSON
util:parseJSON(jsonStr)
Parses the provided JSON string and converts it to XML elements.
parseXML
util:parseXML(xmlStr)
Parses the provided XML string and converts it to an XML element.
random
util:random()
Returns a random number from 0 to 1.
resolveURN
util:resolveURN()
Retrieves the URN mapping for an organization.
safeNumber
util:safeNumber()
Holds a number value safely so it cannot be changed.
toDecimal
util:toDecimal(value, scale)
Converts a string or numeric value to a decimal value. For more information, see toDecimal.
toInteger
util:toInteger(value, flag)
Converts a string or numeric value to a decimal value. For more information, see toInteger.
setProcessTitle
ipd:setProcessTitle()
Sets the title of the process.
Note: Use this function only in Informatica Process Designer.
simplifyXml
util:simplifyXml(undefined)
Utility function that is used to remove all namespaces and convert attributes into child elements. For more information, see simplifyXml.
toJSON
util:toJSON(elements)
Converts the provided list of XML elements to a JSON string.
toXML
util:toXML(element)
Converts the provided XML element to an XML string.
trace
util:trunc(arg, precision)
Provides an execution trace to be used in debugging queries. For more information, see trace.
trunc
util:trunc(arg, precision)
Truncates numbers to a specific digit based on the number of decimal places specified by the precision. For more information, see trunc (Numbers).
zero-or-one
fn:zero-or-one(arg)
Returns a sequence if it contains zero or one items, otherwise returns an error.
Numbers
The following number functions are available in the Expression Editor:
•abs
•avg
•ceiling
•floor
•max
•min
•round
•round-half-to-even
•sum
Sequences
The following sequences functions are available in the Expression Editor:
•count
•distinct-values
•index-of
•insert-before
•last
•position
•remove
•reverse
•subsequence
•unordered
String
The following string functions are available in the Expression Editor:
•codepoint-equal
•codepoints-to-string
•compare
•concat
•contains
•default-collation
•ends-with
•lang
•lower-case
•matches
•normalize-space
•normalize-unicode
•replace
•starts-with
•string-join
•string-to-codepoints
•substring
•substring-after
•substring-before
•tokenize
•translate
•upper-case
XML
You can add common XML functions as you build expressions. From the Expression Editor, a list of common XML functions displays in these categories:
•XML Documents, URIs, and IDs (processes only)
•XML Namespaces and Names
•XML Nodes
The following XML Nodes functions are available in the Expression Editor:
Function
Syntax
Description
data
fn:data(arg)
Returns the atomic value of a node.
The function takes a sequence of items and returns a sequence of atomic values. For more information, see data.
deep-equal
fn:deep-equal(parameter1, parameter2, collation)
Assesses whether two nodes have the same content and attributes. For more information, see deep-equal.
root
fn:root(arg)
Returns the root of the tree that contains the argument. For more information, see root.
Date and time functions
The following date functions are available in the Dates and Times section of the Expression Editor:
addToDate
Adds a specified amount to one part of a datetime value, and returns a date in the same format as the date you pass to the function. The addToDate function accepts positive and negative integer values. Use addToDate to change the following parts of a date:
•Year. Enter a positive or negative integer in the amount argument. Use any of the year format strings: Y, YY, YYY, or YYYY. The following expression adds 10 years to all dates in the SHIP_DATE column:
•Month. Enter a positive or negative integer in the amount argument. Use any of the month format strings: MM, MON, MONTH. The following expression subtracts 10 months from each date in the SHIP_DATE column:
•Day. Enter a positive or negative integer in the amount argument. Use any of the day format strings: D, DD, DDD, DY, and DAY. The following expression adds 10 days to each date in the SHIP_DATE column:
•Hour. Enter a positive or negative integer in the amount argument. Use any of the hour format strings: HH, HH12, HH24. The following expression adds 14 hours to each date in the SHIP_DATE column:
•Minute. Enter a positive or negative integer in the amount argument. Use the MI format string to set the minute. The following expression adds 25 minutes to each date in the SHIP_DATE column:
•Seconds. Enter a positive or negative integer in the amount argument. Use the SS format string to set the second. The following expression adds 59 seconds to each date in the SHIP_DATE column:
•Milliseconds. Enter a positive or negative integer in the amount argument. Use the MS format string to set the milliseconds. The following expression adds 125 milliseconds to each date in the SHIP_DATE column:
•Microseconds. Enter a positive or negative integer in the amount argument. Use the US format string to set the microseconds. The following expression adds 2,000 microseconds to each date in the SHIP_DATE column:
Note: You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/ Optional
Description
date
Required
Date/Time data type. Passes the values that you want to change.
You can enter any valid transformation expression.
format
Required
A format string that specifies the portion of the date value that you want to change. Enclose the format string within single quotation marks, for example, 'mm'. The format string is not case sensitive.
amount
Required
An integer value that specifies the amount of years, months, days, hours, and so on by which you want to change the date value. You can enter any valid transformation expression that evaluates to an integer.
Return Value
Date in the same format as the date you pass to this function.
NULL if a null value is passed as an argument to the function.
Examples
The following expressions all add one month to each date in the DATE_SHIPPED column. If you pass a value that creates a day that does not exist in a particular month, addToDate returns the last day of the month. For example, if you add one month to Jan 31 1998, addToDate returns Feb 28 1998.
Also, addToDate recognizes leap years and adds one month to Jan 29 2000:
The following table shows some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 1 1997 12:00:30AM
Dec 31 1996 9:00:30AM
Jan 31 1997 6:24:45PM
Jan 31 1997 3:24:45AM
Oct 9 1997 2:30:12PM
Oct 8 1997 11:30:12PM
Mar 3 1996 5:12:20AM
Mar 2 1996 2:12:20PM
Mar 1 1996 5:32:12AM
Feb 29 1996 2:32:12PM (Leap Year)
NULL
NULL
adjust-date-to-timezone
Adjusts a date value to a specific time zone, or removes the time zone component from the date value.
Syntax
fn:adjust-date-to-timezone(arg, timezone)
The following table describes the arguments:
Argument
Required/
Optional
Description
arg
Required
The date value that is to be adjusted.
The date-value is of type xs:date or is an empty sequence.
timezone
Optional
A duration that represents the time zone to which date-value is to be adjusted.
Return Value
The fn:adjust-date-to-timezone function behaves differently depending on whether the $arg date value already has a time zone, and on the value of the time zone provided as given below:
•The $timezone argument is expressed as an xs:dayTimeDuration, for example -PT5H for US Eastern Standard Time.
•If $timezone is an empty sequence, returns an xs:date without a time zone. Otherwise, returns an xs:date with a time zone.
•If $arg already has a time zone, its value is adjusted to that time zone.
•To adjust the time zone, an xs:date is treated as an xs:dateTime with time 00:00:00 .
•If $arg does not already have a time zone, its date part stays the same, and the time is associated with the specified time zone.
•If $timezone is omitted from the function call, it is assumed to be the implicit time zone.
Examples
The following table contains examples and return values for the adjust-date-to-timezone function:
Adjusts a dateTime value to a specific time zone, or removes the time zone component from the dateTime value.
Syntax
fn:adjust-dateTime-to-timezone(arg, timezone)
The following table describes the arguments:
Argument
Required/
Optional
Description
arg
Required
The dateTime value that is to be adjusted.
The dateTime-value is of type xs:dateTime or is an empty sequence.
timezone
Optional
A duration that represents the time zone to which dateTime-value is to be adjusted.
Return Value
The fn:adjust-dateTime-to-timezone function behaves differently depending on whether the $arg date value already has a time zone, and on the value of the time zone provided as given below:
•The $timezone argument is expressed as an xs:dayTimeDuration, for example -PT5H for US Eastern Standard Time.
•If $timezone is an empty sequence, returns an xs:dateTime without a time zone. Otherwise, returns an xs:dateTime with a time zone.
•If $arg already has a time zone, its value is adjusted to that time zone.
•If $arg does not already have a time zone, its date part stays the same, and the time is associated with the specified time zone.
•If $timezone is omitted from the function call, it is assumed to be the implicit time zone.
Examples
The following table contains examples and return values for the adjust-date-to-timezone function:
Adjusts a time value to a specific time zone, or removes the time zone component from the date value.
Syntax
fn:adjust-time-to-timezone(arg, timezone)
The following table describes the arguments:
Argument
Required/
Optional
Description
arg
Required
The time value that is to be adjusted.
The time-value is of type xs:time or is an empty sequence.
timezone
Optional
A duration that represents the time zone to which time-value is to be adjusted.
Return Value
The fn:adjust-time-to-timezone function behaves differently depending on whether the $arg date value already has a time zone, and on the value of the time zone provided as given below:
•The $timezone argument is expressed as an xs:dayTimeDuration, for example -PT5H for US Eastern Standard Time.
•If $timezone is an empty sequence, returns an xs:time without a time zone. Otherwise, returns an xs:time with a time zone.
•If $arg already has a time zone, its value is adjusted to that time zone.
•If $arg does not already have a time zone, its date part stays the same, and the time is associated with the specified time zone.
•If $timezone is omitted from the function call, it is assumed to be the implicit time zone.
Examples
The following table contains examples and return values for the adjust-date-to-timezone function:
The fn:current-date function takes no parameters, and returns the current date with the time zone. The time zone is implementation-dependent.
Example
If this function was invoked on January 12, 2023, the returned value would be 2023-01-12Z.
current-dateTime
Returns the current dateTime with time zone.
Syntax
fn:current-dateTime()
Return Value
The fn:current-dateTime function takes no parameters, and returns the current date and time with the time zone. If the function is called multiple times within the same query, it returns the same value every time. The time zone is implementation-dependent.
Example
If this function was invoked on January 12, 2023 in time zone Z, the returned value would be 2023-01-12T11:25:30.125Z.
current-time
Returns the current time.
Syntax
fn:current-time()
Return Value
The fn:current-time function takes no parameters, and returns the current time with the time zone. If the function is called multiple times within the same query, it returns the same value every time. The time zone is implementation-dependent.
Example
The following expression will return 23:17:00.000-05:00 as the result:
fn:current-time()
dateDiff
Returns the length of time between two dates. You can specify the format as years, months, days, hours, minutes, seconds, milliseconds, or microseconds. The dateDiff function subtracts the second date from the first date and returns the difference.
The dateDiff function calculates the value based on the number of months instead of the number of days. It calculates the date differences for partial months with the days selected in each month. To calculate the date difference for the partial month, dateDiff adds the days used within the month. It then divides the value with the total number of days in the selected month.
The dateDiff function gives a different value for the same period in the leap year period and a non-leap year period. The difference occurs when February is part of the dateDiff function. The dateDiff function divides the days with 29 for February for a leap year and 28 if it is not a leap year.
For example, you want to calculate the number of months from September 13 to February 19. In a leap year period, dateDiff calculates the month of February as 19/29 months or 0.655 months. In a non-leap year period, dateDiff calculates the month of February as 19/28 months or 0.678 months. The dateDiff function similarly calculates the difference in the dates for the remaining months and the dateDiff function returns the total value for the specified period.
Note: Some databases might use a different algorithm to calculate the difference in dates.
Note: You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/ Optional
Description
date
Required
Date/Time data type. Passes the values for the first date that you want to compare.
You can enter any valid transformation expression.
date
Required
Date/Time data type. Passes the values for the second date that you want to compare.
You can enter any valid transformation expression.
format
Required
Format string that specifies the date or time measurement. You can specify years, months, days, hours, minutes, seconds, milliseconds, or microseconds. You can specify only one part of the date, such as 'mm'. Enclose the format strings within single quotation marks. The format string is not case sensitive. For example, the format string 'mm' is the same as 'MM', 'Mm', or 'mM'.
Return Value
Double value. If the first date is later than the second date, the return value is a positive number. If the first date is earlier than the second date, the return value is a negative number.
0 if the dates are the same.
NULL if one (or both) of the date values is NULL.
Examples
The following expressions return the number of hours between the DATE_PROMISED and DATE_SHIPPED columns:
The following table lists some sample values and return values:
DATE_PROMISED
DATE_SHIPPED
LEAP YEAR VALUE (in Months)
NON-LEAP YEAR VALUE (in Months)
Sept 13
Feb 19
-5.237931034
-5.260714286
NULL
Feb 19
NULL
N/A
Sept 13
NULL
NULL
N/A
dateTime
Constructs a dateTime value from a date value and a time value.
Syntax
fn:dateTime(arg1, arg2)
The fn:dateTimeAdd (arg1 as xs:date?, arg2 as xs:time?) is different from the xs:dateTime constructor, which accepts a single argument that includes the date and time. Time zone is taken into account when constructing the date/time.
Return Value
•If only one of the arguments has a time zone, or if both arguments have the same time zone, the result has this time zone.
•If neither the date nor the date argument has a time zone, the result has no time zone.
•If the two arguments have different time zones, an error is returned.
•If the combination does not result in a valid time, for example, the day is 32 or the minute is 61, an error is returned.
Example
The following expression returns 2023-06-12T11:35:29 as the result:
The following table describes the argument for this command:
Argument
Required/
Optional
Description
arg
Required
The duration value from which the days component is to be extracted.
The duration-value is an empty sequence, or is a value that has one of the following types:
- xs:dayTimeDuration
- xs:duration
- xs:yearMonthDuration
Return Value
•If duration-value is of type xs:dayTimeDuration or is of type xs:duration, the returned value is of type xs:integer, and is the days component of duration-value cast as xs:dayTimeDuration.
•If duration-value is of type xs:yearMonthDuration, the returned value is 0.
•If duration-value is an empty sequence, the returned value is an empty sequence.
•If duration-value is negative, the returned value is negative.
Example
The following expression returns the days from the duration:
The following table lists some sample values and return values:
DURATION
RETURN VALUE
P5D
5
-PT24H
-1
PT23H
0
P1DT36H
2
PT1440M
1
getDatePart
Returns the specified part of a date as an integer value. Therefore, if you create an expression that returns the month portion of the date, and pass a date such as Apr 1 1997 00:00:00, getDatePart returns 4.
Syntax
date:getDatePart(xs:dateTime('date'), 'format')
Note: You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/ Optional
Description
date
Required
Date/Time data type.
You can enter any valid transformation expression.
format
Required
A format string that specifies the portion of the date value that you want to return. Enclose format strings within single quotation marks, for example, 'mm'. The format string is not case sensitive.
For example, if you pass the date Apr 1 1997 to getDatePart, the format strings 'Y', 'YY', 'YYY', or 'YYYY' all return 1997.
Return Value
Integer representing the specified part of the date.
NULL if a value passed to the function is NULL.
Examples
The following expressions return the hour for each date in the DATE_SHIPPED column. 12:00:00AM returns 0 because the default date format is based on the 24 hour interval:
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Mar 13 1997 12:00:00AM
1997
June 3 1997 11:30:44PM
1997
NULL
NULL
getLocale
Returns a string representing the current locale where the process is running.
Syntax
date:getLocale()
Use the getLocale function to retrieve the current locale setting active within the system. It identifies regional or language specific settings that are used to generate certain values such as language code and a country/region code.
Return Value
Returns the locale value as a text string.
Example
If the process is being executed in the IST time zone, the following expression returns the locale information:
date:getLocale()
The following table lists some sample properties and values:
PROPERTY
VALUE
Base locale ID
en_IN
Language
en
Country
IN
Display name
English (India)
getTimeZone
Returns a string that specifies the timezone ID where the process is running or where the user is executing it.
Syntax
date:getTimeZone()
Return Value
Returns the timezone ID as a string value.
Example
If the process is being executed in the IST time zone, the following expression returns 2023-01-01T12:00:00+00:00 as the result:
date:getTimeZone()
hours-from-dateTime
Returns the hour portion of a date/time.
Syntax
fn:hours-from-dateTime(arg)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
arg
Required
The dateTime value from which the hours component is to be extracted.
The dateTime-value is of type xs:dateTime or is an empty sequence.
Return Value
Returns the hour portion of an xs:dateTime value in its localized form, as an integer between 0 and 23 inclusive.
If dateTime-value is an empty sequence, the returned value is an empty sequence.
Example
The following expression returns the hour from the date/time:
fn:hours-from-dateTime(xs:dateTime('TIME'))
The following table lists some sample values and return values:
TIME
RETURN VALUE
2023-08-15T10:30:23
10
2023-08-15T10:30:23-05:00
10
hours-from-duration
Returns the hours in a duration.
Syntax
fn:hours-from-duration(arg)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
arg
Required
The duration value from which the hours component is to be extracted.
The duration-value is an empty sequence or is a value that has one of the following types:
- xs:dayTimeDuration
- xs:duration
- xs:yearMonthDuration
Return Value
•If duration-value is of type xs:dayTimeDuration or is of type xs:duration, the returned value is of type xs:integer, and is a value between -23 and 23, inclusive. The value is the hours component of duration-value cast as xs:dayTimeDuration.
•If duration-value is of type xs:yearMonthDuration, the returned value is of type xs:integer and is 0.
•If duration-value is an empty sequence, the returned value is an empty sequence.
•If duration-value is negative, the returned value is negative.
Example
The following expression returns the hours from the duration:
The following table lists some sample values and return values:
DURATION
RETURN VALUE
P1DT5H
5
-PT36H
-12
PT23H
23
PT1H90M
2
PT2H59M
2
PT3600S
1
hours-from-time
Returns the hour portion of a time.
Syntax
fn:hours-from-time(arg)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
arg
Required
The time value from which the hours component is to be extracted.
The time-value is of type xs:time or is an empty sequence.
Return Value
Returns the hour portion of an xs:time value in its localized form, as an integer between 0 and 23 inclusive.
If time-value is an empty sequence, the returned value is an empty sequence.
Example
The following expression returns the hour from the time:
fn:hours-from-time(xs:time('TIME'))
The following table lists some sample values and return values:
TIME
RETURN VALUE
10:30:23
10
10:30:23-05:00
10
09:30:00-08:00
9
implicit-timezone
Returns the value of the implicit timezone property.
Syntax
fn:implicit-timezone()
The implicit time zone is used in comparisons and calculations involving date and time values that do not have explicitly defined time zones. The implicit time zone is defined by the implementation.
Return Value
Returns the implicit time zone as an xs:dayTimeDuration value.
Example
If the implicit time zone is UTC minus 5 hours, the following expression returns -PT5H as the result:
fn:implicit-timezone()
lastDay
Returns the date of the last day of the month for each date in a column.
Syntax
date:lastDay(date)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
date
Required
Date/Time data type. Passes the date for which you want to return the last day of the month.
You can enter any valid transformation expression that evaluates to a date.
Return Value
Date. The last day of the month for the date value that you pass to this function.
NULL if a value in the selected column is NULL.
Example
The following expression returns the current date as the last day:
date:lastDay(fn:current-dateTime('DATE'))
The following table lists some sample values and return values:
DATE
RETURN VALUE
18-04-98 01:00
Apr 18 1998 01:00:00 AM
20-08-99 05:00
Aug 20 1999 05:00:00 AM
The following expression returns the last day of the previous month for each date in the DATE column:
The following table lists some sample values and return values:
DATE
RETURN VALUE
Apr 1 1998 12:00:00AM
Mar 31 1998 12:00:00AM
Jan 6 1998 12:00:00AM
Dec 31 1997 12:00:00AM
Feb 2 1996 12:00:00AM
Jan 31 1996 12:00:00AM
NULL
NULL
You can nest toDate to convert string values to a date. toDate function always includes time information. If you pass a string that does not have a time value, the date returned will include the time 00:00:00.
The following example returns the last day of the month for each date in the same format as the string:
date:lastDay(toDate('DATE', 'MON-DD-YYYY'))
The following table lists some sample values and return values:
DATE
RETURN VALUE
'18-NOV-98'
Nov-30-1998 00:00:00
'28-APR-98'
Apr-30-1998 00:00:00
NULL
NULL
'18-FEB-96'
Feb-29-1996 00:00:00(Leap year)
date:lastDay(date:toDate("DATE", "YYYY-MM-DD"))
The following table lists some sample values and return values:
DATE
RETURN VALUE
'18-NOV-98'
1998-Nov-30 00:00:00
'28-APR-98'
1998-Apr-30 00:00:00
NULL
NULL
'18-FEB-96'
1996-Feb-29 00:00:00(Leap year)
millisToDate
Converts the current time from milliseconds.
Syntax
date:millisToDate(millis)
Argument
Required/
Optional
Description
millis
Required
Numeric data type. Passes the values in milliseconds.
Return Value
The function returns a date that is the number of milliseconds passed since January 1, 1970, 00:00:00 UTC.
Examples
The function returns the corresponding date values for the value mentioned in the MILLISECONDS column:
MILLISECONDS
RETURN VALUE
1000
1970-01-01T00:00:01Z
1900800000
1970-01-23T00:00:00Z
minutes-from-dateTime
Returns an xs:integer value between 0 and 59, both inclusive, representing the minute component in the localized value of $arg.
Syntax
fn:minutes-from-dateTime(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as datetime that you want to convert to minutes.
Return Value
If $arg is an empty sequence, the function returns the empty sequence. Otherwise, the function returns an xs:integer value between 0 and 59, both inclusive, representing the minute component in the local value of $arg.
Examples
The function returns the corresponding date values for the value mentioned in the MILLISECONDS column:
MILLISECONDS
RETURN VALUE
1999-05-31T13:40:00-05:00
40
1999-05-31T13:30:00+05:30
30
minutes-from-duration
The function returns an xs:integer representing the minutes component in the value of $arg.
Syntax
fn:minutes-from-duration(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a duration that must be converted to minutes.
Return Value
The function returns an xs:integer representing the minutes component in the value of $arg. The result is obtained by casting $arg to an xs:dayTimeDuration.
Examples
The function returns the corresponding date values for the value mentioned in the DURATION column:
DURATION
RETURN VALUE
P3DT12H32M12S
32
-P5DT12H30M
-30
minutes-from-time
Returns an xs:integer value between 0 and 59, both inclusive, representing the minute component in the localized value of $arg.
Syntax
fn:minutes-from-time(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a time that must be converted to minutes.
Return Value
If $arg is an empty sequence, the function returns the empty sequence. Otherwise, the function returns an xs:integer value between 0 and 59, both inclusive, representing the minute component in the local value of $arg.
Examples
The function returns the corresponding date values for the value mentioned in the TIME column:
TIME
RETURN VALUE
11:23:00
23
13:00:00Z
0
month-from-date
Returns an xs:integer between 1 and 12, both inclusive, representing the month component in the localized value of $arg.
Syntax
fn:month-from-date(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a date that must be converted to months.
Return Value
If $arg is an empty sequence, the function returns the empty sequence. Otherwise, the function returns an xs:integer value between 1 and 12, both inclusive, representing the month component in the local value of $arg.
Examples
The function returns the corresponding date values for the value mentioned in the DATE column:
DATE
RETURN VALUE
1991-05-31-05:00
05
2000-01-01+05:00
01
month-from-dateTime
Returns an xs:integer between 1 and 12, both inclusive, representing the month component in the localized value of $arg.
Syntax
fn:month-from-dateTime(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a datetime that must be converted to months.
Return Value
If $arg is an empty sequence, the function returns the empty sequence. Otherwise, the function returns an xs:integer value between 1 and 12, both inclusive, representing the month component in the local value of $arg.
Examples
The function returns the corresponding date values for the value mentioned in the DATETIME column:
DATETIME
RETURN VALUE
1995-05-31T13:20:00-05:00
05
1996-12-31T19:20:00-05:00
12
months-from-duration
Returns an xs:integer representing the minutes component in the value of $arg . The result is obtained by casting $arg to an xs:dayTimeDuration.
Syntax
fn:months-from-duration(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a duration that must be converted to months.
Return Value
If $arg is an empty sequence, the function returns the empty sequence. Otherwise, the function returns an xs:integer value between 1 and 12, both inclusive, representing the month component in the local value of $arg.
Examples
The function returns the corresponding date values for the value mentioned in the DURATION column:
DURATION
RETURN VALUE
1991-05-31-05:00
05
2000-01-01+05:00
01
seconds-from-dateTime
Returns an xs:decimal value greater than or equal to 0 and less than 60, representing the seconds and fractional seconds in the localized value of $arg.
Syntax
fn:seconds-from-dateTime(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a datetime that must be converted to seconds.
Return Value
If $arg is an empty sequence, the function returns the empty sequence. Otherwise, the function returns an xs:decimal value greater than or equal to 0 and less than 60, representing the seconds and fractional seconds in the local value of $arg.
Examples
The function returns the corresponding date values for the value mentioned in the DATETIME column:
DATETIME
RETURN VALUE
2006-08-15T10:30:14.5
14.5
2001-05-31T13:20:40
40
seconds-from-duration
Returns an xs:decimal representing the seconds component in the value of $arg . The result is obtained by casting $arg to an xs:dayTimeDuration.
Syntax
fn:seconds-from-duration(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a duration that must be converted to seconds.
Return Value
If $arg is an empty sequence, the function returns the empty sequence. Otherwise, the function returns an xs:decimal value greater than or equal to 0 and less than 60, representing the seconds and fractional seconds in the local value of $arg.
Examples
The function returns the corresponding date values for the value mentioned in the DURATION column :
DURATION
RETURN VALUE
PT30.8S
30.8
seconds-from-time
Returns an xs:decimal value greater than or equal to zero and less than 60, representing the seconds and fractional seconds in the localized value of $arg.
Syntax
fn:seconds-from-time(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a time that must be converted to seconds.
Return Value
If $arg is an empty sequence, the function returns the empty sequence. Otherwise, the function returns an xs:decimal value greater than or equal to 0 and less than 60, representing the seconds and fractional seconds in the localized value of $arg.
Examples
The function returns the corresponding date values for the value mentioned as arg in the TIME column :
TIME
RETURN VALUE
13:20:11.5
11.5
timezone-from-date
Returns the timezone component of $arg, if any.
Syntax
fn:timezone-from-date(arg)
Return Value
If $arg has a timezone component, the result is an xs:dayTimeDuration that indicates deviation from UTC. Its value might range from +14:00 to -14:00 hours, both inclusive. Otherwise, the result is an empty sequence.
Examples
The function returns the corresponding date values for the value mentioned in the DATE column:
DATE
RETURN VALUE
1999-05-31-05:00
-PT5H
timezone-from-dateTime
Returns the timezone component of an xs:dateTime.
Syntax
fn:timezone-from-dateTime(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a datetime that must be converted to a timezone.
Return Value
If $arg is an empty sequence, the function returns the empty sequence. Otherwise, the function returns the timezone component of $arg, if any. If $arg has a timezone component, the result is an xs:dayTimeDuration that indicates deviation from UTC. Its value might range from +14:00 to -14:00 hours, both inclusive. If $arg has no timezone component, the result is the empty sequence.
Examples
The function returns the corresponding date values for the value mentioned in the DATETIME column:
DATETIME
RETURN VALUE
1995-05-31T13:20:00-05:00
-PT5H
timezone-from-time
Returns the timezone component of an xs:time.
Syntax
fn:timezone-from-time(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a time that must be converted to a timezone.
Return Value
If $arg is an empty sequence, the function returns the empty sequence. Otherwise the fucntion returns the timezone component of the xs:time value supplied as argument. The result is an xs:dayTimeDuration that indicates deviation from UTC. Its value might range from +14:00 to -14:00 hours, both inclusive.
Examples
The function returns the corresponding date values for the value mentioned in the TIME column:
TIME
RETURN VALUE
12:00:00-05:00
-PT5H
toChar
Converts the date passed to a string based on the specified format string.
Syntax
date:toChar(date, format)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a date that must be converted to a specified format string.
Examples
The function returns the corresponding date values for the value mentioned as arg in the DATE column:
DATE
RETURN VALUE
date:toChar(08-02-2017, DL)
August 02, 2017
toDate
Converts a character string to a date data type. You use the toDate format strings to specify the format of the source strings.
The output port must be Date/Time for toDate expressions.
If you are converting two-digit years with toDate, use either the RR or YY format string. Do not use the YYYY format string.
Syntax
date:toDate(xs:dateTime('date'), 'format')
Note: You must manually add the date phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/
Optional
Description
date
Required
Must be a string data type. Passes the values that you want to convert to dates. You can enter any valid transformation expression.
format
Required
Enter a valid toDate format string. The format string must match the parts of the date argument. For example, if you pass the date 'Mar 15 1998 12:43:10AM', you must use the format string 'MON DD YYYY HH12:MI:SSAM'.
Return Value
Date.
The toDate function always returns a date and time. If you pass a string that does not have a time value, the date returned always includes the time 00:00:00.000000000. You can map the results of this function to any target column with a datetime data type.
The following table lists some sample values and return values:
DATE_STR
RETURN_VALUE
'12/31/1999 3783'
12/31/1999 01:02:03
'09/15/1996 86399'
09/15/1996 23:59:59
trunc (Dates)
Truncates dates to a specific year, month, day, hour, minute, second, or millisecond.
You can truncate the following date parts:
•Year. If you truncate the year portion of the date, the function returns Jan 1 of the input year with the time set to 00:00:00.000000000. For example, the following expression returns 1/1/1997 00:00:00.000000000:
•Month. If you truncate the month portion of a date, the function returns the first day of the month with the time set to 00:00:00.000000000. For example, the following expression returns 4/1/1997 00:00:00.000000000:
•Day. If you truncate the day portion of a date, the function returns the date with the time set to 00:00:00.000000000. For example, the following expression returns 6/13/1997 00:00:00.000000000:
•Hour. If you truncate the hour portion of a date, the function returns the date with the minutes, seconds, and milliseconds set to 0. For example, the following expression returns 4/1/1997 11:00:00.000000000:
•Minute. If you truncate the minute portion of a date, the function returns the date with the seconds and milliseconds set to 0. For example, the following expression returns 5/22/1997 10:15:00.000000000:
•Second. If you truncate the second portion of a date, the function returns the date with the milliseconds set to 0. For example, the following expression returns 5/22/1997 10:15:29.000000000:
•Millisecond. If you truncate the millisecond portion of a date, the function returns the date with the microseconds set to 0. For example, the following expression returns 5/22/1997 10:15:30.135000000:
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:31AM
Jan 15 1998 02:00:00.000000000
Apr 19 1998 1:31:20PM
Apr 19 1998 13:00:00.000000000
Jun 20 1998 3:50:04AM
Jun 20 1998 03:00:00.000000000
Dec 20 1998 3:29:55PM
Dec 20 1998 15:00:00.000000000
Dec 31 1998 11:59:59PM
Dec 31 1998 23:00:00.000000000
NULL
NULL
The following expression truncates the minute portion of each date in the DATE_SHIPPED column:
date:trunc(xs:dateTime('DATE_SHIPPED'), 'MI')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 15 1998 02:10:00.000000000
Apr 19 1998 1:31:20PM
Apr 19 1998 13:31:00.000000000
Jun 20 1998 3:50:04AM
Jun 20 1998 03:50:00.000000000
Dec 20 1998 3:29:55PM
Dec 20 1998 15:29:00.000000000
Dec 31 1998 11:59:59PM
Dec 31 1998 23:59:00.000000000
NULL
NULL
year-from-date
Returns an xs:integer representing the year in the localized value of $arg. The value might be negative.
Syntax
fn:year-from-date(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a date that must be converted to a year format.
Return Value
If $arg is an empty sequence, the function returns the empty sequence. Otherwise, the function returns an xs:integer representing the year in the localized value of $arg.
Examples
The function returns the corresponding year values for the value mentioned as arg in the DATE column:
DATE
RETURN VALUE
1999-06-30
1999
2001-01-01+05:00
2001
year-from-dateTime
Returns an xs:integer representing the year component in the localized value of $arg.
Syntax
fn:year-from-dateTime(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a datetime that must be converted to a year format.
Return Value
If $arg is an empty sequence, the function returns the empty sequence. Otherwise, the function returns an xs:integer representing the year in the localized value of $arg.
Examples
The function returns the corresponding year values for the value mentioned as arg in the DATETIME column:
DATETIME
RETURN VALUE
1999-06-30+08:00
1999
2001-01-01+05:00
2001
years-from-duration
Returns an xs:integer representing the years component in the value of $arg. The result is obtained by casting $arg to an xs:yearMonthDuration.
Syntax
fn:years-from-duration(arg)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument as a duration that must be converted to years.
Return Value
If $arg is an empty sequence, the function returns the empty sequence. Otherwise, the function returns an xs:integer representing the years component in the value of $arg. Given that a duration is a ($months, $seconds) tuple, the result is the value of ($months idiv 12).
Examples
The function returns the corresponding date values for the value mentioned as arg in the DURATION column:
DATETIME
RETURN VALUE
P20Y15M
21
-P15M
-1
Miscellaneous functions
The following functions are available in the Miscellaneous section of the Expression Editor:
aesEncryption
Encrypts the specified data with the provided key using the Advanced Encryption Standard (AES) algorithm.
Syntax
util:aesEncryption(key, dataToEncrypt)
Return Value
The function returns an encrypted value.
Examples
The following example returns encrypted values for the data and the key passed as inputs in the function:
SAMPLE FUNCTION
OUTPUT
util:aesEncryption("1234@abc", "Informatica")
QETcr60N6QBSTB0X8V4Y+GL\/K0+nt7M6ON0VommGAU4=
util:aesEncryption("abcdefghkl", "cloud")
tceVjWKW2lKWQ+ZJfHn4gUKXPDUPrEilmNoTueesPo0=
aesDecryption
Decrypts the specified data with the provided key using the Advanced Encryption Standard (AES) algorithm. You must use the same key that you had used for encrypting the data in the aesEncryption function.
Syntax
util:aesDecryption(key, dataToDecrypt)
Return Value
The function returns a decrypted value.
Examples
The following example returns decrypted values for the data and the key passed as inputs in the function:
Searches a column for a value that you specify. If the function finds the value, it returns a result value, which you define. You can build an unlimited number of searches within a decode function.
If you use decode to search for a value in a string column, you can either trim trailing blank characters with the rtrim function or include the blanks in the search string.
You can enter any valid transformation expression. You can pass any data type except Binary.
To pass a NULL value, you must specify an empty sequence in the following format: ()
search1
Required
Passes the values for which you want to search.
You can enter any valid transformation expression. You can pass any value with the same data type as the value argument. The search value must match the value argument. You cannot search for a portion of a value. Also, the search value is case sensitive.
For example, if you want to search for the string 'Halogen Flashlight' in a particular column, you must enter 'Halogen Flashlight, not just 'Halogen'. If you enter 'Halogen', the search does not find a matching value.
To pass a NULL value, you must specify an empty sequence in the following format: ()
result1
Required
The value that you want to return if the search finds a matching value.
You can enter any valid transformation expression and pass any data type except Binary.
To pass a NULL value, you must specify an empty sequence in the following format: ()
args
Required
Pairs of search values and result values separated by a comma.
The return values in this expression are 5, 1.414213562, and 3.141592654. The first result is an integer, and the other results are decimal. The decimal data type has a greater precision than the integer data type. This expression always writes the result as a decimal value.
You cannot create a decode function with both string and numeric return values.
For example, the following expression is not valid:
The following table lists some sample values and return values:
ITEM_ID
RETURN VALUE
10
Flashlight
14
Regulator
17
NONE
20
Knife
25
NONE
NULL
NONE
40
Tank
The decode function returns the default value of NONE for items 17 and 25 because the search values did not match the ITEM_ID. Also, decode returns NONE for the NULL ITEM_ID.
The following expression tests multiple columns and conditions, evaluated in a top to bottom order for TRUE or FALSE:
util:decode( TRUE, Var1 = 22, 'Variable 1 was 22!', Var2 = 49, 'Variable 2 was 49!', Var1 < 23, 'Variable 1 was less than 23.', Var2 > 30, 'Variable 2 was more than 30.', 'Variables were out of desired ranges.')
The following table lists some sample values and return values:
Var1
Var2
RETURN VALUE
21
47
Variable 1 was less than 23.
22
49
Variable 1 was 22!
23
49
Variable 2 was 49!
24
27
Variables were out of desired ranges.
25
50
Variable 2 was more than 30.
escape-Json-String
Escapes special characters in the provided string to make it valid for usage in JSON.
Syntax
util:escapeJsonString(str)
Return Value
Encodes and returns the value as a JSON string according to http://json.org/ rules.
Examples
The function returns the following values for various strings provided as input:
STRING INPUT
RETURN VALUE
//
/\/\
Item1, Items2
Item1, Items2
\n
\\\\n
getAssetLocation
Returns the location where the process or the guide that uses the function is stored.
Note: In addition to guides, you can use the getAssetLocation function only in processes that run on the Cloud Server.
Syntax
util:getAssetLocation()
Return Value
The function returns the location of the folder where the asset exists.
getAssetName
Returns the name of the process or the guide that uses the function.
Syntax
util:getAssetName()
Return Value
The function returns the asset name as a string in the output.
getInstanceStartTime
Returns the start time of the running instance of the specified process or guide.
Syntax
util:getInstanceStartTime()
Return Value
The function returns the start time of the running instance corresponding to the specified asset. The function returns an output in the yyyy-MM-dd'T'HH:mm:ss.SS'Z' format.
Examples
The following sample displays the result of the function that is used in a process:
2023-09-20T09:02:50.774Z
getOrganizationId
Returns the Informatica Intelligent Cloud Services organization ID string in the context of the executing instance.
Syntax
util:getOrganizationId()
Return Value
When the process is deployed on the agent, the function returns the output as $public.
When the process is deployed on the cloud server, the function returns the Informatica Intelligent Cloud Services organization ID string as the output.
Examples
The following sample displays the result of the function:
getOrganizationName
Returns the organization name in the context of the executing process, guide, service connector, or data access service connector.
Syntax
util:getOrganizationName()
Return Value
The function returns the organization name string as the output.
iif
Returns one of two values that you specify based on the results of a condition.
Syntax
util:iif(condition, val1 ,val2)
The following table describes the arguments:
Argument
Required/Optional
Description
condition
Required
The condition that you want to evaluate.
You can enter any valid transformation expression that evaluates to TRUE or FALSE.
val1
Required
The value that you want to return if the condition is TRUE. The return value is always the data type specified by this argument.
You can enter any valid transformation expression, including another iif expression. You can pass any data type except Binary.
val2
Optional
The value that you want to return if the condition is FALSE.
You can enter any valid transformation expression, including another iif expression. You can pass any data type except Binary.
The FALSE (val2) condition in the iif function is not required. If you omit val2, the function returns one of the following values when the condition is FALSE:
•0 if val1 is a Numeric data type.
•Empty string if val1 is a String data type.
•NULL if val1 is a Date/Time data type.
For example, the following expression does not include a FALSE condition and val1 is a string data type so decode returns an empty string for each row that evaluates to FALSE:
util:iif(SALES > 100, EMP_NAME)
The following table lists some sample values and return values:
SALES
EMP_NAME
RETURN VALUE
150
John Smith
John Smith
50
Pierre Bleu
'' (empty string)
120
Sally Green
Sally Green
NULL
Greg Jones
'' (empty string)
Return Value
val1 if the condition is TRUE.
val2 if the condition is FALSE.
For example, the following expression includes the FALSE condition NULL so decode returns NULL for each row that evaluates to FALSE:
util:iif(SALES > 100, EMP_NAME, NULL)
The following table lists some sample values and return values:
SALES
EMP_NAME
RETURN VALUE
150
John Smith
John Smith
50
Pierre Bleu
NULL
120
Sally Green
Sally Green
NULL
Greg Jones
NULL
iif and data types
When you use iif, the data type of the return value is the same as the data type of the result with the greatest precision.
For example, you have the following expression:
util:iif(SALES < 100, 1, .3333)
The TRUE result (1) is an integer and the FALSE result (.3333) is a decimal. The Decimal data type has a greater precision than the Integer data type. Therefore, the data type of the return value is always a decimal value.
Special uses of iif
Use nested iif statements to test multiple conditions. The following example tests for various conditions and returns 0 if sales is 0 or negative:
Matches input data to a list of values. By default, the match is case sensitive.
Syntax
util:in(valueToSearch, values, caseFlag)
The following table describes the arguments:
Argument
Required/
Optional
Description
valueToSearch
Required
Can be a string, date, or numeric value. Input value that you want to match against a comma-separated list of values.
To pass a NULL value, you must specify an empty sequence in the following format: ()
values
Required
Can be a string, date, or numeric value. Comma-separated list of values that you want to search for. Values can be columns. There is no limit to the maximum number of values that you can list.
To pass a NULL value, you must specify an empty sequence in the following format: ()
caseFlag
Optional
Must be an integer. Determines whether the arguments in this function are case sensitive. You can enter any valid transformation expression.
When caseFlag is a number other than 0, the function is case sensitive.
When caseFlag is a null value or 0, the function is not case sensitive.
Return Value
TRUE (1) if the input value matches the list of values.
FALSE (0) if the input value does not match the list of values.
NULL if the input is a null value.
Example
The following expression determines if the input value is a safety knife, chisel point knife, or medium titanium knife. The input values do not have to match the case of the values in the comma-separated list:
util:in(ITEM_NAME, ‘Chisel Point Knife’, ‘Medium Titanium Knife’, ‘Safety Knife’, 0)
The following table lists some sample values and return values:
ITEM_NAME
RETURN VALUE
Stabilizing Vest
0 (FALSE)
Safety knife
1 (TRUE)
Medium Titanium knife
1 (TRUE)
NULL
isNull
Returns whether a value is NULL.
Syntax
util:isNull(value)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
value
Required
Passes the rows that you want to evaluate. You can enter any valid transformation expression. You can pass a value of any data type except Binary.
Return Value
TRUE (1) if the value is NULL.
FALSE (0) if the value is not NULL.
Example
The following example checks for NULL values in the items table:
util:isNull( ITEM_NAME )
The following table lists some sample values and return values:
ITEM_NAME
RETURN VALUE
Flashlight
0 (FALSE)
NULL
1 (TRUE)
Regulator system
0 (FALSE)
''
0 (FALSE) Empty string is not NULL
toDecimal
Converts a string or numeric value to a decimal value. The toDecimal function ignores leading spaces.
Syntax
util:toDecimal(value, scale)
The following table describes the arguments:
Argument
Required/
Optional
Description
value
Required
Must be a string or numeric data type. Passes the value that you want to convert to decimals.
You can enter any valid transformation expression.
scale
Optional
Must be an integer literal between 0 and 28, inclusive. Specifies the number of digits allowed after the decimal point. If you omit this argument, the function returns a value with the same scale as the input value.
This argument is required in advanced mode.
Return Value
Decimal of precision and scale between 0 and 28, inclusive.
0 if the value in the selected column is an empty string or a non-numeric character.
NULL if a value passed to the function is NULL.
Example
This expression uses values from the column IN_TAX. The data type is decimal with precision of 10 and scale of 3:
util:toDecimal(IN_TAX, 3)
The following table lists some sample values and return values:
IN_TAX
RETURN VALUE
'15.6789'
15.679
'60.2'
60.200
'118.348'
118.348
NULL
NULL
'A12.3Grove'
0
This expression uses values from the column Sales. The data type is decimal with precision of 10 and scale of 2:
util:toDecimal(Sales, 2)
The following table lists some sample values and return values:
Sales
RETURN VALUE
'1234'
1234
'1234.01'
1234.01
If you want the return value in 1234.00 format, you can use the following expression:
format-number(util:toDecimal('1234', 2), '0.00')
Decimal overflow
If the size of the number on the left hand side of the decimal point exceeds the precision, the decimal operation overflows.
To resolve this, modify the scale and/or precision of the expression port and connect downstream ports of the mapping to accommodate the size of the input data in the expression.
For example:
If a numeric field is defined to be size 28 with scale of 15, this accepts 13 numbers on the left side of the decimal and 15 on the right. So the following numbers would be valid for a number of 28,15:
1234567890123.11143
13575.123451234567891
However, these numbers would cause a decimal overflow error:
111112222233333.4444
123.1111122222333334
toInteger
Converts a string or numeric value to an integer. The toInteger syntax contains an optional argument that you can choose to round the number to the nearest integer or truncate the decimal portion. The toInteger function ignores leading spaces.
Syntax
util:toInteger(value, flag)
The following table describes the arguments:
Argument
Required/
Optional
Description
value
Required
Must be a string or numeric data type. Passes the value that you want to convert to an integer.
You can enter any valid transformation expression.
flag
Optional
Specifies whether to truncate or round the decimal portion. The flag must be an integer literal or the constants TRUE or FALSE:
- toInteger truncates the decimal portion when the flag is TRUE or a number other than 0.
- toInteger rounds the value to the nearest integer if the flag is FALSE or 0 or if you omit this argument.
Return Value
Integer.
NULL if a value passed to the function is NULL.
0 if a value passed to the function contains alphanumeric characters.
Example
The following expressions use values from the column IN_TAX:
util:toInteger(IN_TAX, fn:boolean(1))
The following table lists some sample values and return values:
IN_TAX
RETURN VALUE
'15.6789'
15
'60.2'
60
'118.348'
118
NULL
NULL
'A12.3Grove'
0
' 123.87'
123
'-15.6789'
-15
'-15.23'
-15
trace
Provides an execution trace to be used in debugging queries.
The fn:trace function accepts items and a label for those items and returns the items unchanged. The exact behavior of the function is implementation-dependent, but generally the processor puts the label and the value of the items in a log file or user console.
Syntax
fn:trace(value, label)
The following table describes the arguments:
Argument
Required/
Optional
Description
value
Required
The items to trace.
label
Optional
A label to display with the trace information.
Return Value
Based on the items and a label for those items, it returns the items unchanged.
Example
Assume that you have the following variable definition:
let $var1 := 5
The following expression returns The correct value is: 5 as the result:
fn:trace($var1, 'The correct value is: ')
trunc (Numbers)
Truncates numbers to a specific digit based on the number of decimal places specified by the precision.
Syntax
util:trunc(arg, precision)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument that you want to truncate. You can enter any valid expression that evaluates to a numeric data type.
precision
Optional
Can be a positive or negative integer. You can enter any valid expression that evaluates to an integer. The integer specifies the number of digits to truncate.
If precision is a positive integer, trunc returns arg with the number of decimal places specified by precision. If precision is a negative integer, trunc changes the specified digits to the left of the decimal point to zeros. If you omit the precision argument, trunc truncates the decimal portion of arg and returns an integer.
All trailing zeros after the decimal point in an argument will be truncated. For example, the following expression returns 2345.7535 as the result:
util:trunc(2345.75350000, 6)
If a number contains more than 16-digits after the decimal point, the result returns an exponential value due to an XQuery limitation.
For example, the following expression returns 1.234567812345679E7 as the result:
util:trunc(12345678.12345678901234567890, 15)
If you pass a decimal precision value, the arg is rounded to the nearest integer before evaluating the expression.
Return Value
Numeric or integer value based on the parameters provided in the function.
Examples
The following expressions truncate the values in the PRICE column:
util:trunc(PRICE, 3)
PRICE
RETURN VALUE
12.9995
12.999
-18.8652
-18.865
56.9563
56.956
15.9928
15.992
util:trunc(PRICE, -1)
PRICE
RETURN VALUE
12.99
10
-187.86
-180
56.95
50
1235.99
1230
util:trunc(PRICE )
PRICE
RETURN VALUE
12.99
12
-18.99
-18
56.95
56
15.99
15
simplifyXml
Rearranges data within XML so that it can be used by the process objects.
Syntax
util:simplifyXml(undefined)
Return Value
When the process is deployed on the cloud server or agent, the function returns an output without name spaces and convert attributes into child elements.
Examples
The function returns the following output for the XML data provided as input:
INPUT
OUTPUT
<ns:root xmlns:ns="https://qwerty" attrKey="attrValue"> data </ns:root>
The following XML functions are available in the XML Nodes section of the Expression Editor:
data
The atomic value of a node.
The fn:data function takes a sequence of items and returns a sequence of atomic values.
Syntax
fn:data(arg)
The following table describes the argument:
Argument
Required/
Optional
Description
arg
Required
The items whose typed values are to be returned.
Return Value
For atomic values, it returns the unchanged value. For nodes, it extracts the typed value of the node.
Example
The following expression returns 123, 456 as the result:
fn:data((123, 456))
Assume that you have the following variable definition:
let $x := <hello>Hello
let $x := <hello>Hello <world>World</world> </hello>
The expression fn:data($x) returns the following result:
Hello World
deep-equal
Assesses whether two nodes have the same content and attributes.
To be deep-equal, they must contain items that are pairwise deep-equal; and for two items to be deep-equal, they must either be atomic values that compare equal, or nodes of the same kind, with the same name, whose children are deep-equal. The $collation argument identifies a collation which is used at all levels of recursion when strings are compared, but not when names are compared.
Syntax
fn:deep-equal(parameter1, parameter2, collation)
The following table describes the arguments:
Argument
Required/
Optional
Description
parameter1
Required
The first sequence to compare.
parameter2
Required
The second sequence to compare.
collation
Optional
The collation to use for comparing strings. The collation argument identifies the collation to be used at all levels of recursion when strings are compared, but not when names are compared.
Return Value
Returns true if both the $parameter1 and $parameter2 sequences contain the same values, in the same order.
Atomic values are compared based on their typed values, using the eq operator. If two atomic values cannot be compared, for example, if one is a number, and the other is a string, the function returns false rather than raising an error.
Examples
The following expression returns true as the result:
fn:deep-equal (1, 1)
The following expression returns false as the result:
fn:deep-equal ((1,2), (2,1))
The following expression returns false as the result:
fn:deep-equal (123, John)
root
Returns the root of the tree that contains the argument.
Syntax
fn:root(arg)
The following table describes the argument:
Argument
Required/
Optional
Description
arg
Required
The node whose root node will be returned.
Return Value
The fn:root function returns a document node if the $arg node is part of a document. However, it returns an element if the $arg node is not part of a document.
If $arg is the empty sequence, the empty sequence is returned.
If the function is called without an argument, the context item is used as the default argument. If the context item is undefined or is not a node, an error occurs.
Example
Assume that you have the following variable definition:
let $in-xml := <a><x>123</x></a>
The expression root($in-xml/x) returns the following result:
<a> <x>123</x> </a>
Specifying Functions and Variables
To configure service connectors, you may need to specify variables and functions to define bindings, output fields, and other properties.
Built-in Variables
You can reference these variables in a service connector using an XQuery expression:
Variable
Variable Type
Description
$VariableName
All connection properties
Properties, input and Other Parameters can be specified using this format.
$ResponseStatusCode
Output field mapping
HTTP response code.
$ResponseHeaders
Output field mapping
Contains the HTTP response header in an element list where each item is:
<header name="Content-Type">text/plain</header>
For example:
$ResponseHeaders[@name = "Content-Type"]/text()
$RESTResponse
Output field mapping
Contains the RESTResponse XML data, including the headers and code. Visible in the Test Results for the Service Connector.
Output Field Mapping Functions
When you define bindings in the service connector, you can use many functions in the Expression Editor.
Returns a response header value. If the header parameter is not defined, this function returns the optional default value.
getResponseHeaderNames
svc:getResponseHeaderNames( $ResponseHeaders ) : list of strings
Returns a list that contains the names of all the parameters within a response header.
Digital Signature Functions Overview
Using the digital signature functions available for Process Designer, you can use XQuery to create processes and service connectors that handle signing of content. The supported functions are either:
•Symmetric, signed using Hash-based Message Authentication Code (HMAC), which is based on a shared password and key, and allows you to use multiple key formats.
•Asymmetric, signed using private keys (for example, PKCS8, PKCS12, Java KeyStore).
One common use for signed digital content is to allow for API authentication in a service connector, which allows you to use hashing and related functions on string-based content. For example, you might need to use signed headers as part of the payload
For more information on each of the functions, see Using Functions.
Note: There are currently no supported functions to verify signed content, or to sign binary content.
HMAC
The HMAC signing method is used with many services, including:
•Amazon Web Services (AWS)
•Twitter OAuth
Process Designer supports HMAC SHA1, HMAC SHA256, and HMAC SHA512 with Base64, Hex, Hex64 or Base64Url encoding. For example, you might use one of these functions to sign content for AWS:
•dsig:hmacSignature( $data as xs:string, $key as xs:string, $algorithm as xs:string, $encoding as xs:string? ) as xs:string
•dsig:hmacSHA1signature( $data as xs:string, $key as xs:string, $encoding as xs:string? ) as xs:string
•dsig:hmacSHA256signature( $data as xs:string, $key as xs:string, $encoding as xs:string? ) as xs:string
•dsig:hmacSHA512signature ( $data as xs:string, $key as xs:string, $encoding as xs:string?) as xs:string
AWS Authentication
The AWS REST API requires the authentication header in this format:
Note: If using a private key to sign content, the keystore file must reside on a Secure Agent and the process or service connector must run on the Secure Agent in order to access the key. The file path to the key must be specified in the object properties (for example, "C:/certs/mykey.p12").
You can also:
•Deploy the PKCS8/12 artifact on an agent contribution and specify its location using the 'project:/' scheme.
•Supply the key using encoded key content instead of a file location, which may be useful during development of a service connector. For example, you can paste the encoded content into a text area for test purposes.
PKCS8 Key File
To sign with the PKCS8 key file, use this function:
dsig:signWithKeyFile( $messageToSign as xs:string, $pathToKey as xs:string, $encryptionAlgorithm as xs:string, $digestAlgorithm as xs:string ) as xs:string
The key file can be binary or in Base64-encoded format.
P12 and Keystore Based Files
To sign with a P12 or KeyStore-based file, use this function:
dsig:signWithCertificate( $messageToSign as xs:string, $pathToCertificate as xs:string, $keyRecoveryPassword as xs:string, $encryptionAlgorithm as xs:string, $digestAlgorithm as xs:string ) as xs:string
For example, you can use this method for Google JWT (JSON Web Token) Authentication for service accounts. After you obtain a Google private key for access and save it on your Secure Agent, you create a JWT request and sign it with your private key.
Calculates a hash-based message authentication code (HMAC) using the SHA256 algorithm and the optional encoding where $encoding is one of the following values: Base64 (default), Base64Url, Hex, or Hex64.
A sequence of one or more characters based on which you want to split the string.
secretKey
Required
The secret key for HMAC computation. The key can be of any length. However, the recommended size is 64 bytes. If the key is more than 64 bytes long, it is hashed using SHA-256 to derive a 32-byte key.
encoding
Required
Type of encoding used for the output. For example, you can calculate HMAC using the SHA256 algorithm where encoding is one of the following values: Base64 (default), Base64Url, Hex, or Hex64.
Attachments
Process Designer exposes an endpoint so you can access attachments in a process using an event handler, internal API calls, custom service connectors or built-in connectors like the Amazon S3 Connector. This enables you to pass through an attachment and extract data about the attachment like the file size and file name using functions on the response (where supported).
When you design a process, you can pass attachments into a subprocess, service connector, or Service steps or return attachments (where the output field is defined using the Attachment type).
In a process, you can allow the user to add one or more attachments as inputs where the attachment is coming directly from the screen (not carried across steps using process fields). For example, you could use a process to capture real property data and allow the user to attach photos of the associated property. However, you cannot access the attachment data within a process.
Note: If you run a process on the cloud server, do not use an attachment whose size is more than the default 5,242,880 bytes. The cloud server cannot process attachments that are greater than 5,242,880 bytes.
You can specify named input parameters as attachments using two Simple data types:
- Attachment supports single attachments and allows you to define the maximum file size as a field property.
- Attachments supports multiple attachments and allows you to define the maximum file size and maximum number of files as field properties.
From within the process, you can access metadata about the attachment using an XQuery formula and built-in functions. For example, the following returns the size of the 'photo' input:
sff:getAttachmentSize($input.photo)
You can assign input attachment fields to outputs or temp fields using an Assignment step. In that case, be sure that the target temp or output field is also of type Attachment.
In this example, the second parameter ($encodedContent) is a base64-encoded string:
Note: The attachment functions are available in processes but not in service connectors. See below for more information on handling attachments in service connectors.
Refer to Using Functions for more information on the functions available for use with attachments.
Attachment Payload
When using simple attachments, be sure that:
•Each named part has the same name (name and Content-Disposition) as the Input parameter in the process.
•The Content-Type of the part must also match the content type of the Input parameter. For all data types except attachments, Content-Type= text/plain (for example, text, checkbox, date).
•If the part represents a process object, its contents should be valid, serialized JSON content (with a Content-Type of application/json).
•You define process output field (s) of type Attachment (or Attachments). These fields are returned as attachments to the caller.
For example, if the process has input fields "first" (Text), "last" (Text), and "inputFile" (Attachment), the payload would be similar to the following:
POST /active-bpel/public/rt/Attachments_Test HTTP/1.1 Content-Type: multipart/form-data; boundary=----TheBoundary1234 ------TheBoundary1234 Content-Disposition: form-data; name="first" John ------TheBoundary1234 Content-Disposition: form-data; name="last" Smith ------TheBoundary1234 Content-Disposition: form-data; name="inputFile"; filename="filename.png" Content-Type: image/png ... binary image data ... ------TheBoundary1234--
The first part of the response should be a JSON document describing the response output fields. The JSON content will have the output field values (standard response).
If the output field is of type Attachment, the value will be a string containing the content-id (cid) of the part containing the attachment. For example, if the output fields are the "first" (Text), "last" (Text), and "inputFile" (Attachment), the response should look similar to:
•If you do not enable Output field is whole payload in the process properties and you have a single output field of type attachment, the response contains both the attachment data and the metadata, as shown here:
--Boundary content-type: ...
cid:outputFile
--Boundary content-type: image/jpeg
[binary data]
If you enable Output field is whole payload and you have exactly one output field of type Attachment, you can receive a single attachment (of type text or binary) without the metadata. If a third-party wants to access only the attachment, it is easier to consume. For example:
HTTP/1.1 200 OK Content-Type: image/jpeg Content-Length: length
[.. JPEG data ...]
Using Attachments in Service Connectors
You can define a service connector in order to submit an attachment from a process to a service connector's input or obtain an attachment from a service connector and submit it to a process as an attachment.
Attachments are sent directly to the service input (including Salesforce-based services) from processes and service connectors.
Note: You upload or download content from services like Amazon S3 and propagate the attachment back to the main process under the name defined in the output mapping. With the Amazon S3 Connector, you can read or write the contents of an S3 object both as a Base64-encoded string and as an attachment. For more information, see the Amazon S3 Connector Guide.
Using Multiple Output Attachments in Service Connectors
A service connector can handle a multipart response appropriately and create multiple attachments for different response parts. The part related metadata information is available in the attachment properties.
A service connector can also recognize the payload part. The first JSON/XML/Text part is used as the payload. You can extract data from the payload in the output field expressions of the action as you would normally do when you work with non-multipart responses. Note that the selected payload will not be available as an attachment.
In the Output tab, you can use the Get from Attachments option to work with multiple attachments and pass the entire list of attachments to the selected variable except the payload.
You can also use an expression to work with attachments in service connectors. When you open the Expression Editor, you can find the new functions in the Functions tab, under the Output Attachments section.
Using GET, POST, and PUT
You can define a service connector to use GET, POST, or PUT operations on a resource that does not support JSON or XML.
Note that:
•The input parameters (Attachment type) must be uploaded to the destination from the binding URL (POST/PUT). Use a payload format based on the binding type and number of attachments.
•If there are multiple attachment parameters and the binding is FORM, send all the parameters (including non-file parameters) as multipart/form-data.
•If there are one or more attachment parameters and the binding is JSON, send files as multipart/form-data and combine non-file parameters into a single JSON element.
•If there is exactly one file parameter, the binding is CUSTOM, and the binding payload is empty (no XML or JSON in a custom textarea), POST or PUT the raw file data as is.
•If there are multiple attachment parameters and the binding is CUSTOM and the payload is XQuery, the HTTP outbound payload should be multipart/related. The first part is the CUSTOM (XQuery) content, followed by parts for each of the file/attachment input parameters.
•You can assign input attachment fields to outputs or temp fields using an Assignment step. In that case, be sure that the target temp or output field is also of type Attachment.
•Downloading multiple files (using multipart/mime attachment) is not supported.
Note: The "sff" functions are available only in processes.
HTTP and JMS Headers
Use header functions to get details about headers that you pass when you invoke a process. You can get details about process headers and message event headers.
Use header functions to get information that is only available in the header that you pass when you invoke a process. Examples include dates, JSON Web Tokens, and security IDs.
You can assign the header value to a field and, optionally, perform XQuery operations on the value.
The following image shows the header functions available under the Request Context section of the Expression Editor:
To understand how each function works, consider a process invoked with the following headers and values:
Header
Value
accept-language
en-US;q=0.9
client-ip
192.0.2.1
accept
text/xml
postman-token
0023-a5dfd-8vdg3-n2b3
CustomHeader
This is a custom header
ExecutionDate
Wed, July 25 2018 06:25:24 GMT
You can use the following header functions on their own, or along with other functions:
Get All Header Values
Use the getAllHeaders function to get a list of all headers and their values.
For example, if you use the following expression, you get a specific output:
Expression:
fn:getAllHeaders()
Output:
<headers> <header name="accept-language">en-US;q=0.9</header> <header name="client-ip">192.0.2.1</header> <header name="accept">text/xml</header> <header name="postman-token">0023-a5dfd-8vdg3-n2b3</header> <header name="CustomHeader">This is a custom header</header> <header name="ExecutionDate">Wed, July 25 2018 06:25:24 GMT</header> </headers>
Get Date Headers
Use the getDateHeader function to get the value of a Date header. The function returns data in the dateTime type.
You can use other Date and Time functions to parse the value.
For example, if you use the following expression, you get a specific output:
Use the getHeaderOrDefault to get a header value if the header exists, or some default value if the header does not exist.
To use this function, you must give the header name and a default value as expression parameters.
Let the default value be Not Available.
For example, if you use the following expression, you get a valid output:
Expression:
concat ("The header I want is", request:getHeaderOrDefault("postman-token","Not Avaialble" )
Output:
The header I want is 0023-a5dfd-8vdg3-n2b3
If you use the following expression, you get a Not Available output:
Expression:
concat ("The header I want is", request:getHeaderOrDefault("special-token","Not Avaialble" )
Output:
The header I want is Not Available
This is because the request does not contain a header with the name special-token.
Check if a Specific Header Exists
Use the headerExists function to check if a header with a specific name exists.
You get either True or False in the output.
For example, if you use the following expression, you get a specific output:
Expression:
request:headerExists("CustomHeader")
Output:
True
Headers from Message Events
If a process has message events, you can use any header functions to get headers from the message events. To do this, you must add the case-sensitive message event name as an expression parameter.
For example, if you pass a header with the name CustomHeader to the message event receive step, use the following expression to get the value of CustomHeader from the message event ME1.
request:getHeader("CustomHeader","ME1")
HTTP verb functions
Use HTTP verb functions to retrieve the HTTP verb and the resource path segments that are used in a request. The HTTP verb functions are available under the Request Context section of the Expression Editor.
You can use the following HTTP verb functions:
getRequestHTTPVerb
Use the getRequestHTTPVerb function to determine the HTTP verb that is used in a request. The function retrieves the HTTP verb from the request and returns one of the following responses in a string format:
- GET
- POST
- PATCH
- PUT
- DELETE
getResourcePathSegments
Use the getResourcePathSegments function to retrieve all or specific resource path segments of REST requests. The function returns the values as a string of tokens.
If you use the request:getResourcePathSegments() expression, you see the following response:
[Orders, OrderID_100, quantity, 5]
To retrieve a specific resource path segment, use a numeric qualifier to denote the position of the resource path segment from the process name.
For example, to retrieve the OrderID_100 segment alone in the response, use the following expression:
request:getResourcePathSegments()[2]
Note: You cannot use the getResourcePathSegments function to fetch the resource path segments of SOAP requests and message events. You cannot use the getResourcePathSegments function to fetch the host context.
Human task XQuery utilities
If you use a Human Task step in a process, you can use human task utility functions when working with XQuery in Process Designer or in any field that uses a formula to set a value.
The human task utility functions are available under the Human Task Utilities section of the Expression Editor.
The following table describes the human task utility functions:
Function
Syntax
Description
getHumanTaskId
task:getHumanTaskId(humanTaskStepName)
Returns the ID of the human task associated with the specified Human Task step name.
You can use the ID for other actions such as getting information about a human task, reassigning a task, or canceling a task.
getHumanTaskInfo
task:getHumanTaskInfo(humanTaskId)
Returns the following information about the human task based on the specified human task ID:
Reassigns a human task to the specified users and groups, and returns the task ID.
If you reassign the task to a single user, the user becomes the task owner. If you reassign the task to a group or multiple users, a user must claim the task to become the task owner.
To specify multiple users or groups, use a comma character as the delimiter. For example, use the following syntax:
