> User’s Guide > XQuery-SQL Mapping Reference

User’s Guide

     Previous  Next    Open TOC in new window    View as PDF - New Window  Get Adobe Reader - New Window
Content starts here

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:

For information about using these mappings in ALSB XQueries, see Accessing Databases Using XQuery.

For complete information about database and JDBC drivers support in ALSB, see Supported Database Configurations in Supported Configurations for AquaLogic Service Bus.

 

IBM DB2/NT 8

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

Table A-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
CLOB1
xs:string
DATE
xs:date
DOUBLE
xs:double
DECIMAL(p,s)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
TIME3
xs:time4
TIMESTAMP5
xs:dateTime4
VARCHAR
xs:string4
VARCHAR() FOR BIT DATA
xs:hexBinary

1Pushed down in project list only.

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

3Accurate to 1 second.

4Values converted to local time zone (timezone information removed) due to TIME and TIMESTAMP limitations. See XQuery-SQL Data Type Mappings in XQuery Engine and SQL in the XQuery Developer's Guide for more information.

5Precision limited to milliseconds.

 

Microsoft SQL Server

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

Table A-2 SQL Server 2000 Data Type Mapping
SQL Data Type
XQuery Type
BIGINT
xs:long
BINARY
xs:hexBinary
BIT
xs:boolean
CHAR
xs:string
DATETIME1
xs:dateTime2
DECIMAL(p,s)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
NTEXT4
xs:string
NVARCHAR
xs:string
REAL
xs:float
SMALLDATETIME5
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

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

2Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations. See XQuery-SQL Data Type Mappings in XQuery Engine and SQL in the XQuery Developer's Guide for more information.

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

4Pushed down in project list only.

5Accuracy of 1 minute.

 

Oracle 8.1.x

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

Table A-3 Oracle 8.1.x Data Type Mapping
Oracle 8 Data Type
XQuery Type
BFILE
not supported
BLOB
xs:hexBinary
CHAR
xs:string
CLOB1
xs:string
DATE2
xs:dateTime
FLOAT
xs:double
LONG1
xs:string
LONG RAW
xs:hexBinary
NCHAR
xs:string
NCLOB1
xs:string
NUMBER
xs:double
NUMBER(p,s)3
xs:decimal (if s > 0), xs:integer (if s <=0)
NVARCHAR2
xs:string
RAW
xs:hexBinary
ROWID
xs:string
UROWID
xs:string

1Pushed down in project list only.

2Does not support fractional seconds.

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

 

Oracle 9.x, 10.x

This section identifies the data type and other mappings that the XQuery engine generates or supports for Oracle 9.x (Oracle 9i) and Oracle 10.x (Oracle 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 A-4 Oracle 9.x, 10.x Data Type Mapping
Oracle 9 Data Type
XQuery Type
BFILE
not supported
BLOB
xs:hexBinary
CHAR
xs:string
CLOB1
xs:string
DATE
xs:dateTime2
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:dateTime3
TIMESTAMP WITH LOCAL TIMEZONE
xs:dateTime
TIMESTAMP WITH TIMEZONE
xs:dateTime
VARCHAR2
xs:string
UROWID
xs:string

1Pushed down in project list only.

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. See XQuery-SQL Data Type Mappings in XQuery Engine and SQL in the XQuery Developer's Guide for more information.

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.

 

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 A-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
DATETIME1
xs:dateTime2
DECIMAL(p,s)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
SMALLDATETIME4
xs:dateTime
SMALLINT
xs:short
SMALLMONEY
xs:decimal
SYSNAME
xs:string
TEXT5
xs:string
TIME
xs:time
TINYINT
xs:short
VARBINARY
xs:hexBinary
VARCHAR
xs:string

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

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. See XQuery-SQL Data Type Mappings in XQuery Engine and SQL in the XQuery Developer's Guide for more information.

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

4Accurate to 1 minute.

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

 

Pointbase 4.4 (and higher)

This section identifies the data types that the XQuery engine generates or supports for Pointbase.

Table A-6 Pointbase 4.4 Data Type Mapping
Pointbase Data Type
XQuery Type
BIGINT
xs:long
BLOB
xs:hexBinary
BOOLEAN
xs:boolean
CHAR (CHARACTER)
xs:string
CLOB
xs:string
DATE
xs:date
DECIMAL(p,s)1 (NUMERIC)
xs:decimal (if s > 0), xs:integer (if s == 0)
DOUBLE PRECISION
xs:double
FLOAT
xs:double
INTEGER (INT)
xs:int
SMALLINT
xs:short
REAL
xs:float
TIME
xs:time
TIMESTAMP
xs:dateTime
VARCHAR
xs:string

1Where 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).

 

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

Table A-7 Base Platform Data Type Mapping (JDBC<– >XQuery Equivalents)
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
CLOB1
2005
xs:string
DATE
91
xs:date2
DECIMAL (p,s)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)3
2
xs:decimal (if s > 0), xs:integer (if s =0)
REAL
7
xs:float
SMALLINT
5
xs:short
TIME4
92
xs:time4
TIMESTAMP4
93
xs:dateTime2
TINYINT
-6
xs:short
VARBINARY
-3
xs:hexBinary
VARCHAR
12
xs:string
OTHER
1111
ALSB uses native data type name to map to an appropriate XQuery data type.
Other vendor-specific JDBC type codes

1Pushed down in project list only.

2Values converted to local time zone (timezone information removed) due to DATE limitations. See XQuery-SQL Data Type Mappings in XQuery Engine and SQL in the XQuery Developer's Guide for more information.

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

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.

Related Topics

Accessing Databases Using XQuery

fn-bea:execute-sql()


  Back to Top       Previous  Next