SQL Support

This section provides information about the SQL statements that the XML driver processes, and about SQL standards and conventions that the driver supports:

■	“SQL Statements”

■	“Extensions to SQL Standards”

■	“Grammar Token Definitions”

SQL Statements

The SQL Engine included with the XML driver supports the following SQL statements:

■

Select

■	Create and Drop Table

■

Insert

■

Update

■

Delete

NOTE: See the table at the beginning of this chapter for the SQL statements that the XML driver supports for the different types of supported file formats.

Extensions to SQL Standards

The XML driver uses SQL grammar that is compliant with entry level ANSI SQL-92. Table 19-7 summarizes significant extensions to the grammar.

Table 19-7. SQL Extensions 

Entry Level ANSI SQL-92 Extension	Relevant Standard or Convention
Aliasing table references	Intermediate level ANSI SQL-92
ANSI date, time, and timestamp literals	Intermediate level ANSI SQL-92
Dynamic parameter specification	Full level ANSI SQL-92
GUID literals	COM
Hex string literals	Full level ANSI SQL-92
Left Outer Joins	Intermediate level ANSI SQL-92
ODBC escape support	ODBC 3.0
Scalar functions	ODBC 3.0

Grammar Token Definitions

The tokens used in the XML driver SQL grammar are defined in the following sections:

■	“Regular Identifiers”

■	“Delimited Identifiers”

■	“Integer Numbers”

■	“Real Numbers”

■	“Character String Literals”

■	“GUID Literals”

■	“Hex Literals”

■	“Time and Date Literals”

■	“SQL Operators and Symbols”

■	“Keywords for the XML Driver”

■	“SQL Comments”

Regular Identifiers

A regular identifier must begin with a letter and may not exceed 128 characters. In addition, all ASCII characters are converted to uppercase.

The following are examples of regular identifiers:

■

FOO

■	COLUMN_NAME

■	SCHEMA#NAME

■	Col3 (legal, but converted to COL3)

Delimited Identifiers

Delimited identifiers may not exceed 128 characters. A double quotation character can be embedded within the string by specifying two consecutive double quotation mark characters. A delimited identifier can span multiple lines. The body of a delimited identifier can contain any character except the newline character.

The following examples show delimited identifiers:

■	"$ % ^ ( $"

■	"This is a delimited variable name"

Integer Numbers

Examples of integer numbers are:

■

5

■

1004

Real Numbers

Examples of real numbers are:

■

.10

■

12.01

■

10.

■

.01e-10

■

12E+10

■

12.01e2

■

12.01e-10

■

12.e-10

Character String Literals

Character string literals are delimited with single quotation mark characters. A single quotation mark character can be embedded within the string by specifying two consecutive single quotation mark characters. A character string literal can span multiple lines.

Examples are:

■

'$%^($'

■	'This is a character string literal’

GUID Literals

A GUID uses the following format, where x is a hexadecimal digit:

xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

Hex Literals

Hex literal values are introduced with an uppercase X followed by a single quoted string of hexadecimal characters.

Examples are:

■

X’39FA’

■

X’B0F00D’

Time and Date Literals

Date, time, and timestamp literals are date, time, and timestamp values surrounded by a standard prefix and suffix. Date literals are specified in a YYYY-MM-DD format. Time literals are specified in an HH:MM:SS format with an optional fraction component. Timestamp literals are a concatenation of date and time values.

Examples for ODBC and SQL syntax are shown in the following table.

Table 19-8. Time and Date Literals 

Literal Type	ODBC Syntax	ANSI SQL-92 Syntax
Date Literal	{d ’1999-09-19’}	date ’1999-09-19’
Time Literal	{t ’11:11:11.225’}	time ’11:11:11.225’
Timestamp Literal	{ts ’1999-09-19 11:11:11.225’}	timestamp ’1999-09-19 11:11:11.225’
Timestamp Literal	{ts ’1999-09-19’}	timestamp ’1999-09-19’

NOTE: ODBC 1.x style ODBC escape sequences such as the following are not supported:

--(*VENDOR(Microsoft), PRODUCT(ODBC) ...*)--

SQL Operators and Symbols

Table 19-9. SQL Operators and Symbols 

