14 Replies Latest reply: Aug 10, 2013 1:11 PM by odie_63 Branched from an earlier discussion. RSS

    Re: weird behaviour xml query

    Jason_(A_Non)

      From the XML DB FAQ (#5 in the announcement list)

      How to I use namespaces with XMLQuery() ?
      How do I declare namespace prefix mapping with XMLTable() ?


      Your XML has a default namespace associated with it, so you will need to supply the default namespace to XMLTable/XQuery as well.  Also the XPaths in the COLUMNS clause are relative to the XPath for the XMLTable itself, so I adjusted that as well. The below returns rows, but I did not verify it is what you desired.  This should give you a start at least.

      select a.*, b.*
         from cas_nummers,  -- changed
              xmltable
              (
                       XMLNamespaces(default 'http://echa.europa.eu/schemas/ecInventory'),  -- added
                       '/ECSubstanceInventory/ecSubstances/ECSubstance'
                       passing cas_nummers.object_value  -- changed
                       columns
                       creationDate varchar2(30) path '@creationDate',
                       status varchar2(20) path '@status',
                       ecnumber  varchar2(20) path 'ecNumber',  -- changed
                       casnumber varchar2(20) path 'casNumber',  -- changed
                       molecularFormula varchar2(20) path 'molecularFormula',  -- changed
                       namelist xmltype path 'ecNames'  -- changed
              ) a,
              xmltable
              (
                       XMLNamespaces(default 'http://echa.europa.eu/schemas/ecInventory'),  -- added
                       'ecNames'  -- changed
                       passing a.namelist
                       columns
                       ecName      varchar2(5) path '.'  -- changed
              ) b
      
        • 1. Re: weird behaviour xml query
          HenkB

          Thanks a lot, I did missed the default namespace statement and your're right about the relative path of the columns... (forgot to chop the path :-( )

          • 2. Re: weird behaviour xml query
            HenkB

            I have replace the file test1.xml with a file of about 28MB with about 100000 records(say casNumbers)

            I have created a table with the columns
            casnumber,ecnumber, molecularformula, cname, status,creationdate

             

            I use the following plsql anohymous block

            .

            declare

            cursor c0
            is
            select to_date(substr(a.creationdate,1,10),'yyyy-mm-dd') as creationdate
                 , decode(a.status,'active',1,0) as status
                 , a.ecnumber
                 , a.casnumber
                 , a.molecularformula
                 , b.ecName
               from cas_nummers,
                    xmltable 
                    ( 
                             XMLNamespaces(default 'http://echa.europa.eu/schemas/ecInventory'),
                             '/ECSubstanceInventory/ecSubstances/ECSubstance' 
                             passing cas_nummers.object_value 
                             columns 
                             creationDate varchar2(30) path '@creationDate'
                             status varchar2(20) path '@status'
                             ecnumber  varchar2(20) path 'ecNumber', 
                             casnumber varchar2(20) path 'casNumber', 
                             molecularFormula varchar2(20) path 'molecularFormula', 
                             namelist xmltype path 'ecNames' 
                    ) a, 
                    xmltable 
                    ( 
                             XMLNamespaces(default 'http://echa.europa.eu/schemas/ecInventory'),  -- added 
                             'ecNames'  -- changed 
                             passing a.namelist 
                             columns 
                             ecname      varchar2(50) path '.'  -- changed 
                    ) b
            ;
            v_errm varchar2(200);
            i pls_integer;
            begin
               for r_casnr in c0 loop
               begin
                  insert into csa_cas_nummers
                  ( casnummer_id
                  , cas_nummer
                  , ec_nummer 
                  , stofnaam
                  , moleculair_formule
                  , dd_creatie
                  , status  
                  , volgnr
                  )
                  values
                  (csa_cas_nummers_seq.nextval
                  ,r_casnr.casnumber
                  ,r_casnr.ecnumber
                  , trim(r_casnr.stofnaam)
                  , r_casnr.molecularformula
                  , r_casnr.creationdate
                  , r_casnr.status  
                  , 0
                  );
                if mod(i,1000) = 0 then
                   commit;
                end if;
                exception
                   when others then
                      v_errm :=substr(sqlerrm,1,200);
                      insert into foute_casnrs
                      values
                      (r_casnr.casnumber, v_errm);
                      dbms_output.put_line( r_casnr.casnumber||' -> '||sqlerrm);       
               end;
               end loop;
            end;

             

             

            The strange thing is that it stops without error at +/- 38000 records (when i do select count(1) from cas_nummers and no records in the table foute_casnrs)

            I have check some cas_nummers which is in the xml file but not in the tcas_nummers table....

             

            Any idea what is wrong with my code?

             

            Thanks in advance,

             

            Henk

            • 3. Re: weird behaviour xml query
              Jason_(A_Non)

              How many rows does the SQL in the CURSOR return if you run it by itself?

               

              If you remove the entire exception handling block, does it show an error?

               

              Why are you not doing this as a simple

              INSERT INTO csa_cas_nummers (...columns_list...) SELECT ...rest of cursor...;

              SQL statement?

              • 4. Re: weird behaviour xml query
                odie_63

                Hi Henk,

                Any idea what is wrong with my code?

                A cursor loop with a COMMIT inside, maybe?

                 

                Have you tried a single INSERT SELECT with a single commit at the end?

                For some reasons, if you must catch exceptions in the process, use DML Error Logging feature.

                • 5. Re: weird behaviour xml query
                  HenkB

                  Hi Jason,

                   

                  Yes I did try without a cursor loop but then I get a sql error: "cannot read more data from socket" .That is why I used a cursor loop with an exception block to try to catch the error.

                   

                  When I run this on oracle11gr2 it runs without problems and I get the 100000+ records. But I need it to run on oracle10gr2

                   

                  The xml table cas_nummers is created like CREATE TABLE cas_nummers OF XMLType;

                   

                  I did read somewhere that it has something to do with oracle in 10g it try to read the xml in memory and in 11g not... The insert in cas_nummers is without problems. My xml file is not that big...

                   


                  Henk

                  • 6. Re: weird behaviour xml query
                    Marco Gralike

                    HenkB wrote:

                     

                    Yes I did try without a cursor loop but then I get a sql error: "cannot read more data from socket" .

                     

                     

                    You probably hit a bug in that version with XMLTABLE in that version. Try using TABLE(XMLSEQUENCE(EXTRACT,.........))) instead (http://www.liberidu.com/blog/2007/11/27/the-use-of-namespaces-within-xmltable-or-the-tablexmlsequenceextract-constuct/)

                    • 7. Re: weird behaviour xml query
                      HenkB

                      HI,

                       

                      I did try with the following query

                      select rownum

                          , extractvalue(value(b), 'ECSubstance/casNumber','xmlns="http://echa.europa.eu/schemas/ecInventory"') as casnumber

                          , extractvalue(value(b), 'ECSubstance/ecNumber','xmlns="http://echa.europa.eu/schemas/ecInventory"') as ecNumber

                          , extractvalue(value(b), 'ECSubstance/ecNames/ecName','xmlns="http://echa.europa.eu/schemas/ecInventory"') as ecName

                          , extractvalue(value(b), 'ECSubstance/molecularFormula','xmlns="http://echa.europa.eu/schemas/ecInventory"') as molecularformula

                          , to_date(substr(extractvalue(value(b), 'ECSubstance/@creationDate','xmlns="http://echa.europa.eu/schemas/ecInventory"'),1,10), 'yyyy-mm-dd') as creationDate

                          , decode(extractvalue(value(b), 'ECSubstance/@status','xmlns="http://echa.europa.eu/schemas/ecInventory"'), 'active',1,0) as status

                          , 0

                         from cas_nummers a

                            , table(xmlsequence(extract(a.object_value,'/ECSubstanceInventory/ecSubstances/ECSubstance','xmlns="http://echa.europa.eu/schemas/ecInventory"'))) b;

                       

                      It succesfully insert it without socket error but not everything is inserted...., only 40672 records. It should be more than 100000 records. Query not correct?

                       

                      Henk

                      • 8. Re: weird behaviour xml query
                        Marco Gralike

                        Could be that some bits do not have a namespace reference.

                        • 9. Re: weird behaviour xml query
                          Marco Gralike

                          By the way, before I forget, which database version do you use regarding 10gR2 (please mention all digits)

                          • 10. Re: weird behaviour xml query
                            HenkB

                            version 10.2.0.3 windows 7

                            • 11. Re: weird behaviour xml query
                              HenkB

                              No, If I do it in 11gr2 then I get all the records. It seems that 10gr2 xml implementation has some quirks and not reliable.

                               

                              btw: if i do insert into table select * from xml  it takes for hours but when I do it using a cursor (like the example above) it is quite fast just about 1 minute, even in 11gr2!

                              • 12. Re: weird behaviour xml query
                                odie_63

                                Is it possible you share one of your "big" test file ?

                                Or maybe it's OK if we extend the sample you gave to a sufficient amount of records?

                                 

                                Without a test case, we can't really comment on what's really going on.

                                 

                                 

                                Thanks.

                                • 13. Re: weird behaviour xml query
                                  HenkB

                                  Hi Odie,

                                   

                                  Recap results:

                                  In Oracle11r2 all the query inserts works (xmltable and xmlsequence) and I have the correct number of results.

                                   

                                  I notice it is NOT the insert to the tabel is wrong but the insert in the xml table i.e. cas_numbers. The table cas_numbers was created like create table cas_numbers of xmltype;

                                  The xml file is inserted like:

                                  insert into cas_numbers

                                    VALUES (XMLType(bfilename('CAS_XML', 'substances.xml'),  nls_charset_id('AL32UTF8')));

                                   

                                  if I query the to a specific casnumber  then it return no rows selected. If I do it in Oracle11 then I found it. Logically the bfilename() insert is not completely correct in 10.2.0.3...

                                   

                                  I have created a different table cas2_number(xmlcol xmltype) but it gives the same results...

                                  Is there another way to insert xml file in table cas_numbers?

                                   

                                  Henk


                                  • 14. Re: weird behaviour xml query
                                    odie_63

                                    Hi Henk,

                                     

                                    Logically the bfilename() insert is not completely correct in 10.2.0.3...

                                    That's your interpretation.

                                    Again, without a test case, we (well, at least I) cannot say anything for sure.

                                     

                                    You can try to load the file in two steps to see if it makes a difference, using :

                                    1. DBMS_LOB.loadClobFromFile
                                    2. INSERT INTO <table> VALUES ( XMLType( lob_var ) )