4 Replies Latest reply: Mar 26, 2014 4:38 PM by Daiane RSS

    XQuery query over just one single xml file returns data but query over lots of XML didn't return any data.

    Daiane

      Hello,

       

      Please, help me.

      I am depending on this query working fine to complete a academic task.

       

      On the Oracle xml database Express version 11.2.0.2.0 I used data from wikipedia to populate my table of XML files and W3C use case as a query model (XQuery and XPath Full Text 1.0 Use Cases).

      I have a table with 26.416 split xml files into some xml columns, it is about 512 MB.

       

      CREATE TABLE tb_wikipedia_inex (DOC VARCHAR(30) NOT NULL,

                                      XML XMLTYPE,

                                      PRIMARY KEY (DOC)

                                      );

       

      I want to perform a query (full text) on all those files but when I execute a xquery it retuns nothing or warnings. Unfortunatelly I understand nothing from trace file.

       

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

      SQLPLUS:

      XQUERY  fn:collection("oradb:/HR/TB_INEX2009")//article//language  [ora:contains(., "java") > 0]

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

      TRACE FILE:

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

      System altered.

       

      Elapsed: 00:00:42.08

       

      Session altered.

       

      Elapsed: 00:00:00.00

       

      PL/SQL procedure successfully completed.

       

      Elapsed: 00:00:00.00

      Elapsed: 00:00:00.05

       

      Execution Plan

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

      Plan hash value: 1500681423                                                                      

                                                                                                       

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

                                                          

                                                                                                       

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

      ----------                                                                                       

                                                                                                       

      | Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)|

      Time     |                                                                                       

                                                                                                       

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

      ----------                                                                                       

                                                                                                       

      |   0 | SELECT STATEMENT                    |                        |  8168 | 16336 |    29   (0)|

      00:00:01 |                                                                                       

                                                                                                       

      |   1 |  COLLECTION ITERATOR XMLSEQ FETCH   | XMLSEQUENCEFROMXMLTYPE |  8168 | 16336 |    29   (0)|

      00:00:01 |                                                                                       

                                                                                                       

      |   2 |   SORT AGGREGATE                    |                        |     1 |     2 |            |

               |                                                                                       

                                                                                                       

      |*  3 |    COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |   408 |   816 |    66  (57)|

      00:00:01 |                                                                                       

                                                                                                       

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

      ----------                                                                                       

                                                                                                       

                                                                                                       

      Predicate Information (identified by operation id):                                              

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

                                                                                                       

         3 - filter(SYS_XMLCONTAINS(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(SYS_XQCON2SEQ(VALUE(KOKBF$

      ))                                                                                               

                                                                                                       

                    ,'/language/text()'),1,50),50,1,0),'java')>0)                                      

                                                                                                       

      Note                                                                                             

      -----                                                                                            

         - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)       

       

       

      PL/SQL procedure successfully completed.

       

      Elapsed: 00:00:00.00

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

       

      How can I execute xquery over a bunch of xml files to obtain xml data?

       

      Regards,

       

      Daiane

        • 1. Re: XQuery query over just one single xml file returns data but query over lots of XML didn't return any data.
          odie_63

          Hi,

           

          I'm still trying to find some time to look at your test case there :

          Re: How can I fix the error "ORA-19022: Unoptimized XML construct detected." ?

           

          The other problem I have is that I can't download all the files at once from Dropbox (I can view them one by one though), do I need to be logged in to do that?

           

          Regarding the issue you're describing here, could you give a sample XML file, or just its name (in Dropbox), that's supposed to return data?

           

          BTW, if you've included SET AUTOTRACE TRACEONLY EXPLAIN in your script, it doesn't display the results ("TRACEONLY"), just the plan.

          Remove the command or use SET AUTOTRACE ON EXPLAIN if you still want to see the plan.

          • 2. Re: XQuery query over just one single xml file returns data but query over lots of XML didn't return any data.
            Daiane

            Hello Odie_63,

             

            Thank you for answering.

             

            Sorry for that, I thought that you could be able to download the files from Dropbox. I guess you don't have to be logged to do that may be because of the quantity of files. It took a lot time to upload those files.

             

            On the first try to perform those queries I used 'SET AUTOTRACE ON ' and the result was nothing, the file q1.lst was blank.

             

             

            Dropbox path file:

            ~/xmlfiles1/24131.xml

             

            Link to the file:

            https://www.dropbox.com/s/xk3hfolnnbntvqk/24131.xml

             

            Queries:

            XQUERY  fn:collection("oradb:/HR/TB_INEX2009")//article//language  [ora:contains(text(), "java") > 0]

             

            XQUERY  fn:collection("oradb:/HR/TB_INEX2009")//article//language  [ora:contains(., "java") > 0]

             

            Link to the other files:

            https://www.dropbox.com/s/7um2g2bilelpbdw/filesQ1.txt

             

            I am doing a backup of the table and as soon as possible I will post here to you. I think if you see the real table might be better for test.

             

            Regards,

             

            Daiane

            • 3. Re: XQuery query over just one single xml file returns data but query over lots of XML didn't return any data.
              Daiane

              Link to the table backup file:

              https://www.dropbox.com/s/9ya61pz6gi4chxn/backup_tb_inex2009.sql

               

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

              --  DDL for Table TB_INEX2009

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

               

                CREATE TABLE "HR"."TB_INEX2009"

                 (    "DOC" VARCHAR2(30 BYTE),

                  "XML" "XMLTYPE"

                 ) SEGMENT CREATION IMMEDIATE

                PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

                STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

                TABLESPACE "USERS"

              XMLTYPE COLUMN "XML" STORE AS SECUREFILE BINARY XML (

                TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192

                NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES

                STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

                PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ALLOW NONSCHEMA DISALLOW ANYSCHEMA ;

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

              --  DDL for Index SYS_C009368

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

               

                CREATE UNIQUE INDEX "HR"."SYS_C009368" ON "HR"."TB_INEX2009" ("DOC")

                PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

                STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

                TABLESPACE "USERS" ;

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

              --  DDL for Index ITXT_TB_INEX2009

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

               

                CREATE INDEX "HR"."ITXT_TB_INEX2009" ON "HR"."TB_INEX2009" ("XML")

                 INDEXTYPE IS "CTXSYS"."CONTEXT" ;

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

              --  DDL for Index IXML_TB_INEX2009

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

               

                CREATE INDEX "HR"."IXML_TB_INEX2009" ON "HR"."TB_INEX2009" ("XML")

                 INDEXTYPE IS "XDB"."XMLINDEX" ;

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

              --  Constraints for Table TB_INEX2009

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

               

                ALTER TABLE "HR"."TB_INEX2009" ADD PRIMARY KEY ("DOC")

                USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

                STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

                TABLESPACE "USERS"  ENABLE;

                ALTER TABLE "HR"."TB_INEX2009" MODIFY ("DOC" NOT NULL ENABLE);

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

               

              Am I using the correct indexes?

               

              Regards,

               

              Daiane.

               

              Mensagem editada por: Daiane

              • 4. Re: XQuery query over just one single xml file returns data but query over lots of XML didn't return any data.
                Daiane

                Hello,

                 

                I was trying to find what is the problem, but without success.

                 

                The most simple test don't work.

                 

                So, I created a new table with few xml files to reproduce the query.

                 

                 

                Below are my scripts:

                 

                *************************************************************************************

                XML COLUMN  SQLPLUS

                *************************************************************************************

                 

                CREATE TABLE HR.tb_xml (DOC VARCHAR(30) NOT NULL,

                 

                                                           XML XMLTYPE,PRIMARY KEY (DOC))

                 

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

                 

                CREATE INDEX HR.Ixml_tb_xml ON HR.tb_xml (XML)  INDEXTYPE IS XDB.XMLINDEX  PARAMETERS ('PATHS (INCLUDE(//article

                 

                                                                                                                                   //article/physical_entity/communicator/person/compiler/causal_agent/language))')

                 

                /

                 

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

                 

                CREATE INDEX Itxt_tb_xml1  ON HR.tb_xml (XML)  INDEXTYPE IS CTXSYS.CONTEXT;

                 

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

                 

                Shell:

                 

                nohup sqlldr daiane/daiane DIRECT=false PARALLEL=false control = load_xml.ctl;

                 

                *********************************************************************************

                XML TABLE SQLPLUS:

                *****************************************************************************

                 

                create table livros of XMLTYPE;

                 

                Shell:

                 

                nohup sqlldr daiane/daiane DIRECT=false PARALLEL=false control = load_xmltb.ctl;

                 

                *********************************************************************************

                 

                XML Files:

                 

                https://www.dropbox.com/sh/sn26jq6fjoi4635/0wjJI6tT4h

                 

                Queries:

                 

                for XQUERY  fn:collection("oradb:/HR/TB_XML")//article/physical_entity/communicator/person/compiler/causal_agent/language/writer/header/categories/category [ora:contains(., "PHP") > 0]

                 

                /

                 

                XQUERY  fn:collection("oradb:/HR/TB_XML")//article/physical_entity/communicator/person/compiler/causal_agent/language/writer/header/title [ora:contains(text(), "PHP") > 0]

                 

                /

                 

                for XQUERY  fn:collection("oradb:/HR/LIVROS")//article/physical_entity/communicator/person/compiler/causal_agent/language/writer/header/categories/category [ora:contains(., "PHP") > 0]

                 

                /

                 

                XQUERY  fn:collection("oradb:/HR/LIVROS")//article/physical_entity/communicator/person/compiler/causal_agent/language/writer/header/title [ora:contains(text(), "PHP") > 0]

                 

                /

                 

                What is wrong with these queries?

                 

                Regards,

                 

                Daiane