H XQuery-SQL Mapping Reference

This appendix provides information about the native RDBMS Data Type support and XQuery mappings that the BEA XQuery engine generates or supports. It includes the following topics:

Core RDBMS Data Type Mapping:
Section H.6, "Base (Generic) RDBMS Data Type Mapping"

For information about using these mappings in Oracle Service Bus XQueries, see Section 35.9, "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/technology/software/products/ias/files/fusion_certification.html

H.1 IBM DB2/NT 8

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

Table H-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^Foot 1	xs:string
DATE	xs:date
DOUBLE	xs:double
DECIMAL(p,s)^Foot 2 (NUMERIC)	xs:decimal (if s > 0), xs:integer (if s = 0)
INTEGER	xs:int
LONG VARCHAR¹	xs:string
LONG VARCHAR FOR BIT DATA	xs:hexBinary
REAL	xs:float
SMALLINT	xs:short
TIME^Foot 3	xs:time^Foot 4
TIMESTAMP^Foot 5	xs:dateTime^{^Footref 4}
VARCHAR	xs:string^{^Footref 4}
VARCHAR() FOR BIT DATA	xs:hexBinary

^Footnote 1Pushed down in project list only.

^Footnote 2Where 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).

^Footnote 3Accurate to 1 second.

^Footnote 4Values converted to local time zone (timezone information removed) due to TIME and TIMESTAMP limitations.

^Footnote 5Precision limited to milliseconds.

H.2 Microsoft SQL Server

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

Table H-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^Foot 1	xs:dateTime^Foot 2
DECIMAL(p,s)^Foot 3 (NUMERIC)	xs:decimal (if s > 0), xs:integer (if s = 0)
FLOAT	xs:double
IMAGE	xs:hexBinary
INTEGER	xs:int
MONEY	xs:decimal
NCHAR	xs:string
NTEXT^Foot 4	xs:string
NVARCHAR	xs:string
REAL	xs:float
SMALLDATETIME^Foot 5	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 1Fractional-second-precision up to 3 digits (milliseconds). No timezone.

^Footnote 2Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations.

^Footnote 3Where 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).

^Footnote 4Pushed down in project list only.

^Footnote 5Accuracy of 1 minute.

H.3 Oracle8i, 8.1.x

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

Table H-3 Oracle 8.1.x Data Type Mapping

Oracle 8 Data Type	XQuery Type
BFILE	not supported
BLOB	xs:hexBinary
CHAR	xs:string
CLOB^Foot 1	xs:string
DATE^Foot 2	xs:dateTime
FLOAT	xs:double
LONG¹	xs:string
LONG RAW	xs:hexBinary
NCHAR	xs:string
NCLOB¹	xs:string
NUMBER	xs:double
NUMBER(p,s)^Foot 3	xs:decimal (if s > 0), xs:integer (if s <=0)
NVARCHAR2	xs:string
RAW	xs:hexBinary
ROWID	xs:string
UROWID	xs:string

^Footnote 1Pushed down in project list only.

^Footnote 2Does not support fractional seconds.

^Footnote 3Where 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).

H.4 Oracle 9i, Oracle Database 10g

This section identifies the data type and other mappings that the XQuery engine generates or supports for Oracle 9i and Oracle Database 10g. 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 H-4 Oracle 9i, Oracle Database 10g Data Type Mapping

Oracle Data Type	XQuery Type
BFILE	not supported
BLOB	xs:hexBinary
CHAR	xs:string
CLOB^Foot 1	xs:string
DATE	xs:dateTime^Foot 2
FLOAT	xs:double
INTERVAL DAY TO SECOND	xdt:dayTimeDuration
INTERVAL YEAR TO MONTH	xdt:yearMonthDuration
LONG¹	xs:string
LONG RAW	xs:hexBinary
NCHAR	xs:string
NCLOB¹	xs:string
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^Foot 3
TIMESTAMP WITH LOCAL TIMEZONE	xs:dateTime
TIMESTAMP WITH TIMEZONE	xs:dateTime
VARCHAR2	xs:string
UROWID	xs:string

^Footnote 1Pushed down in project list only.

^Footnote 2When 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.

^Footnote 3XQuery 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.

H.5 Sybase 12.5.2 (and higher)

This section identifies 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 H-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^Foot 1	xs:dateTime^Foot 2
DECIMAL(p,s)^Foot 3 (NUMERIC)	xs:decimal (if s > 0), xs:integer (if s == 0)
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^Foot 4	xs:dateTime
SMALLINT	xs:short
SMALLMONEY	xs:decimal
SYSNAME	xs:string
TEXT^Foot 5	xs:string
TIME	xs:time
TINYINT	xs:short
VARBINARY	xs:hexBinary
VARCHAR	xs:string

^Footnote 1Supports fractional seconds up to 3 digits (milliseconds) precision; no timezone information.

^Footnote 2When 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.

^Footnote 3Where 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).

^Footnote 4Accurate to 1 minute.

^Footnote 5Expressions returning text are pushed down in the project list only.

H.6 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 H-6 shows this mapping.

Table H-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^Foot 1	2005	xs:string
DATE	91	xs:date^Foot 2
DECIMAL (p,s)^Foot 3	3	xs:decimal (if s > 0), xs:integer (if s =0)
DOUBLE	8	xs:double
FLOAT	6	xs:double
INTEGER	4	xs:int
LONGVARBINARY	-4	xs:hexBinary
LONGVARCHAR¹	-1	xs:string
NUMERIC (p,s)^{^Footref 3}	2	xs:decimal (if s > 0), xs:integer (if s =0)
REAL	7	xs:float
SMALLINT	5	xs:short
TIME^Foot 4	92	xs:time⁴
TIMESTAMP⁴	93	xs:dateTime^{^Footref 2}
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.

^Footnote 1Pushed down in project list only.

^Footnote 2Values converted to local time zone (timezone information removed) due to DATE limitations.

^Footnote 3Where 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).

^Footnote 4Precision of underlying RDBMS determines the precision of TIME data type and how much truncation, if any, will occur in translating xs:time to TIME.