XML xsd:dateTime not correctly importing with SQLXMLBulkLoad

• Mario Garcia-415929

  SSC-Addicted

  Points: 404

  February 3, 2010 at 7:48 am

  #215224

  I am exporting and importing data using SQLXMLBulkLoad. All of the datatypes except dateTime are loading correctly.

  Here is the routine to export the data

  DECLARE @xml XML

  SET @xml = (

  SELECT [Tag]

  , [Parent]

  , [dbo.Clients!1!ClientsKey]

  , [Client!2!IsMaster!Element]

  , [Client!2!ClientsName!Element]

  , [Client!2!SliceStartTime!Element]

  , [Client!2!SliceIsSoftDeleted!Element]

  , [Client!2!SliceCurrent!Element]

  , [Client!2!CreatedBy!Element]

  , [Client!2!CreatedDate!Element]

  FROM (

  SELECT 1 AS [Tag]

  , 0 AS [Parent]

  , NULL AS [dbo.Clients!1!ClientsKey]

  , NULL AS [Client!2!ClientsKey]

  , NULL AS [Client!2!IsMaster!Element]

  , NULL AS [Client!2!ClientsName!Element]

  , NULL AS [Client!2!SliceStartTime!Element]

  , NULL AS [Client!2!SliceIsSoftDeleted!Element]

  , NULL AS [Client!2!SliceCurrent!Element]

  , NULL AS [Client!2!CreatedBy!Element]

  , NULL AS [Client!2!CreatedDate!Element]

  UNION ALL

  SELECT 2 AS [Tag]

  , 1 AS [Parent]

  , ClientsKey

  , ClientsKey

  , IsMaster

  , ClientsName

  , SliceStartTime

  , SliceIsSoftDeleted

  , SliceCurrent

  , CreatedBy

  , CreatedDate

  FROM dbo.Clients

  ) AS X

  ORDER BY [dbo.Clients!1!ClientsKey]

  FOR XML EXPLICIT

  )

  SELECT @XML AS 'dbo.Clients'

  Here is the XML data. Notice the <CreatedDate> element. The format is CCYY-MM-DDThh:mm:ss. Is this the proper format? I found this web site for assistance.

  http://www.w3schools.com/schema/schema_dtypes_date.asp

  <dbo.Clients>

  <Client ClientsKey="23">

  <IsMaster>1</IsMaster>

  <ClientsName>Master</ClientsName>

  <SliceStartTime>1900-01-01T00:00:00</SliceStartTime>

  <SliceIsSoftDeleted>0</SliceIsSoftDeleted>

  <SliceCurrent>23</SliceCurrent>

  <CreatedBy>Default</CreatedBy>

  <CreatedDate>1900-01-01T00:00:00</CreatedDate>

  </Client>

  </dbo.Clients>

  Here is the xsd.

  <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

  xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

  <xsd:element name="Client" sql:relation="zzz_Clients" >

  <xsd:complexType>

  <xsd:sequence>

  <xsd:element name="IsMaster" type="xsd:integer" />

  <xsd:element name="ClientsName" type="xsd:string" />

  <xsd:element name="SliceStartTime" type="xsd:dateTime" />

  <xsd:element name="SliceIsSoftDeleted" type="xsd:byte" />

  <xsd:element name="SliceCurrent" type="xsd:integer" />

  <xsd:element name="CreatedBy" type="xsd:string" />

  <xsd:element name="CreatedDate" type="xsd:dateTime" />

  </xsd:sequence>

  <xsd:attribute name="ClientsKey" type="xsd:integer" />

  </xsd:complexType>

  </xsd:element>

  </xsd:schema>

  When I run the load routine...

  set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")

  objBL.ConnectionString = "provider=SQLOLEDB.1;data source=myDataSource;database=myDatabase;trusted_connection=yes"

  objBL.ErrorLogFile = "P:\XML\ERROR.XML"

  objBL.Execute "P:\XML\dbo.Clients.xsd", "P:\XML\dbo.ClientsData.xml"

  set objBL = Nothing

  I get the following error.

  <?xml version="1.0"?>

  <Error>

  <Record>

  <HResult>0x80004005</HResult>

  <SQLState>22018</SQLState>

  <NativeError>0</NativeError>

  <Source>Microsoft OLE DB Provider for SQL Server</Source>

  <Description><![CDATA[Invalid character value for cast specification.]]></Description>

  </Record>

  <Record>

  <HResult>0x80004005</HResult>

  <SQLState>22018</SQLState>

  <NativeError>0</NativeError>

  <Source>Microsoft OLE DB Provider for SQL Server</Source>

  <Description><![CDATA[Invalid character value for cast specification.]]></Description>

  </Record>

  </Error>

  When I remove the "T" from the two date fields, the data loads correctly.

  My question is, why does the "T" break the SQLXMLBulkLoad or is it something I am doing wrong?

  Thanks