XML xsd:dateTime not correctly importing with SQLXMLBulkLoad

  • Mario Garcia-415929

    SSC-Addicted

    Points: 404

    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

Viewing post 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply