XQUERY xquery_statement
The SQL*Plus XQUERY command enables you to perform an XQuery 1.0 query on a specified database. XQUERY is supported on Oracle Database 10g (Release 2) and later versions. Attempting to use XQUERY on an earlier version of the Oracle Database gives the error:
SP2-614 Server version too low
xquery_statement
Specifies the XQuery statement you want to run. The statement is entered with standard XQuery syntax. The XQUERY statement is terminated with a forward slash, '/'.
Prefix your XQuery statement with the SQL*Plus command, XQUERY, and terminate the XQUERY command with a slash (/). XQUERY is a SQL*Plus keyword. If XQueries are executed in other tools, the keyword may not be needed.
XML output from the XQUERY command is displayed as native XML according to the active SET command options. SET LONG typically needs to be set. It may be useful to consider the following settings:
Linesize for rows longer than the default 80 characters in the Script Formatting page of iSQL*Plus Preferences (SET LINESIZE).
LOB, LONG and XML Type Size for rows longer than the default 80 characters in the Script Execution page of iSQL*Plus Preferences (SET LONG).
Output Page Setup to match output in the Interface Configuration page of iSQL*Plus Preferences (SET PAGESIZE).
Display Headings to repress the "Result Sequence" column heading in the Script Formatting page of iSQL*Plus Preferences (SET HEADING OFF).
The XQUERY command requires an active database connection. The command will not work with SQLPLUS /NOLOG.
Bind variables are not supported in the XQUERY command.
The site profile, glogin.sql, contains the following COLUMN command to define the column heading for XQuery output:
COLUMN column_value HEADING 'Result Sequence'
You can change the COLUMN definition to define the heading you want.
There are four iSQL*Plus Preferences (SET commands) specific to the XQUERY command. The SHOW XQUERY command gives the status of these settings. They are:
Table 12-5 XQUERY iSQL*Plus Preferences (SET commands)
Script Execution iSQL*Plus Preference | Equivalent SET Command |
---|---|
XQuery Base URI |
SET XQUERY BASEURI |
XQuery Ordering |
SET XQUERY ORDERING |
XQUERY Node Identity |
SET XQUERY NODE |
XQUERY Context |
SET XQUERY CONTEXT |
For more information about the SET XQUERY commands, see :
The XQuery statement in the following script queries the EMP_DETAILS_VIEW view of the HR schema:
set long 160 set linesize 160 xquery for $i in ora:view("EMP_DETAILS_VIEW") return $i /
Result Sequence ------------------------------------------------------------------------------------------- <ROW><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><HIREDATE>17- DEC-80</HIREDATE><SAL>800</SAL><DEPTNO>20</DEPTNO></ROW> 14 item(s) selected. |