Skip Headers
Oracle® Fusion Middleware Administrator's Guide for Oracle Service Bus
11g Release 1 (11.1.1.6.3)

Part Number E15867-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

H 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:

For information about using these mappings in Oracle Service Bus XQueries, see Section 37.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/technetwork/middleware/ias/downloads/fusion-certification-100350.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

CLOBFoot 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 VARCHAR1

xs:string

LONG VARCHAR FOR BIT DATA

xs:hexBinary

REAL

xs:float

SMALLINT

xs:short

TIMEFoot 3 

xs:timeFoot 4 

TIMESTAMPFoot 5 

xs:dateTimeFootref 4

VARCHAR

xs:stringFootref 4

VARCHAR() FOR BIT DATA

xs:hexBinary


Footnote 1 Pushed down in project list only.

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

Footnote 3 Accurate to 1 second.

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

Footnote 5 Precision 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

DATETIMEFoot 1 

xs:dateTimeFoot 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

NTEXTFoot 4 

xs:string

NVARCHAR

xs:string

REAL

xs:float

SMALLDATETIMEFoot 5 

xs:dateTime

SMALLINT

xs:short

SMALLMONEY

xs:decimal

SQL_VARIANT

xs:string

TEXT4

xs:string

TIMESTAMP

xs:hexBinary

TINYINT

xs:short

VARBINARY

xs:hexBinary

VARCHAR

xs:string

UNIQUIDENTIFIER

xs:string


Footnote 1 Fractional-second-precision up to 3 digits (milliseconds). No timezone.

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

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

Footnote 4 Pushed down in project list only.

Footnote 5 Accuracy 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

CLOBFoot 1 

xs:string

DATEFoot 2 

xs:dateTime

FLOAT

xs:double

LONG1

xs:string

LONG RAW

xs:hexBinary

NCHAR

xs:string

NCLOB1

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 1 Pushed down in project list only.

Footnote 2 Does not support fractional seconds.

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

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

CLOBFoot 1 

xs:string

DATE

xs:dateTimeFoot 2 

FLOAT

xs:double

INTERVAL DAY TO SECOND

xdt:dayTimeDuration

INTERVAL YEAR TO MONTH

xdt:yearMonthDuration

LONG1

xs:string

LONG RAW

xs:hexBinary

NCHAR

xs:string

NCLOB1

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:dateTimeFoot 3 

TIMESTAMP WITH LOCAL TIMEZONE

xs:dateTime

TIMESTAMP WITH TIMEZONE

xs:dateTime

VARCHAR2

xs:string

UROWID

xs:string


Footnote 1 Pushed down in project list only.

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

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

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

DATETIMEFoot 1 

xs:dateTimeFoot 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

SMALLDATETIMEFoot 4 

xs:dateTime

SMALLINT

xs:short

SMALLMONEY

xs:decimal

SYSNAME

xs:string

TEXTFoot 5 

xs:string

TIME

xs:time

TINYINT

xs:short

VARBINARY

xs:hexBinary

VARCHAR

xs:string


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

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

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

Footnote 4 Accurate to 1 minute.

Footnote 5 Expressions 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

CLOBFoot 1 

2005

xs:string

DATE

91

xs:dateFoot 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

LONGVARCHAR1

-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

TIMEFoot 4 

92

xs:time4

TIMESTAMP4

93

xs:dateTimeFootref 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 1 Pushed down in project list only.

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

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

Footnote 4 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.