OSB Xquery function DateTime conversion

17 Mar

>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 = "";

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 :)
  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 :)
 else if ($inputDate = "" or empty($inputDate)) then              (: only inputTime received :)
 else                          (: both inputDate and inputTime received :)
  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($inputTime),1,2)

And then down in the XQuery mapping you can call your function like this:


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.


Posted by on 17-03-2011 in Oracle, OSB, Siebel, XQuery


Tags: , , ,

5 responses to “OSB Xquery function DateTime conversion


    12-06-2012 at 14:12


    Thanks for the tutorial. Its very good. I do have a problem, Could you please help me…

    I do have a XQ to copy from one XML to another.

    Input XML Element:

    Output XML Element

    If any of the attribute value is empty or not exist on Input XML, then I should not add the same on output XML element.

    if Input is

    output should be

    I couldn’t write if function inside Attributes mapping.. It accepts only after attri2 = {…} etc

    Can you help me please.

    • jvzoggel

      18-06-2012 at 09:33

      Hello Sanssan, I have to test this myself. Will try to help you as soon as I have some spare time.

    • shuttle

      22-07-2012 at 15:25

      I don’t see your examples. Is that what you thinking of?

      xquery version “1.0” encoding “Cp1250”;
      declare namespace xf = “”;

      declare function xf:copy_xml_attribute_empty($element as element(*))
      as element(*) {
      element {node-name($element)}{
      if(empty($element/@*)) then
      attribute{“value”}{ “filled if attribute is empty” }
      for $attr_child in $element/attribute()
      return $attr_child
      for $child in $element/node()
      return if($child instance of element()) then xf:copy_xml_attribute_empty($child)
      else $child

      declare variable $element as element(*) external;

  2. pankaj

    23-01-2014 at 06:01

    Thanks for the helpful post.
    I have one doubt. You have mentioned the example for specific format. It means if date is coming in specific format. Suppose if date is coming in multiple format like YYYY/MM/DD or YYYY-MM-DD or MM-DD-YYYY or DD/MM/YYYY then can we convert it into MM/DD/YYYY format. It means can we create generic function which will convert any date format mentioned above into MM/DD/YYYY. If yes can you please give some information on it.

    Thanks in adavance.

  3. pankaj

    06-05-2014 at 09:09

    Can you please provide an update on above.?


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: