XQuery-SQL Mapping Reference

F XQuery-SQL Mapping Reference

This chapter provides information about the native RDBMS Data Type support and XQuery mappings that the Oracle XQuery engine generates or supports.

This chapter includes the following sections:

Core RDBMS Data Type Mapping:
Base (Generic) RDBMS Data Type Mapping

For information about using these mappings in Oracle Service Bus XQueries, see Accessing Databases Using XQuery.

For complete information about database and JDBC drivers support in Oracle Service Bus, see Oracle Fusion Middleware Supported System Configurations at:

http://www.oracle.com/technetwork/middleware/ias/downloads/fusion-certification-100350.html

IBM DB2/NT 8

This section lists the data type mappings that the XQuery engine generates or supports for IBM DB2/NT 8.

Table F-1 IBM DB2 Data Type Mappings

DB2 Data Type	XQuery Type
BIGINT	xs:long
BLOB	xs:hexBinary
CHAR	xs:string
CHAR() FOR BIT DATA	xs:hexBinary
CLOB	xs:string Pushed down in project list only.
DATE	xs:date
DOUBLE	xs:double
DECIMAL(p,s) (NUMERIC)	xs:decimal (if s > 0), xs:integer (if s = 0), where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).
INTEGER	xs:int
LONG VARCHAR¹	xs:string
LONG VARCHAR FOR BIT DATA	xs:hexBinary
REAL	xs:float
SMALLINT	xs:short
TIME	xs:time Accurate to one second. Values converted to local time zone (timezone information removed) due to TIME and TIMESTAMP limitations.
TIMESTAMP	xs:dateTime Precision limited to milliseconds.
VARCHAR	xs:string
VARCHAR() FOR BIT DATA	xs:hexBinary

Microsoft SQL Server

This section lists the data type mappings that the XQuery engine generates or supports for Microsoft SQL Server.

Table F-2 SQL Server 2000 Data Type Mapping

SQL Data Type	XQuery Type
BIGINT	xs:long
BINARY	xs:hexBinary
BIT	xs:boolean
CHAR	xs:string
DATETIME	xs:dateTime Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations. Fractional-second-precision up to 3 digits (milliseconds). No timezone.
DECIMAL(p,s) (NUMERIC)	xs:decimal (if s > 0), xs:integer (if s = 0), where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).
FLOAT	xs:double
IMAGE	xs:hexBinary
INTEGER	xs:int
MONEY	xs:decimal
NCHAR	xs:string
NTEXT^Foot 1	xs:string
NVARCHAR	xs:string
REAL	xs:float
SMALLDATETIME^Foot 2	xs:dateTime
SMALLINT	xs:short
SMALLMONEY	xs:decimal
SQL_VARIANT	xs:string
TEXT⁴	xs:string
TIMESTAMP	xs:hexBinary
TINYINT	xs:short
VARBINARY	xs:hexBinary
VARCHAR	xs:string
UNIQUIDENTIFIER	xs:string

^Footnote 1

Pushed down in project list only.

^Footnote 2

Accuracy of 1 minute.

Oracle8i, 8.1.x

This section lists the data types that the XQuery engine generates or supports for Oracle 8.1.x (Oracle 8i).

Table F-3 Oracle 8.1.x Data Type Mapping

Oracle 8 Data Type	XQuery Type
BFILE	not supported
BLOB	xs:hexBinary
CHAR	xs:string
CLOB	xs:string Pushed down in project list only.
DATE	xs:dateTime Does not support fractional seconds.
FLOAT	xs:double
LONG	xs:string Does not support fractional seconds.
LONG RAW	xs:hexBinary
NCHAR	xs:string
NCLOB	xs:string Does not support fractional seconds.
NUMBER	xs:double
NUMBER(p,s)	xs:decimal (if s > 0), xs:integer (if s <=0), where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).
NVARCHAR2	xs:string
RAW	xs:hexBinary
ROWID	xs:string
UROWID	xs:string

Oracle 9i and Later

This section lists the data type and other mappings that the XQuery engine generates or supports for Oracle Database 9i, 10g, 11g, and 12c.

Note that Oracle treats empty strings as NULLs, which deviates from XQuery semantics and may lead to unexpected results for expressions that are pushed down.

Table F-4 Oracle 9i and later Data Type Mapping

