Data Type Mappings in Native XML Web Services

Article
2008-12-03

The following table shows the mapping of SQL types to XSD types.

SQL type	XSD type	Type restrictions
BigInt	xsd:long
Binary	xsd:base64Binary
Bit	xsd:boolean
Char	xsd:string
DateTime	xsd:dateTime	<xsd:pattern value="((000[1-9])\|(00[1-9][0-9])\|(0[1-9][0-9]{2})\|([1-9][0-9]{3}))-((0[1-9])\|(1[0,1,2]))-((0[1-9])\|([1,2][0-9])\|(3[0,1]))T(([0,1][0-9])\|(2[0-3]))(:[0-5][0-9]){2}(\.[0-9]{2}[0,3,7])?" /> <xsd:maxInclusive value="9999-12-31T23:59:59.997"/> <xsd:minInclusive value="1753-01-01T00:00:00.000"/>
Decimal	xsd:decimal
Float(53)	xsd:double
GUID (same as UniqueIndentifier)	xsd:string	<xsd:pattern value="([0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12})\|(\{[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}\})" />
Image	xsd:base64Binary
Int	xsd:int
Money	xsd:decimal	<xsd:totalDigits value="19"/> <xsd:fractionDigits value="4"/> <xsd:maxInclusive value="922337203685477.5807"/> <xsd:minInclusive value="-922337203685477.5808"/>
NChar	xsd:string
NText	xsd:string
Numeric	xsd:decimal
NVarChar	xsd:string
Real	xsd:float
SmallInt	xsd:short
SmallDateTime	xsd:dateTime	<xsd:pattern value="((000[1-9])\|(00[1-9][0-9])\|(0[1-9][0-9]{2})\|([1-9][0-9]{3}))-((0[1-9])\|(1[0,1,2]))-((0[1-9])\|([1,2][0-9])\|(3[0,1]))T(([0,1][0-9])\|(2[0-3]))(:[0-5][0-9])(:00)" /> <xsd:maxInclusive value="2079-06-06T23:59:00"/><xsd:minInclusive value="1900-01-01T00:00:00"/>
SmallMoney	xsd:decimal	<xsd:totalDigits value="10"/> <xsd:fractionDigits value="4"/> <xsd:maxInclusive value="214748.3647"/><xsd:minInclusive value="-214748.3648"/>
Sql_Variant	xsd:anyType
Text	xsd:string
TimeStamp	xsd:base64Binary (in SQL Server 2005 compatibility mode); xsd:long (in SQL Server compatibility mode)	<xsd:maxLength>8</xsd:maxLength>
TinyInt	xsd:unsignedByte
UDT (CLR style)	xsd:base64Binary (in SQL Server 2005)	Additional information about the actual type is provided within facets.
UDT (old style)	Mapped to original base type
VarBinary	xsd:base64Binary
VarChar	xsd:string
XML	xsd:any
XML (typed)	xsd:any	XML schema that the XML is typed to is returned.

When you send ad hoc query requests, you can specify parameters. In specifying parameters, you can also optionally add parameter facets. These facets can be specified on the <SqlParameter> element node or <Value> element node when you send an ad hoc query request. For information about parameter facets, see SOAP Request Message Structure.

The following table shows how xsi:type maps to SQL types. Note that xsi:type is one of the facets that you can add to the <Value> element node in constructing the SOAP request.

xsi:type	Sql Type
sqltypes:bigint	BigInt
sqltypes:binary	Binary
sqltypes:bit	Bit
sqltypes:char	Char
sqltypes:datetime	DateTime
sqltypes:decimal	Decimal
sqltypes:float	Float
sqltypes:image	Image
sqltypes:int	Int
sqltypes:money	Money
sqltypes:nchar	NChar
sqltypes:ntext	NText
sqltypes:numeric	Numeric
sqltypes:nvarchar	NVarChar
sqltypes:real	Real
sqltypes:smalldatetime	SmallDateTime
sqltypes:smallint	SmallInt
sqltypes:smallmoney	SmallMoney
sqltypes:text	Text
sqltypes:timestamp	Timestamp
sqltypes:tinyint	TinyInt
sqltypes:uniqueidentifier	UniqueIdentifier
sqltypes:varbinary	VarBinary
sqltypes:varchar	VarChar
sqltypes:xml	Xml
xsd:anyType	Xml
xsd:boolean	Bit
xsd:datetime	DateTime
xsd:decimal	Decimal
xsd:double	Float
xsd:float	Real
xsd:int	Int
xsd:long	BigInt
xsd:short	SmallInt
xsd:string	NVarchar
xsd:unsignedByte	TinyInt
xsd:base64Binary	varbinary

The following table lists the possible values for the SqlDbType facet that you can add to the <SqlParameter> element node when you construct an ad hoc query request.

BigInt	Real
Binary	SmallDateTime
Bit	SmallInt
Char	SmallMoney
DateTime	Text
Decimal	Timestamp
Float	TinyInt
Image	Udt
Int	UniqueIdentifier
Money	VarBinary
NChar	VarChar
NText	Variant
NVarChar	Xml

Share via

Data Type Mappings in Native XML Web Services

Data Type Issues Specific to Parameter Facets in a SOAP Request

See Also

Other Resources

Help and Information

Additional resources