Data Type Mappings in Native XML Web Services
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 |
