Data Type Mappings

---

This section describes how database data types are mapped to XML schema data types for supported databases:

DB2
Informix
MySQL
Oracle
PostgreSQL
Microsoft SQL Server
Sybase

DB2

Table E-1 describes how DB2 data types are mapped to XML schema data types for DataDirect XQuery. Any DB2 data type that is not listed is not supported by DataDirect XQuery.

Table E-1. DB2 Data Types  

DB2 Data Type	XML Schema Data Type
Bigint 1	xs:long
Binary 2	xs:hexBinary
Blob	xs:hexBinary
Char	xs:string
Char for Bit Data	xs:hexBinary
Clob	xs:string
Date	xs:date
DB2XML.XMLClob	xs:anyType
DB2XML.XMLFile	xs:anyType
DB2XML.XMLVarchar	xs:anyType
DBClob	xs:string
Decfloat 3	xs:decimal
Decimal	xs:decimal
Double	xs:double
Double Precision	xs:double
Float	xs:double
Float(n), n > 24	xs:double
Integer	xs:int
Long Varchar for Bit Data	xs:hexBinary
Long Vargraphic	xs:string
Numeric	xs:decimal
Real	xs:float
Rowid 4	xs:string
Smallint	xs:short
Time	xs:time
Timestamp	xs:dateTime
Varbinary 2	xs:hexBinary
Varchar	xs:string
Varchar for Bit Data	xs:hexBinary
Xml 5	xs:anyType

1 Supported for DB2 for Linux, UNIX, and Windows v8.x and v9.x and for DB2 for z/OS v9.x only. 2 Supported for DB2 v9.x for z/OS only. 3 Supported for DB2 for Linux, UNIX, and Windows v9.x and for DB2 for z/OS v9.x only. 4 Supported for DB2 for z/OS and DB2 for iSeries only. 5 Supported for DB2 for Linux, UNIX, and Windows v9.x only.

Informix

Table E-2 describes how Informix data types are mapped to XML schema data types for DataDirect XQuery. Any Informix data type that is not listed is not supported by DataDirect XQuery.

Table E-2. Informix Data Types

Informix Data Type	XML Schema Data Type
BLOB	xs:hexBinary
BOOLEAN	xs:boolean
BYTE	xs:hexBinary
CHAR	xs:string
CLOB	xs:string
DATE	xs:date
DATETIME HOUR TO SECOND	xs:time
DATETIME YEAR TO DAY	xs:date
DATETIME YEAR TO FRACTION(5)	xs:dateTime
DATETIME YEAR TO SECOND	xs:dateTime
DECIMAL	xs:decimal
FLOAT	xs:double
INT8	xs:long
INTEGER	xs:int
LVARCHAR	xs:string
MONEY	xs:decimal
NCHAR	xs:string
NVARCHAR	xs:string
SERIAL	xs:int
SERIAL8	xs:long
SMALLFLOAT	xs:float
SMALLINT	xs:short
TEXT	xs:string
VARCHAR	xs:string

MySQL

Table E-3 describes how MySQL data types are mapped to XML schema data types for DataDirect XQuery. Any MySQL data type that is not listed is not supported by DataDirect XQuery.

Table E-3. MySQL Enterprise Data Types

MySQL Enterprise Data Type	XML Schema Data Type
BIGINT	xs:long
BIGINT UNSIGNED	xs:decimal
BINARY	xs:hexBinary
BIT	xs:hexBinary
BLOB	xs:hexBinary
BOOLEAN	xs:boolean
DATE	xs:date
DATETIME	xs:dateTime
DECIMAL	xs:decimal
DECIMAL UNSIGNED	xs:decimal
DOUBLE	xs:double
DOUBLE UNSIGNED	xs:double
FLOAT	xs:float
FLOAT UNSIGNED	xs:float
INTEGER	xs:int
INTEGER UNSIGNED	xs:long
LONGBLOB	xs:hexBinary
LONGTEXT	xs:string
MEDIUMBLOB	xs:hexBinary
MEDIUMINT	xs:int
MEDIUMINT UNSIGNED	xs:int
MEDIUMTEXT	xs:string
SMALLINT	xs:short
SMALLINT UNSIGNED	xs:int
TEXT	xs:string
TIME	xs:time
TIMESTAMP	xs:dateTime
TINYBLOB	xs:hexBinary
TINYINT	xs:short
TINYINT UNSIGNED	xs:short
TINYTEXT	xs:string
VARBINARY	xs:hexBinary
VARCHAR	xs:string
YEAR	xs:short

Oracle

Table E-4 describes how Oracle data types are mapped to XML schema data types for DataDirect XQuery. Any Oracle data type that is not listed is not supported by DataDirect XQuery.

Table E-4. Oracle Data Types 

