sp_xml_preparedocument - Date problem

I'm no word-smith, so here was my problem:

My dataset looked like the following when it's serialized.

   1:  <NewDataSet>

   2:    <xs:schema id="NewDataSet" xmlns=""

   3:  xmlns:xs="http://www.w3.org/2001/XMLSchema"

   4:  xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

   5:      <xs:element name="NewDataSet" msdata:IsDataSet="true"

   6:  msdata:UseCurrentLocale="true">

   7:        <xs:complexType>

   8:          <xs:choice minOccurs="0" maxOccurs="unbounded">

   9:            <xs:element name="List_101">

  10:              <xs:complexType>

  11:                <xs:attribute name="InvoiceID" type="xs:int" />

  12:                <xs:attribute name="Email" type="xs:string" />

  13:                <xs:attribute name="DomainID" type="xs:int" />

  14:                <xs:attribute name="SourceID" type="xs:int" />

  15:                <xs:attribute name="TypeEnum" type="xs:int" />

  16:                <xs:attribute name="ImportID" type="xs:int" />

  17:                <xs:attribute name="DateStamp" type="xs:dateTime" />

  18:              </xs:complexType>

  19:            </xs:element>

  20:          </xs:choice>

  21:        </xs:complexType>

  22:      </xs:element>

  23:    </xs:schema>

  24:    <List_101 InvoiceID="55" Email="joeshmoe@joe.com" DomainID="2421"

  25:  SourceID="12" TypeEnum="2" ImportID="20"

  26:  DateStamp="2005-09-18T17:39:00-07:00" />

  27:  </NewDataSet>

My stored procedure looked like the following:

   1:  CREATE Proc xml_List_101

   2:  @listdata nText

   3:  AS

   4:  Declare @hDoc int

   5:  exec sp_xml_preparedocument @hDoc OUTPUT, @listdata

   6:  Insert Into List_101

   7:  Select 

   8:   [InvoiceID], [Email], [DomainID], [SourceID], [TypeEnum], [ImportID], [DateStamp]

   9:  From 

  10:  OPENXML(@hDoc, '/NewDataSet/List_101')

  11:  WITH (

  12:  [InvoiceID] Integer,[Email] varchar(150),[DomainID] Integer,[SourceID] Integer,[TypeEnum] Integer,[ImportID] Integer,[DateStamp] DateTime)

  13:  Exec sp_xml_removedocument @hDoc

First off, my dataset was a lot larger then the one I am showing you. There were many more "rows".

I was trying to insert the XML data into a table called List_101, and that table has a 'DateStamp' column that is a datetime type. I kept getting the "Syntax error converting datetime from character string." because of the DateTime format from DataSet.WriteXML(). (because of the -07:00 offset)

So I ended up using the CAST(left(DateStamp,10) AS datetime) so it was in the correct format and changed the WITH to DateStamp varchar(10). The sp_xml_preparedocument doesn't blow up anymore and the data gets successfuly inserted into List_101 with the proper DateStamp (which is a datetime in the table) because it converts the varchar(10) on its own.

Final working stored proc:

   1:  CREATE Proc xml_List_101

   2:  @listdata nText

   3:  AS

   4:   

   5:  Declare @hDoc int

   6:  exec sp_xml_preparedocument @hDoc OUTPUT, @listdata

   7:   

   8:  Insert Into List_101

   9:  Select 

  10:   [EmailID], [Email], [DomainID], [SourceID], [TypeEnum], [ImportID], CAST(LEFT(DateStamp],10) AS datetime)From 

  11:  OPENXML(@hDoc, '/NewDataSet/List_101')

  12:  WITH (

  13:  [EmailID] Integer,[Email] varchar(150),[DomainID] Integer,[SourceID] Integer,[TypeEnum] Integer,[ImportID] Integer,[DateStamp] varchar(10)) 

  14:  Exec sp_xml_removedocument @hDoc

Hopefully this helps the next person.