This discussion is archived
6 Replies Latest reply: Jan 8, 2009 11:23 AM by 678084 RSS

"xquery" versus "select xmlquery" and passing clauses

678084 Newbie
Currently Being Moderated
Hi All,

I'm using SQL*Plus in Oracle 11g.

I've been doing some experiments with executing xqueries. So far, I've used the form "select xmlquery('<statement>') from dual;", but this is annoying because some character escaping has to be done: the xquery must use only double quotes, not single, and function declarations must be terminated by
};(: :)
instead of the standard };

Using "xquery"instead of "xmlquery" gets rid of these problems. For example:
xquery
declare function local:one() { 1 };
local:one()
/
works as expected. So that's better. But so far I've been unable to use passing clauses, either to set the context or external variables, with xquery:
xquery
1
passing XMLTYPE('<dummy/>')
/
returns:
ERROR:
ORA-19114: XPST0003 - error during parsing the XQuery expression:
LPX-00801: XQuery syntax error at 'passing'

So I have a couple of questions:
* How can I use 'passing' to set the context in xquery?
* How can I use 'passing' to set external variables in xquery?
* Is there a way to use xquery or xmlquery with the query being in a file on the (client's or server's) filesystem, and having this file containing any legal xquery -- with single-quotes, double-quotes, and unescaped function declaration endings?
* What is your personal guideline in choosing between xquery and xmlquery?