Oracle Data Type	XML Schema Data Type
BFILE	xs:hexBinary
BINARY_FLOAT	xs:float
BINARY_DOUBLE	xs:double
BLOB	xs:hexBinary
CHAR	xs:string
CLOB	xs:string
DATE	xs:dateTime
FLOAT(n)	xs:double
LONG	xs:string
LONG RAW	xs:hexBinary
NCHAR	xs:string
NCLOB	xs:string
NUMBER (p, s)	xs:decimal
NUMERIC	xs:decimal
NVARCHAR2	xs:string
RAW	xs:hexBinary
REAL	xs:double
ROWID	xs:string
SMALLINT	xs:decimal
TIMESTAMP	xs:dateTime
TIMESTAMP WITH LOCAL TIME ZONE	xs:dateTime
TIMESTAMP WITH TIME ZONE	xs:dateTime
UROWID	xs:string
VARCHAR2	xs:string
XMLTYPE1	xs:anyType

1 Supported for Oracle 10gR2 and 11gR1 only.

PostgreSQL

Table E-5 describes how PostgreSQL data types are mapped to XML schema data types for DataDirect XQuery. Any PostgreSQL data type that is not listed is not supported by DataDirect XQuery.

Table E-5. PostgreSQL Data Types 

PostgreSQL Data Type	XML Schema Data Type
bigserial	xs:long
bit	xs:boolean
bool	xs:boolean
bytea	xs:hexBinary
char	xs:string
date	xs:date
float4	xs:double
float8	xs:float
int2	xs:short
int4	xs:int
int8	xs:long
money	xs:float
name	xs:string
numeric	xs:decimal
oid	xs:int
serial	xs:int
text	xs:string
time	xs:time
timestamp	xs:dateTime
timestamp with time zone	xs:dateTime
time with time zone	xs:time
varchar	xs:string

Microsoft SQL Server

Table E-6 describes how Microsoft SQL Server data types are mapped to XML schema data types for DataDirect XQuery. Any Microsoft SQL Server data type that is not listed is not supported by DataDirect XQuery.

Table E-6. Microsoft SQL Server Data Types 

Microsoft SQL Server Data Type	XML Schema Data Type
bigint	xs:long
bigint identity	xs:long
binary	xs:hexBinary
bit	xs:boolean
char	xs:string
datetime	xs:dateTime
decimal	xs:decimal
decimal() identity	xs:decimal
double	xs:double
float	xs:double
image	xs:hexBinary
int	xs:int
int identity	xs:int
money	xs:decimal
nchar	xs:string
ntext	xs:string
numeric	xs:decimal
numeric() identity	xs:decimal
nvarchar	xs:string
real	xs:float
rowversion	xs:hexBinary
sql_variant	xs:string
smalldatetime	xs:dateTime
smallint	xs:short
smallint identity	xs:short
smallmoney	xs:decimal
sysname	xs:string
text	xs:string
timestamp	xs:hexBinary
tinyint	xs:short
tinyint identity	xs:short
uniqueidentifier	xs:string
varbinary	xs:hexBinary
varchar	xs:string
xml 1	xs:anyType
1 Supported for Microsoft SQL Server 2005 and 2008 only.

Sybase

Table E-7 describes how Sybase data types are mapped to XML schema data types for DataDirect XQuery. Any Sybase data type that is not listed is not supported by DataDirect XQuery.

Table E-7. Sybase Data Types 

Sybase Data Type	XML Schema Data Type
BIGINT 1	xs:long
BINARY(n)	xs:hexBinary
BIT	xs:boolean
CHAR(n)	xs:string
CHAR VARYING	xs:string
CHARACTER	xs:string
CHARACTER VARYING	xs:string
DATE	xs:date
DATETIME	xs:dateTime
DEC	xs:decimal
DECIMAL(p,s)	xs:decimal
DOUBLE	xs:double
FLOAT	xs:double
IMAGE	xs:hexBinary
INT	xs:int
INTEGER	xs:int
MONEY	xs:decimal
NATIONAL CHAR	xs:string
NATIONAL CHAR VARYING	xs:string
NCHAR(n)	xs:string
NUMERIC(p,s)	xs:decimal
NVARCHAR(n)	xs:string
REAL	xs:float
SMALLDATETIME	xs:dateTime
SMALLINT	xs:short
SMALLMONEY	xs:decimal
TEXT	xs:string
TIME	xs:time
TINYINT	xs:short
UNICHAR	xs:string
UNICODE CHAR VARYING	xs:string
UNICODE CHARACTER	xs:string
UNICODE CHARACTER VARYING	xs:string
UNIVARCHAR	xs:string
VARBINARY(n)	xs:hexBinary
VARCHAR(n)	xs:string
1 Supported only for Sybase 15.0.