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 post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy