I Need A Dip

Friday, January 27, 2006

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
   5:  Declare @hDoc int
   6:  exec sp_xml_preparedocument @hDoc OUTPUT, @listdata
   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.


  • but what about when you actually require the date and time?

    By Blogger Mohd Ali, at 9:50 PM  

Post a Comment

<< Home