Symbol	Description	Symbol	Description
':'	Colon	'<'	Less than operator
';'	Semicolon	‘)’	Right parenthesis
'.'	Period	‘=’	Equal operator
','	Comma	‘+’	Plus operator
'<>'	Not equal operator	‘-‘	Minus operator
'<='	Less than or equal operator	‘*’	Multiply operator
'>='	Greater than or equal operator	‘/’	Divide operator
‘>’	Greater than operator	‘?’	Dynamic parameter
‘(‘	Left parenthesis

Keywords for the XML Driver

A keyword may not be used as a regular identifier. For example, the following statement would generate a syntax error because INDICATOR is a keyword:

SELECT INDICATOR FROM T1

You can, however, enclose a keyword in double quotation marks to form a delimited identifier. For example, the following statement is valid:

SELECT "INDICATOR" FROM T1

Table 19-10 lists all of the keywords that are reserved for use in SQL statements or designated as potential future reserved words.

Table 19-10. Reserved Keywords 

ABSOLUTE	ACTION	ADD
AFTER	ALIAS	ALL
ALLOCATE	ALTER	AND
ANY	ARE	AS
ASC	ASSERTION	ASYNC
AT	AUTHORIZATION	AVG
BEFORE	BEGIN	BETWEEN
BIT	BIT_LENGTH	BOOLEAN
BOTH	BREADTH	BY
CALL	CASCADE	CASCADED
CASE	CAST	CATALOG
CHAR	CHAR_LENGTH	CHARACTER
CHARACTER_ LENGTH	CHECK	CLOSE
COALESCE	COLLATE	COLLATION
COLUMN	COLUMNS	COMMIT
COMPLETION	CONCAT	CONNECT
CONNECTION	CONSTRAINT	CONSTRAINTS
CONTINUE	CONVERT	CORRESPONDING
COUNT	CREATE	CROSS
CURDATE	CURRENT	CURRENT_DATE
CURRENT_TIME	CURRENT_ TIMESTAMP	CURRENT_USER
CURSOR	CURTIME	CYCLE
DATA	DATE	DAY
DAYOFMONTH	DAYOFWEEK	DEALLOCATE
DEC	DECIMAL	DECLARE
DEFAULT	DEFERRABLE	DEFERRED
DELETE	DEPTH	DESC
DESCRIBE	DESCRIPTOR	DIAGNOSTICS
DICTIONARY	DISCONNECT	DISTINCT
DOMAIN	DOUBLE	DROP
EACH	ELSE	ELSEIF
END	END_EXEC	EQUALS
ESCAPE	EXCEPT	EXCEPTION
EXEC	EXECUTE	EXISTS
EXTERNAL	EXTRACT	FALSE
FETCH	FIRST	FLOAT
FLOOR	FOR	FOREIGN
FOUND	FROM	FULL
GENERAL	GET	GLOBAL
GO	GOTO	GRANT
GROUP	HAVING	HOUR
IDENTIFY	IF	IFNULL
IGNORE	IMMEDIATE	IN
INDEX	INFO	INDICATOR
INITIALLY	INNER	INPUT
INSENSITIVE	INSERT	INT
INTEGER	INTERSECT	INTERVAL
INTO	IS	ISOLATION
JOIN	KEY	LANGUAGE
LAST	LCASE	LEADING
LEAVE	LEFT	LENGTH
LESS	LEVEL	LIKE
LIMIT	LOCAL	LOOP
LOWER	LTRIM	MATCH
MAX	MIN	MINUTE
MOD	MODIFY	MODULE
MONTH	NAMES	NATIONAL
NATURAL	NCHAR	NEW
NEXT	NO	NONE
NOT	NOW	NULL
NULLIF	NUMERIC	OBJECT
OCTET_LENGTH	OF	OFF
OID	OLD	ON
ONLY	OPEN	OPERATION
OPERATORS	OPTION	OR
ORDER	OTHERS	OUTER
OUTPUT	OVERLAPS	PAD
PARAMETERS	PARTIAL	PENDANT
POSITION	POWER	PRECISION
PREORDER	PREPARE	PRESERVE
PRIMARY	PRIOR	PRIVATE
PRIVILEGES	PROCEDURE	PROTECTED
PUBLIC	RCASE	READ
REAL	RECURSIVE	REF
REFERENCES	REFERENCING	RELATIVE
REMOVE	REPLACE	RESIGNAL
RESTRICT	RETURN	RETURNS
REVOKE	RIGHT	ROLE
ROLLBACK	ROUND	ROUTINE
ROW	ROWS	RTRIM
SAVEPOINT	SCHEMA	SCROLL
SEARCH	SECOND	SECTION
SELECT	SENSITIVE	SEQUENCE
SESSION	SESSION_USER	SET
SIGNAL	SIMILAR	SIZE
SMALLINT	SOME	SPACE
SQL	SQLCODE	SQLERROR
SQLEXCEPTION	SQLSTATE	SQLWARNING
STRUCTURE	SUBSTRING	SUM
SYSTEM_USER	TABLE	TEMPORARY
TEST	THEN	THERE
TIME	TIMESTAMP	TIMEZONE_ HOUR
TIMEZONE_ MINUTE	TO	TRAILING
TRANSACTION	TRANSLATE	TRANSLATION
TRIGGER	TRIM	TRUE
TYPE	UCASE	UNDER
UNION	UNIQUE	UNKNOWN
UPDATE	UPPER	USAGE
USER	USING	VALUE
VALUES	VARCHAR	VARIABLE
VARYING	VIEW	VIRTUAL
VISIBLE	WAIT	WHEN
WHENEVER	WHERE	WHILE
WITH	WITHOUT	WORK
WRITE	YEAR	ZONE

SQL Comments

ANSI SQL-92 standard comments (--) and C++ standard comments (/*...*/, //) are supported. Comments can be nested.

For example, in the following query columns col2, col3, and col4 are ignored:

SELECT col1  /* col1 comment */

/*

    col2, -- col2 comment

    col3, // col3 comment

    col4, /* col4 comment */

*/

FROM t1