Converting SAP IDoc XML date and time to a database date value

In one of my last blog entries, I descriped how to convert a xsd:string date and time value from SAP IDoc into the more appropriate xsd:dateTime value. While for most cases, specifying the required date and time parameters of xsd:dateTime is sufficient, it might not for database writes. Databases often require the precision of the optional milliseconds and especially timezone parameters of xsd:dateTime when writing the a DATE field.

The following POC will show how to extend the XSL Transformation to add entirely specify all parameters of xsd:dateTime. The POC is realizing a database adapter service to write a new empoyee to the well-known HR schema. Therefore, a BPEL process is exposing a SOAP-based web service endpoint to provide input data in a SAP IDoc like style ... hence all data are defined as xsd:string, no matter their contents' meaning.

SCA composite of the POC

The input data require firstname, lastname and hire date and time. The web service returns the hire date formated as xsd:dateTime after successful insert to the database.

XML Schema Definition of input and output data

The BPEL process itselfs stores the received input to an input variable. The following XSL transformation we focus on maps the input data to the database write variable. After successful write to the database by invocation of the endpoint reference of the database adapter, the returned data are mapped to the output data of the process.

BPEL Process "AddEmployee"

Let's have a detailed look at the XSL transformation that is "building" our xsd:dateTime datatype. The graphical representation already shows the usage of the xp20:implicit-timezone() function from XPath 2.0 (green icon).

XSL transformation to "build" an xsd:dateTime value

While the date and time concatination is known from my previous poc, we will focus on the milliseconds and timezone parameters. The milliseconds are simply set to zero (.000) while we make use of the service engines' timezone setting to specify the timezone. In the end, we end up with a pattern of yyyy-mm-ddThh:mm:ss.s+zzzzzz instead of the less precise pattern yyyy-mm-ddThh:ss:mm from the previous POC. A detailed description of the pattern can be found in the W3C recommendation of XSD.

<top:hireDate>
  <xsl:value-of select='concat(
    substring(/client:request/client:hireDay,1.0,4.0),"-",
    substring(/client:request/client:hireDay,5.0,2.0),"-",
    substring(/client:request/client:hireDay,7.0,2.0),"T",
    substring(/client:request/client:hireTime,1.0,2.0),":",
    substring(/client:request/client:hireTime,3.0,2.0),":",
    substring(/client:request/client:hireTime,5.0,2.0),".000",
    xp20:implicit-timezone())'/>
</top:hireDate>

Testing the XSL transformation in the XSLT tester in JDeveloper, everything is working as expected.

Local testing in JDevelopers' XSLT tester

Testing the POC in Oracle Enterprise Manager shows everything working as expected.

Testing the POC in Oracle Enterprise Manager with sample data

An xsd:dateTime value with full precision is returned

References:

• XML Schema Definition Language (XSD) 1.1 Part 2: Datatypes
• Oracle HR data schema for relational databases
• Download the POC

Node selection of Oracle's XSLT engine

Oracle Busisness Process Management Suite and Oracle SOA Suite enable XML Stylesheet Transformations (XSLT). The XSLT engine is part of the mediator component engine, which is indeed the former enterprise service bus (ESB) implementation of Oracle before Oracle Service Bus (OSB) was bought.
For implementation purpose, it is sometimes useful to know exactly the node selection behavior of the XSLT engine. In most cases, XSLT relies on XML Path Language (XPath) node selection mechanisms. But what happens, if the expression would match more then one node? Well, let's try!

The POC is quiet simple. A synchornous BPEL process exposing a SOAP web service interface accepts a list of animals as request.

SCA Composite of the POC

For the response, a list of the first, last and XPath node-selected animal will be returned.

XML Schema definition of request and response of the SOAP web service endpoint

The BPEL process itself is quiet simple: Received input data from the input variable are transformed to the output variable to be returned to the requester.

BPEL Process "SortMyAnimals"

The XSL Transformation uses XPath expression to map the first animal of the input list (/request/animals/animal[1]), the last animal of the input list (/request/animals/animal[last()]) and the animal that is selected by the XSLT engine we are actually interested in (/request/animals/animal) ... since we did not define its position in the list.

  <xsl:template match="/">
    <ns1:response>
      <ns1:firstanimal>
        <xsl:value-of select="/ns1:request/ns1:animals/ns1:animal[1]"/>
      </ns1:firstanimal>
      <ns1:lastanimal>
        <xsl:value-of select="/ns1:request/ns1:animals/ns1:animal[last()]"/>
      </ns1:lastanimal>
      <ns1:nodeselectedanimal>
        <xsl:value-of select="/ns1:request/ns1:animals/ns1:animal"/>
      </ns1:nodeselectedanimal>
    </ns1:response>
  </xsl:template>

For a sample input of three animals cat, dog and cow (in that order), the XSLT tester in JDeveloper returns the cat, that means the first element that is matching the XPath expression.

Local testing in JDeveloper using XSLT tester

After deployment of the POC to our environment, we will run the same example to proof what we found out.

Testing the POC in Oracle Enterprise Manager

 And yes, the result is the same:

Testing result in Oracle Enterprise Manager

Summery: Not specifying the exact node in an XPath expression in an XSL Stylesheet Transformation will return the first node matching the path criteria.

References:

• Oracle SOA Suite on Oracle Technology Network
• XSL Transformations (XSLT) Version 1.0: http://www.w3.org/TR/xslt/
• XML Path Language (XPath) Version 1.0: http://www.w3.org/TR/xpath
• Download this POC