Oracle Data Type	XQuery Type
BFILE	not supported
BLOB	xs:hexBinary
CHAR	xs:string
CLOB	xs:string Pushed down in project list only.
DATE	xs:dateTime When SDO stores xs:dateTime value in Oracle DATE type, it is converted to local time zone and fractional seconds are truncated due to DATE limitations.
FLOAT	xs:double
INTERVAL DAY TO SECOND	xdt:dayTimeDuration
INTERVAL YEAR TO MONTH	xdt:yearMonthDuration
LONG	xs:string Pushed down in project list only.
LONG RAW	xs:hexBinary
NCHAR	xs:string
NCLOB	xs:string Pushed down in project list only.
NUMBER	xs:double
NUMBER(p,s)	xs:decimal (if s > 0), xs:integer (if s <=0)
NVARCHAR2	xs:string
RAW	xs:hexBinary
ROWID	xs:string
TIMESTAMP	xs:dateTime XQuery engine maps XQuery xs:dateTime to either TIMESTAMP or TIMESTAMP WITH TIMEZONE data type, depending on presence of timezone information. Storing xs:dateTime using SDO may result in loss of precision for fractional seconds, depending on the SQL type definition.
TIMESTAMP WITH LOCAL TIMEZONE	xs:dateTime
TIMESTAMP WITH TIMEZONE	xs:dateTime
VARCHAR2	xs:string
UROWID	xs:string

Sybase 12.5.2 (and higher)

This section lists the data types that the XQuery engine generates or supports for Sybase 12.5.2 and higher.

Note:

Sybase deviates from XQuery semantics (which ignores empty strings) and treats empty strings as a single-space string.

Table F-5 Sybase 12.5.2 Data Type Mapping

Sybase Data Type	XQuery Type
BINARY	xs:hexBinary
BIT	xs:boolean
CHAR	xs:string
DATE	xs:date
DATETIME	xs:dateTime Supports fractional seconds up to 3 digits (milliseconds) precision; no timezone information. When SDO stores xs:dateTime value in Oracle DATE type, it is converted to local time zone and fractional seconds are truncated due to DATE limitations.
DECIMAL(p,s) (NUMERIC)	xs:decimal (if s > 0), xs:integer (if s == 0) Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).
DOUBLE PRECISION	xs:double
FLOAT	xs:double
IMAGE	xs:hexBinary
INT (INTEGER)	xs:int
MONEY	xs:decimal
NCHAR	xs:string
NVARCHAR	xs:string
REAL	xs:float
SMALLDATETIME	xs:dateTime Accurate to 1 minute.
SMALLINT	xs:short
SMALLMONEY	xs:decimal
SYSNAME	xs:string
TEXT	xs:string Expressions returning text are pushed down in the project list only.
TIME	xs:time
TINYINT	xs:short
VARBINARY	xs:hexBinary
VARCHAR	xs:string

Base (Generic) RDBMS Data Type Mapping

When mapping SQL to XQuery data types, the XQuery engine first checks the JDBC typecode. If the typecode has a corresponding XQuery type, the XQuery engine uses the matching native type name. If no matching typecode or type name is available, the column is ignored.

Table F-6 shows this mapping.

Table F-6 RDBMS Data Type Mapping

JDBC Data Type	Typecode	XQuery Data Type
BIGINT	-5	xs:long
BINARY	-2	xs:string
BIT	-7	xs:boolean
BLOB	2004	xs:hexBinary
BOOLEAN	16	xs:boolean
CHAR	1	xs:string
CLOB	2005	xs:string Pushed down in project list only.
DATE	91	xs:date Values converted to local time zone (timezone information removed) due to DATE limitations.
DECIMAL (p,s)	3	xs:decimal (if s > 0), xs:integer (if s =0) Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).
DOUBLE	8	xs:double
FLOAT	6	xs:double
INTEGER	4	xs:int
LONGVARBINARY	-4	xs:hexBinary
LONGVARCHAR	-1	xs:string
NUMERIC (p,s)	2	xs:decimal (if s > 0), xs:integer (if s =0)
REAL	7	xs:float
SMALLINT	5	xs:short
TIME	92	xs:time Precision of underlying RDBMS determines the precision of TIME data type and how much truncation, if any, will occur in translating xs:time to TIME.
TIMESTAMP	93	xs:dateTime Precision of underlying RDBMS determines the precision of TIME data type and how much truncation, if any, will occur in translating xs:time to TIME.
TINYINT	-6	xs:short
VARBINARY	-3	xs:hexBinary
VARCHAR	12	xs:string
OTHER vendor-specific JDBC type codes	1111	Oracle Service Bus uses native data type name to map to an appropriate XQuery data type.