fn:tokenize() and fn:string-join() in OSB

In this post, I am trying to cover functionality of both fn:tokenize() and fn:string-join() XQuery functions of OSB.

I have a situation to deal with strings with comma separated values.

Situation :

Say there is a field "ID", in a database table which consists of values as comma separated strings.

Ex: 100,200,300,400,500

And this string needs to be passed to a query as a parameter. 

Query looks like this.


Select name,dept,location from mytable where id in('100','200',300',400','500')

Here, I need to convert the string coming from database table into a proper format.


String coming from the table   :   100,200,300,400,500
Desired Format                        :    '100','200',300',400','500'

Approach:

Say, the comma separated string is in a variable of OSB Proxy Service. And the variable is $varIDs.

fn:tokenize() :

fn: tokenize() splits the comma separated string and populates each string in the variable $id. In each iteration,capture every string using  an element. 

Now, use the function fn:tokenize() to split the string into parts by removing the commas. After splitting the string we need to handle each string. Here I am capturing each string as an element.

Inside the Xquery expression of an assign action writing an expression as in the screenshot.

Resultant XML is as follow.

<tokens>

<token>100</token>

<token>200</token>

<token>300</token>

<token>400</token>

<token>500</token>

<tokens>

Say this XML content is saved in a variable $varTokens.

Now we need to construct the final string that is passed to the SQL query.

fn:string-join() :

Following is the xquery expression.

Here, we iterate over each token id inside the fn:string-join() function. First, using the concat function a string is constructed like, '100'. Now each such string is joined with each other and each one is separated by a comma (This is the actual functionality of the fn:string-join() function).

Resultant string is '100','200','300','400','500'

Say, this string is saved in a variabie $varDelimIDs of the assign action. Now this variable can be used to construct the IN clause of the SQL statement.