Hi All,
I have a SOA composite in 11.1.1.7. It contains a FTP adapter --> Mediator --> DB adapter.
The FTP adpater reads a csv which contains many records and then gets persisted into the database. The csv contains few date columns, the values comes in DD-MON-YY format eg: 01-Jan-1987.
Given XML follows ISO 8601 date format, what options do I have to convert the string 'DD-MON-YY' to date field to store in Oracle database.
I map each fields in XSL in xsl tranformation file using the design view.
<xsl:template match="/">
<top:RefEmployeesCollection>
<xsl:for-each select="/imp1:Employees/imp1:Employee">
<top:RefEmployees>
<top:id>
<xsl:value-of select="imp1:Id"/>
</top:id>
<top:firstName>
<xsl:value-of select="imp1:FirstName"/>
</top:firstName>
<top:surname>
<xsl:value-of select="imp1:Surname"/>
</top:surname>
<top:joinDate>
<xsl:value-of select="imp1:JoinDate"/>
</top:joinDate>
<top:startDate>
<xsl:value-of select="imp1:StartDate"/>
</top:startDate>
<top:startDate>
<xsl:value-of select="imp1:StartDate"/>
</top:startDate>
</top:RefEmployees>
</xsl:for-each>
</top:RefEmployeesCollection>
</xsl:template>
I go one step further and define a new template within the same xsl file
</xsl:template> <!-- User Defined Templates --> <xsl:template name="date">
<xsl:param name="dd-mmm-yy"/>
<xsl:variable name="dd" select="substring-before($dd-mmm-yy,'-')"/>
<xsl:variable name="mmm-yy" select="substring-after($dd-mmm-yy,'-')"/>
<xsl:variable name="mmm" select="substring-before($mmm-yy, '-')"/>
<xsl:variable name="yy" select="substring-after($mmm-yy, '-')"/>
<xsl:value-of select="$yy"/>
<xsl:choose>
<xsl:when test="$mmm = 'JAN'">01</xsl:when>
<xsl:when test="$mmm = 'FEB'">02</xsl:when>
<xsl:when test="$mmm = 'MAR'">03</xsl:when>
<xsl:when test="$mmm = 'APR'">04</xsl:when>
<xsl:when test="$mmm = 'MAY'">05</xsl:when>
<xsl:when test="$mmm = 'JUN'">06</xsl:when>
<xsl:when test="$mmm = 'JUL'">07</xsl:when>
<xsl:when test="$mmm = 'AUG'">08</xsl:when>
<xsl:when test="$mmm = 'SEP'">09</xsl:when>
<xsl:when test="$mmm = 'OCT'">10</xsl:when>
<xsl:when test="$mmm = 'NOV'">11</xsl:when>
<xsl:when test="$mmm = 'DEC'">12</xsl:when>
</xsl:choose>
<xsl:value-of select="$dd"/>
</xsl:template>
But how do I apply the date template to the first transformation. Also, as DD-MON-YY format is not valid date in XML, I am thinking of defining it as string datatype at source in XML.
Please suggest what is the best way to solve this problem. i.e convert DD-MMM-YY format to a valid XML date before passing it to the database.
Thanks