Data Types and XML Bulk Load Behavior (SQLXML 4.0)
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
ortime
, you must specify thesql: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. Ifsql: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 (SQLXML 4.0).
In XDR:
If the
dt:type
isdatetime
,time
,dateTime.tz
, ortime.tz
, you must specify both thedt:type
andsql: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; dt:type="uuid" is also required, unless the data is string data. If you do not specifydt:uuid
, XML Bulk Load accepts strings with braces (and removes them if needed).If the XML data is
bin.base64
orbin.hex
, you must specify the XML data type withdt:type
. XML Bulk Load then loads the data into SQL Server as a hexadecimal representation of the data.