Creating an attribute within an element using OSB XQuery 11g

For example we have an xml document as follow.

<root>

<invoiceNumber></invoiceNumber>

</root>

Based on above input I want to create one attribute in side an element say,  <testelemnet>.

Following is the code snippet to achieve the required output.

<testelemnet>

{

if (data($getTaxData/invoiceNumber) = '')

then attribute documentNumber {"SALES"}

else

attribute documentNumber {data($getTaxData/invoiceNumber)}

}

</testelemnet>

 Output is as follow.

<testelement documentNumber ="SALES"/>

 Note : If there is an attribute documentNumber  within <testelemnet> an error will be raised due to the duplication of the attribute names.

Replacing $ in amount and calculating the sum of amount using OSB XQuery

XQuery Code Snippet:

let $in :=
<test>
{
for $repeat in $input/repeat
let $amount := $repeat/amount
return
<amount1>{fn:replace($amount,"\quot;,'')}</amount1>
}
</test>
return
sum($in/amount1)

Sample Input:

<input>

<repeat>

<amount>$100</amount>

</repeat>

<repeat>

<amount>100</amount>

</repeat>

</input>

Result : 200.0

P.S. An alternative to this lengthy approach will be updated as early as possible in this post.

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.