>Yesterday at the Oracle SOA and E2.0 Partner Community Forum in Utrecht I had a good talk with Edwin, Guido and Eric about OSB and one of the topics was our experience with Xquery functions in the OSB. So I mentioned some examples I used in the past and decided to post them on my blog when I had the time.
So here is one to start.
First we declare a nice namespace for our function ‘library’ in the top of our XQuery file.
declare namespace funcRBX = "http://www.rubix.nl";
Then we create our function, the following example is used for altering the format of Date and Time values to the specific format that Siebel expects. The code should be clear enough I hope with the comments in them. This is off course just one of the possible ways to do it and contains some specific environment checks I had to use. For instance this function was also used when the source is not XML but files (the old BEA MFL way of working) where day and month could have a value of 00 and we had to alter this to 01 as a business requirement.
declare function funcRBX:siebelDateTime ( $inputDate as xs:string?, $inputTime as xs:string?) as xs:string { if (($inputDate = "" or empty($inputDate)) and ($inputTime = "" or empty($inputTime))) (: no input :) then "" else if ($inputTime = "" or empty($inputTime)) then (: only inputDate received :) concat( if (substring(data($inputDate),5,2) = "00") then ("01") else substring(data($inputDate),5,2) (: check if day is not 00 :) ,"/",if (substring(data($inputDate),7,2) = "00") then ("01") else substring(data($inputDate),7,2) (: check if month is not 00 :) ,"/",substring(data($inputDate),1,4)) else if ($inputDate = "" or empty($inputDate)) then (: only inputTime received :) concat( substring(data($inputTime),1,2) ,":",substring(data($inputTime),3,4) ,":00") else (: both inputDate and inputTime received :) concat( if (substring(data($inputDate),5,2) = "00") then ("01") else substring(data($inputDate),5,2) (: check if day is not 00 :) ,"/",if (substring(data($inputDate),7,2) = "00") then ("01") else substring(data($inputDate),7,2) (: check if month is not 00 :) ,"/",substring(data($inputDate),1,4) ," ",substring(data($inputTime),1,2) ,":",substring(data($inputTime),3,4) ,":00") };
And then down in the XQuery mapping you can call your function like this:
{ funcRBX:siebelDateTime($someInputDate) }
Due to the the fact that the input parameters are optional (the ? character after the declaration) you can leave the second input for time out or use ” as input.