Thanks!
  • 1. Re: "xquery" versus "select xmlquery" and passing clauses
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    As said (::) is used in SQLPlus* as a terminator. This behavior can be alter in SQL*Plus by defining a different sqlterminator
    SQL> show sqlterminator
    sqlterminator ";" (hex 3b)
    The XQuery specific SQL*Plus statement get re-written internally to a XMLTable operator as described in http://www.liberidu.com/blog/?cat=146

    Examples from Mark while making use of the repository resources (accessible via WebDAV / FTP / HTTP): ---> Re: XMLQuery (XQuery) with namespace
    Using PASSING see: http://www.liberidu.com/blog/?p=376

    Officially you should not query outside the database (also a very expensive use of an database as an software client) because it seen as an security issue...
    ...but (http://www.liberidu.com/blog/?p=682 )

    The following is part of that expensive use of the database as a xquery client (example ukoug06.sql from the scripts.zip file)
    clear screen
    ----------------------------------------------------------
    
    -- Drop user BFILE if it exists - Cleaning Up First
     
    ----------------------------------------------------------
     
    connect / as sysdba
    
    set serveroutput on
    set echo on
    set termout on
    set feed on
     
    drop user bfile cascade;
    
    purge dba_recyclebin;
     
    CREATE USER bfile PROFILE DEFAULT IDENTIFIED BY bfile;
     
    grant xdbadmin, dba to BFILE;
    
    -- GRANT JAVAUSERPRIV to BFILE;
    
    EXECUTE dbms_java.grant_permission( 'BFILE', 'java.io.FilePermission','C:\UKOUG-2008\XML','read' );
    
    pause
     
    clear screen
    
    ----------------------------------------------------------
    
    -- Create directory and show the content of an XML File
     
    ----------------------------------------------------------
    
    connect bfile/bfile
    
    set termout on
    set feed on
    set lines 40
    set long 10000000
    set serveroutput on
    set echo on
    
    
    drop directory XMLSTORE;
    
    CREATE directory xmlstore AS 'C:\UKOUG-2008\XML';
    
    SELECT extract((XMLTYPE(bfilename('XMLSTORE','test.xml'),NLS_CHARSET_ID('AL32UTF8'))),'*') AS "XML"
    from   dual;
     
    pause
    
    clear screen
    
    ----------------------------------------------------------
    
    -- Directory Listing - Tom Kyte
     
    ----------------------------------------------------------
    
    create global temporary table DIR_LIST
     ( filename varchar2(255) )
     on commit delete rows
    /
     
    
    create or replace
      and compile java source named "DirList"
    as
    import java.io.*;
    import java.sql.*;
     
    public class DirList
    {public static void getList(String directory)
                       throws SQLException
    {   File path = new File( directory );
        String[] list = path.list();
        String element;
     
        for(int i = 0; i < list.length; i++)
        {   element = list;
    #sql { INSERT INTO DIR_LIST (FILENAME)
    VALUES (:element) };
    }}}
    /

    create or replace procedure get_dir_list( p_directory in varchar2 )
    as language java
    name 'DirList.getList( java.lang.String )';
    /

    pause

    clear screen

    ----------------------------------------------------------

    -- The content of the global temporary table

    ----------------------------------------------------------

    exec get_dir_list( 'C:\UKOUG-2008\XML' );


    select * from dir_list;

    -- "COMMIT" will clear / truncate the global temporary table...

    pause

    clear screen

    ----------------------------------------------------------

    -- Combined: Reading XML content from multiple XML files

    ----------------------------------------------------------

    commit;

    exec get_dir_list( 'C:\UKOUG-2008\XML' );

    select * from dir_list where filename like '%.xml';

    set lines 80

    select extract((XMLTYPE(bfilename('XMLSTORE',dl.filename),NLS_CHARSET_ID('AL32UTF8'))),'*') AS "XML"
    from dir_list dl
    where dl.filename like '%.xml';



    Has the following output
    Connected.
    SQL> set termout on
    SQL> set feed on
    SQL>
    SQL> drop user bfile cascade;
    
    User dropped.
    
    SQL>
    SQL> purge dba_recyclebin;
    
    DBA Recyclebin purged.
    
    SQL>
    SQL> CREATE USER bfile PROFILE DEFAULT IDENTIFIED BY bfile;
    
    User created.
    
    SQL>
    SQL> grant xdbadmin, dba to BFILE;
    
    Grant succeeded.
    
    SQL>
    SQL> -- GRANT JAVAUSERPRIV to BFILE;
    SQL>
    SQL> EXECUTE dbms_java.grant_permission( 'BFILE', 'java.io.FilePermission','C:\UKOUG-2008\XML','read' );
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> ----------------------------------------------------------
    SQL>
    SQL> -- Create directory and show the content of an XML File
    SQL>
    SQL> ----------------------------------------------------------
    SQL>
    SQL> connect bfile/bfile
    Connected.
    SQL>
    SQL>
    SQL> drop directory XMLSTORE;
    
    Directory dropped.
    
    SQL>
    SQL> CREATE directory xmlstore AS 'C:\UKOUG-2008\XML';
    
    Directory created.
    
    SQL>
    SQL> SELECT extract((XMLTYPE(bfilename('XMLSTORE','test.xml'),NLS_CHARSET_ID('AL32UTF8'))),'*') AS "XML"
      2  from   dual;
    
    XML
    ----------------------------------------
    <ROOT xmlns="http://www.myserver.com/pub
    lic/root.xsd"><ID>666</ID><INFO><INFO_ID
    
    666</INFO_ID><INFO_CONTENT>Text</INFO_C
    ONTENT></INFO></ROOT> 1 row selected. SQL> SQL> ---------------------------------------------------------- SQL> SQL> -- Directory Listing - Tom Kyte SQL> SQL> ---------------------------------------------------------- SQL> SQL> create global temporary table DIR_LIST   2   ( filename varchar2(255) )   3   on commit delete rows   4  / Table created. SQL> SQL> SQL> create or replace   2    and compile java source named "DirList"   3  as   4  import java.io.*;   5  import java.sql.*;   6   7  public class DirList   8  {public static void getList(String directory)   9                     throws SQLException 10  {   File path = new File( directory ); 11      String[] list = path.list(); 12      String element; 13 14      for(int i = 0; i < list.length; i++) 15      {   element = list;
    16 #sql { INSERT INTO DIR_LIST (FILENAME)
    17 VALUES (:element) };
    18 }}}
    19 /

    Java created.

    SQL>
    SQL> create or replace procedure get_dir_list( p_directory in varchar2 )
    2 as language java
    3 name 'DirList.getList( java.lang.String )';
    4 /

    Procedure created.

    SQL>
    SQL> ----------------------------------------------------------
    SQL>
    SQL> -- The content of the global temporary table
    SQL>
    SQL> ----------------------------------------------------------
    SQL>
    SQL> exec get_dir_list( 'C:\UKOUG-2008\XML' );

    PL/SQL procedure successfully completed.

    SQL>
    SQL>
    SQL> select * from dir_list;

    FILENAME
    ----------------------------------------
    anton.xml
    marco.xml
    root.xml
    root.xsd
    test.xml

    5 rows selected.

    SQL>
    SQL> -- "COMMIT" will clear / truncate the global temporary table...
    SQL>
    Commit complete.

    SQL>
    SQL> exec get_dir_list( 'C:\UKOUG-2008\XML' );

    PL/SQL procedure successfully completed.

    SQL>
    SQL> select * from dir_list where filename like '%.xml';

    FILENAME
    ----------------------------------------
    anton.xml
    marco.xml
    root.xml
    test.xml

    4 rows selected.

    SQL>
    SQL> set lines 80
    SQL>
    SQL> select extract((XMLTYPE(bfilename('XMLSTORE',dl.filename),NLS_CHARSET_ID('AL32UTF8'))),'*') AS "XML"
    2 from dir_list dl
    3 where dl.filename like '%.xml';

    XML
    --------------------------------------------------------------------------------
    <ROWSET><ROW><ROWNUM>1</ROWNUM></ROW><ROW><ROWNUM>2</ROWNUM></ROW><ROW><ROWNUM>3
    </ROWNUM></ROW><ROW><ROWNUM>4</ROWNUM></ROW><ROW><ROWNUM>5</ROWNUM></ROW><ROW><R
    OWNUM>6</ROWNUM></ROW><ROW><ROWNUM>7</ROWNUM></ROW><ROW><ROWNUM>8</ROWNUM></ROW>
    <ROW><ROWNUM>9</ROWNUM></ROW><ROW><ROWNUM>10</ROWNUM></ROW><ROW><ROWNUM>11</ROWN
    UM></ROW><ROW><ROWNUM>12</ROWNUM></ROW><ROW><ROWNUM>13</ROWNUM></ROW><ROW><ROWNU
    M>14</ROWNUM></ROW><ROW><ROWNUM>15</ROWNUM></ROW><ROW><ROWNUM>16</ROWNUM></ROW><
    ROW><ROWNUM>17</ROWNUM></ROW><ROW><ROWNUM>18</ROWNUM></ROW><ROW><ROWNUM>19</ROWN
    UM></ROW><ROW><ROWNUM>20</ROWNUM></ROW><ROW><ROWNUM>21</ROWNUM></ROW><ROW><ROWNU
    M>22</ROWNUM></ROW><ROW><ROWNUM>23</ROWNUM></ROW><ROW><ROWNUM>24</ROWNUM></ROW><
    /ROWSET>

    <ROWSET><ROW><ROWNUM>1</ROWNUM></ROW><ROW><ROWNUM>2</ROWNUM></ROW><ROW><ROWNUM>3
    </ROWNUM></ROW><ROW><ROWNUM>4</ROWNUM></ROW><ROW><ROWNUM>5</ROWNUM></ROW><ROW><R
    OWNUM>6</ROWNUM></ROW><ROW><ROWNUM>7</ROWNUM></ROW><ROW><ROWNUM>8</ROWNUM></ROW>
    <ROW><ROWNUM>9</ROWNUM></ROW><ROW><ROWNUM>10</ROWNUM></ROW><ROW><ROWNUM>11</ROWN
    UM></ROW><ROW><ROWNUM>12</ROWNUM></ROW><ROW><ROWNUM>13</ROWNUM></ROW><ROW><ROWNU
    M>14</ROWNUM></ROW><ROW><ROWNUM>15</ROWNUM></ROW><ROW><ROWNUM>16</ROWNUM></ROW><
    ROW><ROWNUM>17</ROWNUM></ROW><ROW><ROWNUM>18</ROWNUM></ROW><ROW><ROWNUM>19</ROWN
    UM></ROW><ROW><ROWNUM>20</ROWNUM></ROW><ROW><ROWNUM>21</ROWNUM></ROW><ROW><ROWNU
    M>22</ROWNUM></ROW><ROW><ROWNUM>23</ROWNUM></ROW><ROW><ROWNUM>24</ROWNUM></ROW><
    /ROWSET>

    <ROOT xmlns="http://www.myserver.com/public/root.xsd"><ID>0</ID><INFO><INFO_ID>0
    </INFO_ID><INFO_CONTENT>Text</INFO_CONTENT></INFO></ROOT>

    <ROOT xmlns="http://www.myserver.com/public/root.xsd"><ID>666</ID><INFO><INFO_ID
    666</INFO_ID><INFO_CONTENT>Text</INFO_CONTENT></INFO></ROOT>
    4 rows selected.



    Edited by: Marco Gralike on Jan 6, 2009 12:56 AM
  • 2. Re: "xquery" versus "select xmlquery" and passing clauses
    mdrake Expert
    Currently Being Moderated
    The xquery command was added to sqlplus to make it easy to demonstrate very basic Xquery syntax. All that happens with this is the SQLPLUS executable executes the command

    select * from XMLTABLE('your XQuery goes here')

    Hence if you want to use features like parameter passing etc you'll need to use the select ... from mechansim.

    -M
  • 3. Re: "xquery" versus "select xmlquery" and passing clauses
    678084 Newbie
    Currently Being Moderated
    Thanks Marco and Mdrake.

    So, I understand that I cannot use "xquery" if I want to set the context and external variable. Fair enough: I have no problem using "passing" in a "select xmlquery" statement.

    However, there are still serious limitations to that approach. One of them is the 4000 character limit for the query.

    Marco: maybe I wasn't clear enough. I'm not trying to query outside the database. I want to query XMLType columns in the database, but using an XQuery defined in an external file, so as to get rid of the limitations of xmlquery concerning single quotes, blank lines, max query length, sql terminators.

    Besides "xquery" and "xmlquery" in SQL*Plus, I understand that a third option is to use XQL*Plus instead. I have had trouble using it (class path problems and sealed package java security errors). Are the annoying limitations mentioned above also present in XQL*Plus? If not, I'll try to get XQL*Plus to work.

    Best,
  • 4. Re: "xquery" versus "select xmlquery" and passing clauses
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Maybe the following example from Mark will help (XMLQuery() function: xquery string max length? Although it doing it the way as advertised here (webday storage) is performance intensive and should be used as an example only.

    You will have to find an alternative for the uri part
    xdburitype('/public/q1.xqy')
    As far as I could find, "XQLPlus" is part of JDeveloper 11 (XQUERY / XMLTABLE Syntax to read and XML from a Windows local drive?
    I have no knowledge off, yet, regarding eg the limitations and / or its use. But if you would go this far then every other outside xquery with jdbc capabilities should fit...?

    Edited by: Marco Gralike on Jan 6, 2009 7:04 PM
  • 5. Re: "xquery" versus "select xmlquery" and passing clauses
    mdrake Expert
    Currently Being Moderated
    Not sure which release you are on or what your DB Character set is but the patch for bug 7317171 raises the limit to about 20k
  • 6. Re: "xquery" versus "select xmlquery" and passing clauses
    678084 Newbie
    Currently Being Moderated
    I believe that the thread pointed by Marco indeed would help me do the job. I didn't try, though. First, it looks way more complicated than I would expect ("my XQuery is in a file" sounds like a pretty natural scenario to me).

    I was evaluating Oracle to start the development of a new product. Given this issue, and the fact that in just a couple of days of evaluation, I filed two bugs related to the XQuery engine (7354071.992 and 20067666.6), I'll just give up on Oracle. I have tried at least two products (Berkeley DB XML and X-Hive/DB) that are much easier to use for XQuery. If I can get the performance I need, I'll probably go with one of them.

    Still thanks to both of you for your help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points