Export (0) Print
Expand All
0 out of 1 rated this helpful - Rate this topic

Data Types and XML Bulk Load Behavior

SQL Server 2000

The data types that are specified in the mapping schema (XSD or XDR type and sql:datatype) are generally ignored, except in the following cases:

In XSD:

  • If the type is dateTime or time, you must specify the sql:datatype because XML Bulk Load performs data conversion before sending the data to Microsoft® SQL Server™.

  • When you are bulk loading into a column of uniqueidentifier type in SQL Server and the XSD value is a GUID that includes braces ({ and }), you must specify sql:datatype="uniqueidentifier" to remove the braces before the value is inserted into the column. If sql:datatype is not specified, the value is sent with the braces and the insert fails.

For more information about sql:datatype, see Data Type Coercions and the sql:datatype Annotation.

In XDR:

  • If the dt:type is datetime, time, dateTime.tz, or time.tz, you must specify both the dt:type and sql:datatype data types because XML Bulk Load performs data conversion before it sends the data to SQL Server.

  • If your XML data is of type uuid, sql:datatype must be specified, and dt:type="uuid" is also required, unless the data is string data. If you do not specify dt:uuid, XML Bulk Load accepts strings with braces (and removes them if needed).

  • If the XML data is bin.base64 or bin.hex, you must specify the XML data type with dt:type. XML Bulk Load then loads the data into SQL Server as a hexadecimal representation of the data.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.