Need help with XSLT for DATES in DataSet for XMLSS

[Luther Miller]

I am using an XSLT file to convert data in a DataSet to XMLSS format
for opening in Excel. Excel doesn't like the way Dates in the DataSet
are being ouput. It appears that a timezone offset is being appended
to the datetime; e.g., instead of "2003-08-26T00:00:00.0000000" I am
getting "2003-08-26T00:00:00.0000000-07:00" in the output. If I could
just get rid of the trailing "-07:00" in the output, I would be all
set. I'm guessing this must be easy to do in XSLT, but I am far from
an XSLT expert... Thanks for any advice!

XML ERROR in Table
REASON: Bad Value
FILE: C:\Documents and Settings\lmiller.SOFTAGON\My
Documents\Work\CreedonKeller_TIPS\Excel\TestBasicR isk-TEST4.xml
GROUP: Cell
TAG: Data
VALUE: 2003-08-26T00:00:00.0000000-07:00

[SQL Server Development Team]

Such text processing isn't that easy to do in XSLT. I'd suggest using EXSLT
for MSXML or the .NET Framework which comes with a number of functions that
make text processing easier.

EXSLT info - http://www.exslt.org
EXSLT for MSXML: http://www.xml.com/pub/a/2003/08/06/exslt.html
EXSLT for .NET:
http://msdn.microsoft.com/library/en...ml05192003.asp

--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Luther Miller" <go********@hotmail.com> wrote in message
news:8c**************************@posting.google.c om...

I am using an XSLT file to convert data in a DataSet to XMLSS format
for opening in Excel. Excel doesn't like the way Dates in the DataSet
are being ouput. It appears that a timezone offset is being appended
to the datetime; e.g., instead of "2003-08-26T00:00:00.0000000" I am
getting "2003-08-26T00:00:00.0000000-07:00" in the output. If I could
just get rid of the trailing "-07:00" in the output, I would be all
set. I'm guessing this must be easy to do in XSLT, but I am far from
an XSLT expert... Thanks for any advice!

XML ERROR in Table
REASON: Bad Value
FILE: C:\Documents and Settings\lmiller.SOFTAGON\My
Documents\Work\CreedonKeller_TIPS\Excel\TestBasicR isk-TEST4.xml
GROUP: Cell
TAG: Data
VALUE: 2003-08-26T00:00:00.0000000-07:00

[Dimitre Novatchev]

"SQL Server Development Team" <sq****@microsoft.com> wrote in message
news:e5**************@TK2MSFTNGP11.phx.gbl...

Such text processing isn't that easy to do in XSLT. I'd suggest using EXSLT for MSXML or the .NET Framework which comes with a number of functions that make text processing easier.

EXSLT info - http://www.exslt.org
EXSLT for MSXML: http://www.xml.com/pub/a/2003/08/06/exslt.html
EXSLT for .NET:
http://msdn.microsoft.com/library/en...ml05192003.asp

Thanks for mentioning EXSLT for MSXML4.

EXSLT for MSXML4 does not implement the date-time functions of EXSLT. It
implements the functions from the Sets module:
intersection()
difference()
has-same-node()
distinct()
leading()
trailing()

and the node-set() function from the Common module.
In the case of MSXML I would recommend the native XSLT 1.0 datetime_lib.xsl
library, which can be downloaded as part of the trial version of Xselerator.

=====
Cheers,

Dimitre Novatchev.
http://fxsl.sourceforge.net/ -- the home of FXSL

[Luther Miller]

Well, for dates I ended up using substring to trim the end off:

<xsl:value-of select="substring(EffectiveDate,1,27)"/>

For booleans I had to convert to 1/0 using choose:
<xsl:choose><xsl:when
test="Some_Fl='true'">1</xsl:when><xsl:otherwise>0</xsl:otherwise></xsl:choose>

To handle NULLs in the data was another story. SSXML doesn't want the
cell at all if there is no data, so I ended up hardcoding the INDEX
(column number) property into every cell and then dynamically
determining if the cell should be output at all:

<xsl:if test="SomeNullableColumn"><Cell ss:Index="6"
ss:StyleID="s32"><Data ss:Type="Number"><xsl:value-of
select="SomeNullableColumn"/></Data></Cell></xsl:if>

The other thing I had to do was to set the Namespace property of the
DataSet to an empty string. Because it is a typed-dataset, the
generated code was setting the namespace and the XSLT transform was
not recognizing the dataset and table names. I probably could have
found another way to handle this, but this seemed to be the simplest
in order to work from other examples using (non-typed) DataSets...

fun...