Creating XML structure in a recursive using xquery in OSB

Following code snippet generates the desired XML structure in a recursive way.

let $range := 50 div 10
return
let $i in (1 to xs:integer($range))
return
<root>
<boo>
{$input/MyElement/text()}
</boo>
</root>

This snippet generates the XML in the structure <root><boo>sample text</boo></root> for 5 times.

Above snippet can be used in the xquery expression builder of assign ,replace actions on the OSB proxy service or can be used in the xquery transformation.

In this snippet we can make the "50 div 10" dynamic using the xquery variables. 

Ex : $dividend div $divisor

And $input in the element <boo></boo> can be a variable which holds the XML structure from which we can extract the text.

Multiple markers at this line - An error occurred compiling the XQuery resource: The use of an unbound current node is not supported for XQuery resources

Error: Multiple markers at this line - An error occurred compiling the XQuery resource: The use of an unbound current node is not supported for XQuery resources.

We can observe this error some times during the XQuery development in OSB.
I faced this error and it was quite annoying.
Checked each line but could not find any mapping mistake.

In the last attempt there is a silly mistake which happened during copy pate.

In one of the mappings, variable name was wrongly copied.

Wrong mapping : <test>{data(testVar/GroupNumber)}</test>

Correct mapping : <test>{data($testVar/GroupNumber)}</test>

HTTP 411 error in business service

If there is a HTTP 411 error when invoking WSDL based business service within a proxy service, make sure of the following things.

1.  ServiceAccount is required if the web service has basic authentication. Add it in ServiceAccount are of HTTPTransport page of the business service configuration
2. Click on AdvancedSettings in  HTTPTransport page of the business service configuration and uncheck "UseChunkedStreamingMode"

Following is the screenshot.

Useful XQuery functions

Following are the useful XQuery functions which can come handy while doing the OSB programming.

Function to join different strings with a delimiter:

fn:string-join(
for $empName in $empList/emp:Employee/emp:Name
return
data($empName/text()),',')

DateToString and StringToDate conversion functions:

fn-bea:date-to-string-with-format('MM/dd/yyyy',fn-bea:date-from-string-with-format('MM-dd-yyyy', $requestDate))
fn-bea:date-from-string-with-format('yyyy/MM/dd', $requestDate)

Code snippet to convert any phone number format to the USA phone number format:

fn:replace(fn:replace($phoneNbr,"\(|-|\)| ",""),'(\d{3})(\d{3})(\d{4})', '($1) $2-$3')

Rounding to an integer value:

xs:int(fn:round($varTranSeqID))

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.

XQuery expression to count certain element occurances in OSB

Some times we may need to count the number of elements in an XML document that start with specific characters.

Following examples shows the solution.

Ex:

We have XML as below. And this XML is in a variable say, $varXML

<ns0:Data_Structure Name="Group">
<ns0:Lookup Name="GroupReceiverID"></ns0:Lookup>
<ns0:Lookup Name="GroupSenderID"></ns0:Lookup>
<ns0:Lookup Name="GroupVersionNumber"></ns0:Lookup>
<ns0:Property Name="AckChildInvalid"></ns0:Property>
<ns0:Property Name="GroupAgencyCode"></ns0:Property>
<ns0:Property Name="GroupChildCount"></ns0:Property>
<ns0:Property Name="GroupControlNumber"></ns0:Property>
<ns0:Property Name="GroupDate"></ns0:Property>
<ns0:Property Name="GroupID"></ns0:Property>
<ns0:Property Name="GroupReceiverID"></ns0:Property>
<ns0:Property Name="GroupSenderID"></ns0:Property>
<ns0:Property Name="GroupTime"></ns0:Property>
<ns0:Property Name="GroupTrailerControlNumber"></ns0:Property>
<ns0:Property Name="GroupVersionNumber"></ns0:Property>
<ns0:Property Name="TPName"></ns0:Property>
<ns0:Data_Structure Name="Transaction">
<ns0:Lookup Name="ActionCode"></ns0:Lookup>
<ns0:Lookup Name="TransactionID"></ns0:Lookup>
<ns0:Property Name="ActionCode"></ns0:Property>
<ns0:Property Name="TPName"></ns0:Property>
<ns0:Property Name="TransactionChildCount"></ns0:Property>
<ns0:Property Name="TransactionControlNumber"></ns0:Property>
<ns0:Property Name="TransactionID"></ns0:Property>
<ns0:Property Name="TransactionImplementationReference"></ns0:Property>
<ns0:Property Name="TransactionTrailerControlNumber"></ns0:Property>
</ns0:Data_Structure>
</ns0:Data_Structure>

We need to find the number of elements that start with ns0:L . Here, we basically counting the number of ns0:Lookup elements in the entire XML data.

The XQuery expression to find the count would be as follow.

count($varXML//*[fn:starts-with(name(),'ns0:L')])

Use this expression inside any of the message processing actions of OSB.

Subtracting Times in OSB using XQuery expression

If there is a need to calculate the Round Trip time of incoming and outgoing message in a Proxy Service of OSB we have an approach.

To subtract times you can use op:subtract-times() xquery function in OSB.

Example:

assing fn:current-time() to variable requestInTime.

Now to calculate the round trip time you can do the following in which we use the $requestInTime variable.

fn:substring-before(fn:substring(string(op:subtract-times(fn:current-time(),xs:time($requestInTime))), 3),'S')

Use above function in XQUERY expression in required action like assign, insert,replace.

Here, I've done some amount of formatting based on my requirement.

Essentially we need to use the op:subtract-times() function.

Hope this helps when you have a situation.