Search code examples
xmloraclexsltxqueryosb

OSB transform String to DateTime (xsl or xquery)

I'm working with Oracle OSB and I have the following incoming xml message:

<db:InputParameters>
    <db:DETAILS>
        <db:DETAILS_ITEM>
            <db:Mutate>W</db:Mutate>
            <db:Date>2020-04-06T14:43</db:Date>
            <db:Account>T</db:Account>
        </db:DETAILS_ITEM>
        <db:DETAILS_ITEM>
            <db:Mutate>W</db:Mutate>
            <db:Date>2020-04-06T14:43</db:Date>
            <db:Account>T</db:Account>
        </db:DETAILS_ITEM>
    </db:DETAILS>
</db:InputParameters>

The element "Date" is a "string" -> according to the xsd. But the application that I'm sending this message to, expects a "DateTime" type. So I need to transform the element "Date" from type "String" to type "DateTime". Keep in mind that the incoming message has more than one element called "Date". I tried a For Each stage with a replace action but I couldn't get it to work.

Also, I tried to concat ":00" to the "Date" element with the expression: fn:concat($body/*:inputparameters/*:DETAILS/*:DETAILS_ITEM/*:Date,':00') This didn't seem to work either.

What would be the most simple solution to this?

Thanks for the help.

Solution

  • With XSLT you can change the value of the element as follows:

    <?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:db="http://example.com/db"
    version="1.0">

  <xsl:template match="@* | node()">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="db:DETAILS_ITEM/db:Date">
      <xsl:copy>
          <xsl:value-of select="concat(., ':00')"/>
      </xsl:copy>
  </xsl:template>

</xsl:stylesheet>

    https://xsltfiddle.liberty-development.net/pNmCzsv

    Note that this is simply changing the contents of that element to a value which can be parsed as an xs:dateTime. There is no schema involved or any validation done. You will need to adapt the namespace declaration xmlns:db="http://example.com/db" to the one of the input document.