Re: [xsl] DateTime Conversion question

["G. Ken Holman" <gkholman@xxxxxxxxxxxxxxxxxxxx>]

At 2010-10-06 23:50 +0100, Neil Owens wrote:
> I'm trying to convert a (UK) date format into a SQL DateTime so I can
> ingest the resultant transform into SQL2008 with SSIS
>
> The (simplified) source XML is:
> <Log>
> <outputCommand Command="sendData" Time="06/10/2010 22:22:01">
> </outputCommand>
> </Log>
>
> I can get as far as  transforming thus:
>
> .....
> <xsl:template match=:"outputCommand">
> <outputCommand>
> <Command>xsl:value-of select="@Command"></Command>
> <Time>xsl:value-of select="@Time"></Time
> </outputCommand>
> </xsl:template>
>
> But I admit defeat in trying to convert the time value to a SQL
> datetime (i.e. 20101006T22:22:01)

That looks suspiciously like ISO 8601 date format:

   2010-10-06T22:22:01+01:00

> I'm happy to use XSLT 1.0 or 2.0, however, everything I've read
> today says that .NET2008 only supports XSLT 1.0, as I'm using
> System.Xml.Xsl.XslCompiledTransform to 'do' the transformation
>
> Any help or a link appreciated

Repetitive use of substring() would work in XSLT 1:

  <xsl:value-of select="concat(
substring(@Time,7,4),'-',substring(@Time,4,2),'-',substring(@Time,1,2),'T',
substring(@Time,12,8),'+01:00' )"/>

If you want to reduce the typing a bit:

 <xsl:for-each select="@Time">
  <xsl:value-of select="concat(
substring(.,7,4),'-',substring(.,4,2),'-',substring(.,1,2),'T',
substring(.,12,8),'+01:00' )"/>
 </xsl:for-each>

In XSLT 2 I might use regex and groups to pull apart the string.

I hope this helps.

. . . . . . . . Ken


--
XSLT/XQuery training:   after http://XMLPrague.cz 2011-03-28/04-01
Vote for your XML training:   http://www.CraneSoftwrights.com/s/i/
Crane Softwrights Ltd.          http://www.CraneSoftwrights.com/s/
G. Ken Holman                 mailto:gkholman@xxxxxxxxxxxxxxxxxxxx
Male Cancer Awareness Nov'07  http://www.CraneSoftwrights.com/s/bc
Legal business disclaimers:  http://www.CraneSoftwrights.com/legal