declare @xl varchar(8000) select @xl= '<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <VFPData> <tmp> <dt>2005-01-24T13:25</dt> </tmp> <tmp> <dt>2005-01-24T13:25</dt> </tmp> <tmp> <dt>2005-01-24T13:26</dt> </tmp> <tmp> <dt>2005-01-24T13:35</dt> </tmp> <tmp> <dt>2005-03-05T00:00</dt> </tmp> <tmp> <dt>2005-02-03T00:00</dt> </tmp> <tmp> <dt>2005-01-25T00:00</dt> </tmp> </VFPData> ' --Note that the XML script was generated with cursortoxml() in VFP9. exec ReadXML @xlSP - ReadXML:
CREATE PROCEDURE ReadXML ( @xml Varchar(8000) ) as declare @xdoc int exec sp_xml_preparedocument @xdoc output, @xml select t.* from openxml (@xdoc, '/VFPData/tmp', 2) with ( dt datetime) as t exec sp_xml_removedocument @xdoc GOError message raised in SQL Query Analyzer:
Server: Msg 241, Level 16, State 1, Procedure ReadXML, Line 8 Syntax error converting datetime from character string.