This discussion is archived
14 Replies Latest reply: Aug 10, 2013 11:11 AM by odie_63 Branched from an earlier discussion. RSS

Re: weird behaviour xml query

Jason_(A_Non) Expert
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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) Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE Director
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE Director
    Currently Being Moderated

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

  • 9. Re: weird behaviour xml query
    Marco Gralike Oracle ACE Director
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    version 10.2.0.3 windows 7

  • 11. Re: weird behaviour xml query
    HenkB Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 ) )