2.2.5.2 sqlTypes

  • Article

The following set of simple types defines SQL Server data types in terms of XML data types. These types are defined under the "http://schemas.microsoft.com/sqlserver/2004/sqltypes" namespace, typically referred to using the sqltypes prefix.

 <xsd:simpleType name="char">
   <xsd:restriction base="xsd:string"/>
 </xsd:simpleType>
 <xsd:simpleType name="nchar">
   <xsd:restriction base="xsd:string"/>
 </xsd:simpleType>
 <xsd:simpleType name="varchar">
   <xsd:restriction base="xsd:string"/>
 </xsd:simpleType>
 <xsd:simpleType name="nvarchar">
   <xsd:restriction base="xsd:string"/>
 </xsd:simpleType>
 <xsd:simpleType name="text">
   <xsd:restriction base="xsd:string"/>
 </xsd:simpleType>
 <xsd:simpleType name="ntext">
   <xsd:restriction base="xsd:string"/>
 </xsd:simpleType>
 <xsd:simpleType name="varbinary">
   <xsd:restriction base="xsd:base64Binary"/>
 </xsd:simpleType>
 <xsd:simpleType name="binary">
   <xsd:restriction base="xsd:base64Binary"/>
 </xsd:simpleType>
 <xsd:simpleType name="image">
   <xsd:restriction base="xsd:base64Binary"/>
 </xsd:simpleType>
 <xsd:simpleType name="timestamp">
   <xsd:restriction base="xsd:base64Binary">
     <xsd:maxLength value="8"/>
   </xsd:restriction>
 </xsd:simpleType>
 <xsd:simpleType name="timestampNumeric">
   <!-- The timestampNumeric type supports a 
 legacy format of timestamp. -->
   <xsd:restriction base="xsd:long"/>
 </xsd:simpleType>
 <xsd:simpleType name="decimal">
   <xsd:restriction base="xsd:decimal"/>
 </xsd:simpleType>
 <xsd:simpleType name="numeric">
   <xsd:restriction base="xsd:decimal"/>
 </xsd:simpleType>
 <xsd:simpleType name="bigint">
   <xsd:restriction base="xsd:long" />
 </xsd:simpleType>
 <xsd:simpleType name="int">
   <xsd:restriction base="xsd:int"/>
 </xsd:simpleType>
 <xsd:simpleType name="smallint">
   <xsd:restriction base="xsd:short"/>
 </xsd:simpleType>
 <xsd:simpleType name="tinyint">
   <xsd:restriction base="xsd:unsignedByte"/>
 </xsd:simpleType>
 <xsd:simpleType name="bit">
   <xsd:restriction base="xsd:boolean"/>
 </xsd:simpleType>
 <xsd:simpleType name="float">
   <xsd:restriction base="xsd:double"/>
 </xsd:simpleType>
 <xsd:simpleType name="real">
   <xsd:restriction base="xsd:float"/>
 </xsd:simpleType>
 <xsd:simpleType name="datetime">
   <xsd:restriction base="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[012]))-((0[1-
 9])|([12][0-9])|(3[01]))T(([01][0-9])|(2[0-3]))(:[0-5][0-
 9]){2}(\.[0-9]{2}[037])?"/>
     <xsd:maxInclusive value="9999-12-31T23:59:59.997"/>
     <xsd:minInclusive value="1753-01-01T00:00:00.000"/>
   </xsd:restriction>
 </xsd:simpleType>
 <xsd:simpleType name="smalldatetime">
   <xsd:restriction base="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[012]))-((0[1-
 9])|([12][0-9])|(3[01]))T(([01][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"/>
   </xsd:restriction>
 </xsd:simpleType>
 <xsd:simpleType name="money">
   <xsd:restriction base="xsd:decimal">
     <xsd:totalDigits value="19"/>
     <xsd:fractionDigits value="4"/>
     <xsd:maxInclusive value="922337203685477.5807"/>
     <xsd:minInclusive value="-922337203685477.5808"/>
   </xsd:restriction>
 </xsd:simpleType>
 <xsd:simpleType name="smallmoney">
   <xsd:restriction base="xsd:decimal">
     <xsd:totalDigits value="10"/>
     <xsd:fractionDigits value="4"/>
     <xsd:maxInclusive value="214748.3647"/>
     <xsd:minInclusive value="-214748.3648"/>
   </xsd:restriction>
 </xsd:simpleType>
 <xsd:simpleType name="uniqueidentifier">
   <xsd:restriction base="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}\})"/>
   </xsd:restriction>
 </xsd:simpleType>
 <!-- sql_variant directly maps to xsd:anyType -->
 <xsd:complexType name="xml" mixed="true">
   <xsd:sequence>
     <xsd:any minOccurs="0" maxOccurs="unbounded" 
 processContents="skip" />
   </xsd:sequence>
 </xsd:complexType>
 <!-- the following type is for FOR XML binary URL 
 results only -->
 <xsd:simpleType name="dbobject">
   <xsd:restriction base="xsd:anyURI" />
 </xsd:simpleType>
           

The SQL Server equivalent data types are listed in the following table.

Value

Meaning

sqltypes:char

SQL Server char data type.

sqltypes:nchar

SQL Server nchar data type.

sqltypes:varchar

SQL Server varchar data type.

sqltypes:nvarchar

SQL Server nvarchar data type.

sqltypes:text

SQL Server text data type.

sqltypes:ntext

SQL Server ntext data type.

sqltypes:varbinary

SQL Server varbinary data type.

sqltypes:binary

SQL Server binary data type.

sqltypes:image

SQL Server image data type.

sqltypes:timestamp

SQL Server timestamp data type.

sqltypes:decimal

SQL Server decimal data type.

sqltypes:numeric

SQL Server numeric data type.

sqltypes:bigint

SQL Server bigint data type.

sqltypes:int

SQL Server int data type.

sqltypes:smallint

SQL Server smallint data type.

sqltypes:tinyint

SQL Server tinyint data type.

sqltypes:bit

SQL Server bit data type.

sqltypes:float

SQL Server float data type.

sqltypes:real

SQL Server real data type.

sqltypes:datetime

SQL Server datetime data type.

sqltypes:smalldatetime

SQL Server smalldatetime data type.

sqltypes:money

SQL Server money data type.

sqltypes:smallmoney

SQL Server smallmoney data type.

sqltypes:uniqueidentifier

SQL Server uniqueidentifier data type.

sqltypes:xml

SQL Server xml data type.

xsd:any

SQL Server CLR UDT data type.

In this table, the CLR UDT data type is mapped to XML schema of xsd:any. This protocol itself does not define the XML structure of a user-defined type value; it only defines the mechanism by which a user-defined type value can be exchanged. It is the user-defined type creator’s responsibility to define the XML representation for the type and to provide the XML schema of the type to the client application. The transfer of the XML schema between the user-defined type creator and the client application is done out of band. For additional information about using CLR's XML serialization attributes to control the XML serialization format, refer to [MSDN-XMLSNET].