6 Replies Latest reply: Jan 8, 2009 1:23 PM by 678084 RSS

    "xquery" versus "select xmlquery" and passing clauses

    678084
      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
          Marco Gralike
          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-Oracle
            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
              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
                Marco Gralike
                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-Oracle
                  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
                    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.