February 3, 2010 at 7:48 am
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 (
SELECT1AS [Tag]
,0AS [Parent]
, NULLAS [dbo.Clients!1!ClientsKey]
, NULLAS [Client!2!ClientsKey]
, NULLAS [Client!2!IsMaster!Element]
, NULLAS [Client!2!ClientsName!Element]
, NULLAS [Client!2!SliceStartTime!Element]
, NULLAS [Client!2!SliceIsSoftDeleted!Element]
, NULLAS [Client!2!SliceCurrent!Element]
, NULLAS [Client!2!CreatedBy!Element]
, NULLAS [Client!2!CreatedDate!Element]
UNION ALL
SELECT2AS [Tag]
, 1AS [Parent]
, ClientsKey
, ClientsKey
, IsMaster
, ClientsName
, SliceStartTime
, SliceIsSoftDeleted
, SliceCurrent
, CreatedBy
, CreatedDate
FROMdbo.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 0 posts
You must be logged in to reply to this topic. Login to reply