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
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.
