All I'm trying to do is simply write out a DataSet in .net 2.0 with
..WriteXml(), then read it into sql 2005 with OPENXML.
WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
Yet it appears sql 2005 doesn't support that format. Is that possible?
Running the below code gives:
"Conversion failed when converting datetime from character string."
declare @.Xml varchar(max)
declare @.iRet int
declare @.hDoc int
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>
'
exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
select SaveDate
from openxml(@.hDoc, N'/ROOT/Favorite', 2)
with Favorite
thanks-
Mike
You have a datetime value with a timezone which is not recognized with
OpenXML.
Try one of the following instead:
1. do not generate datetime values with timezones.
2. Use the nodes method (needs to explicitly code the table shape):
declare @.Xml xml
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>'
select R.Fav.value('xs:dateTime(SaveDate[1])', 'datetime') as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Note that you need to first cast it to xs:dateTime to normalize the value to
Z time and then cast it to datetime which will drop the timezone
altogether...
Season's Greetings
Michael
"Mike" <nospam@.dontemailme.com> wrote in message
news:esL2%23A4BGHA.3936@.TK2MSFTNGP12.phx.gbl...
> All I'm trying to do is simply write out a DataSet in .net 2.0 with
> .WriteXml(), then read it into sql 2005 with OPENXML.
> WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
> Yet it appears sql 2005 doesn't support that format. Is that possible?
> Running the below code gives:
> "Conversion failed when converting datetime from character string."
> --
> declare @.Xml varchar(max)
> declare @.iRet int
> declare @.hDoc int
> set @.Xml = '<ROOT>
> <Favorite>
> <Directory>\Astronomy\Aurora\</Directory>
> <Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
> <Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
> <SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
> </Favorite>
> </ROOT>
> '
> exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
> select SaveDate
> from openxml(@.hDoc, N'/ROOT/Favorite', 2)
> with Favorite
>
> thanks-
> Mike
|||Michael-
That still gave me a 'Conversion failed...' error but did get me on the
right track. Extracting as a string first then converting did the
trick.
selectconvert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
'char(20)'),127) as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Thanks much for the quick response - it really helped.
Mike
|||Hmm. What version of SQL Server 2005 are you currently running?
This should work automatically without you having to do the string/datetime
yourself in the RTM version...
Best regards
Michael
<mhardy@.gmail.com> wrote in message
news:1135818951.156103.3160@.g49g2000cwa.googlegrou ps.com...
> Michael-
> That still gave me a 'Conversion failed...' error but did get me on the
> right track. Extracting as a string first then converting did the
> trick.
> select convert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
> 'char(20)'),127) as SaveDate
> from @.Xml.nodes('/ROOT/Favorite') R(Fav)
> Thanks much for the quick response - it really helped.
> Mike
>
