This discussion is archived
1 2 3 Previous Next 30 Replies Latest reply: Oct 16, 2012 1:04 PM by Reneet RSS

ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -

Reneet Newbie
Currently Being Moderated
I am new to XML DB. I am trying to insert records from an xML file into a database table. I have a directory. My procedure took about 20 minutes when I first run it but now it takes over 3 hours. Its only inserting about 90,000 records. L looked at my session from the enterprie manager and seemed to have this error ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got - xs:string although my data was inserted. Wondering what this could be. Below is my oracle db version and a sample of my procedure. I have about 110 nodes into 110 columns

--------------------------------------------------------------------------------------------------------------------------------------
My Procedure
------------------------------------------------------------------------------------------------------------------------------------
DECLARE
--filename varchar2(50):= 'myfile.xml';
the_data xmltype := xmltype( bfilename('DIR_MYDIR_XML','myfile.xml'), nls_charset_id('AL32UTF8') );
BEGIN

insert into table_xml_load (
localid,
caseNo,
address1,
address4,
postcode,
familyName,
firstName,
dobEst,
dob,
gender,
activityDate,
ecg,
cpox
)
select x.*
from xmltable(
'for $p in /ClientImport/episode
return
for $i in $p/descendant::episodeDetails
return
for $j in $i/following-sibling::dailyInterventions[1]/dailyIntervention
return element r {$p, $i, $j}'
passing the_data
columns
LOCALID varchar2(10) path 'episode/@localID',
caseNo     varchar2(100)     path      'episodeDetails/caseNo',
address1     varchar2(100)     path      'episodeDetails/address1',
address4     varchar2(100)     path      'episodeDetails/address4',
postcode     varchar2(100)     path      'episodeDetails/postcode',
familyName     varchar2(100)     path      'episodeDetails/familyName',
firstName     varchar2(100)     path      'episodeDetails/firstName',
dobEst     varchar2(100)     path      'episodeDetails/dobEst',
dob     date     path      'episodeDetails/dob',
gender     varchar2(100)     path      'episodeDetails/gender',
activityDate     date     path      'dailyIntervention/activityDate',
ecg     varchar2(100)     path      'dailyIntervention/ecg',
cpox     varchar2(100)     path      'dailyIntervention/cpox'
) x
;

END;
/

--------------------------------------------------------------------------------------------------------------------------------------
Sample XML
------------------------------------------------------------------------------------------------------------------------------------

<ClientImport xmlns:xsi="http://www.w3.org/2001/XMLSchema">

     <episode localID="P333456">
          <episodeDetails>
               <caseNo>0000000</caseNo>
               <address1>where Person1 live</address1>
               <address4>Do they live there</address4>
               <postcode>PP1 2PP</postcode>
               <familyName>Person1</familyName>
               <firstName>PersonF1</firstName>
               <dobEst>0</dobEst>
          </episodeDetails>
          <dailyInterventions>
          <dailyIntervention>
               <activityDate>2012-08-23</activityDate>
               <ecg>true</ecg>
               <cpox>true</cpox>
               <invVentET>true</invVentET>
               <invVentTT>false</invVentTT>
          </dailyIntervention>
          <dailyIntervention>
               <activityDate>2012-08-24</activityDate>
               <ecg>true</ecg>
               <cpox>true</cpox>
               <invVentET>true</invVentET>
               <invVentTT>false</invVentTT>
          </dailyIntervention>
          <dailyIntervention>
               <activityDate>2012-08-25</activityDate>
               <ecg>true</ecg>
               <cpox>true</cpox>
               <invVentET>false</invVentET>
               <invVentTT>false</invVentTT>
          </dailyIntervention>
          <dailyIntervention>
               <activityDate>2012-08-26</activityDate>
               <ecg>true</ecg>
               <cpox>true</cpox>
               <invVentET>true</invVentET>
               <invVentTT>false</invVentTT>
          </dailyIntervention>
          <dailyIntervention>
               <activityDate>2012-08-27</activityDate>
               <ecg>true</ecg>
               <cpox>true</cpox>
               <invVentET>true</invVentET>
               <invVentTT>false</invVentTT>
          </dailyIntervention>
          <dailyIntervention>
               <activityDate>2012-08-28</activityDate>
               <ecg>true</ecg>
               <cpox>true</cpox>
               <invVentET>true</invVentET>
               <invVentTT>false</invVentTT>
          </dailyIntervention>
          <dailyIntervention>
               <activityDate>2012-08-29</activityDate>
               <ecg>true</ecg>
               <cpox>true</cpox>
               <invVentET>true</invVentET>
               <invVentTT>false</invVentTT>
          </dailyIntervention>
          </dailyInterventions>
     </episode>
<episode localID="20120410">
          <episodeDetails>
               <caseNo>0000001</caseNo>
               <address1>where Person2 Lives</address1>
               <address4>Street Person2</address4>
               <postcode>XX1 1XX</postcode>
               <familyName>Person2</familyName>
               <firstName>Person2 A</firstName>
               <dobEst>0</dobEst>
          </episodeDetails>
          <dailyInterventions>
          <dailyIntervention>
               <activityDate>2012-08-23</activityDate>
               <noCrit>true</noCrit>
          </dailyIntervention>
          </dailyInterventions>
     </episode>
     <episode localID="20120411">
     <episodeDetails>
          <caseNo>0000002</caseNo>
          <address1>Where Person3 Lives</address1>
          <address4>Person 3 Street</address4>
          <postcode>WW1 1WW</postcode>
          <familyName>Person3</familyName>
          <firstName>Person 3 B</firstName>
          <dobEst>0</dobEst>
     </episodeDetails>
     <dailyInterventions>
     <dailyIntervention>
          <activityDate>2012-08-24</activityDate>
          <ecg>true</ecg>
          <cpox>true</cpox>
          <invVentET>true</invVentET>
          <invVentTT>false</invVentTT>
     </dailyIntervention>
</dailyInterventions>
     </episode>
</ClientImport>






Oracle DB Version : Enterprise Edition Release 11.1.0.7.0

ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got - xs:string

Edited by: Reneet on Oct 9, 2012 3:16 PM to replace a censored word

Edited by: Reneet on Oct 9, 2012 7:51 PM to add Sample XML
  • 1. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Don't know how big or complex the XML is, but my gut feeling tells me that it is probably faster to

    - insert data via bfilename or SQLLoader into a ETL kind of table with one XML column or an XMLType Table, based on XML Binary Securefile XML
    - then do the SELECT INTO into the TABLE_XML_LOAD or the destination relational table.

    You are doing all the XML parsing in memory currently which can't be fast...


    I have loaded, at home, 20GB of XML data into a Binary XML column securefile, unoptimized without extra tricks like partitioning etc, via SQL Loader (direct load/parallel) in less than 30 minutes (in a database with an MAX_TARGET of 800 GB)

    Edited by: Marco Gralike on Oct 9, 2012 3:12 PM
  • 2. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    If possible, BTW, update the database version to 11.2.0.3.0 ...
  • 3. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    Jason_(A_Non) Expert
    Currently Being Moderated
    Follow Marco's suggestion about creating a SECUREFILE BINARY XML storage based XMLType column and then run your SELECT statement against it to populate the primary table. This will also allow you to run the SELECT statement independently and may give you more insight into the error.

    Using your method, I had a similar process running in 16+ minutes. I knocked it down to just under a second using what Marco suggested and that included the time to store the CLOB (the data came from a web service) into the table and INSERT... SELECT the data into the final table.
  • 4. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    odie_63 Guru
    Currently Being Moderated
    My 50 cents...

    I suspect the following-sibling axis will also disable the streaming XPath evaluation that binary XML normally provides.

    I can't comment much on the XQuery logic, but do you really need to use a following-sibling axis?

    Can you post a sample XML as well?
  • 5. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    Reneet Newbie
    Currently Being Moderated
    Marco - Unfortunately I am not in a position to upgrade. The decision does not lie with me. I could ask but could take a couple of months to get that done.

    Odie_63 - I am very new to XML DB and therefore very open minded in exploring options that will be quicker. I have submitted a sample XML. Need to add that there are several other siblings, but I am not interested in them.
  • 6. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    Jason_(A_Non) Expert
    Currently Being Moderated
    In terms of a simpler query, this gives the same results, well it returns one extra column of data since I did not filter it out in the SELECT list.
    WITH temp_table AS (
    SELECT XMLTYPE('<ClientImport xmlns:xsi="http://www.w3.org/2001/XMLSchema">
       <episode localID="P333456">
          <episodeDetails>
             <caseNo>0000000</caseNo>
             <address1>where Person1 live</address1>
             <address4>Do they live there</address4>
             <postcode>PP1 2PP</postcode>
             <familyName>Person1</familyName>
             <firstName>PersonF1</firstName>
             <dobEst>0</dobEst>
          </episodeDetails>
          <dailyInterventions>
             <dailyIntervention>
                <activityDate>2012-08-23</activityDate>
                <ecg>true</ecg>
                <cpox>true</cpox>
                <invVentET>true</invVentET>
                <invVentTT>false</invVentTT>
             </dailyIntervention>
             <dailyIntervention>
                <activityDate>2012-08-24</activityDate>
                <ecg>true</ecg>
                <cpox>true</cpox>
                <invVentET>true</invVentET>
                <invVentTT>false</invVentTT>
             </dailyIntervention>
             <dailyIntervention>
                <activityDate>2012-08-25</activityDate>
                <ecg>true</ecg>
                <cpox>true</cpox>
                <invVentET>false</invVentET>
                <invVentTT>false</invVentTT>
             </dailyIntervention>
             <dailyIntervention>
                <activityDate>2012-08-26</activityDate>
                <ecg>true</ecg>
                <cpox>true</cpox>
                <invVentET>true</invVentET>
                <invVentTT>false</invVentTT>
             </dailyIntervention>
             <dailyIntervention>
                <activityDate>2012-08-27</activityDate>
                <ecg>true</ecg>
                <cpox>true</cpox>
                <invVentET>true</invVentET>
                <invVentTT>false</invVentTT>
             </dailyIntervention>
             <dailyIntervention>
                <activityDate>2012-08-28</activityDate>
                <ecg>true</ecg>
                <cpox>true</cpox>
                <invVentET>true</invVentET>
                <invVentTT>false</invVentTT>
             </dailyIntervention>
             <dailyIntervention>
                <activityDate>2012-08-29</activityDate>
                <ecg>true</ecg>
                <cpox>true</cpox>
                <invVentET>true</invVentET>
                <invVentTT>false</invVentTT>
             </dailyIntervention>
          </dailyInterventions>
       </episode>
       <episode localID="20120410">
          <episodeDetails>
             <caseNo>0000001</caseNo>
             <address1>where Person2 Lives</address1>
             <address4>Street Person2</address4>
             <postcode>XX1 1XX</postcode>
             <familyName>Person2</familyName>
             <firstName>Person2 A</firstName>
             <dobEst>0</dobEst>
          </episodeDetails>
          <dailyInterventions>
             <dailyIntervention>
                <activityDate>2012-08-23</activityDate>
                <noCrit>true</noCrit>
             </dailyIntervention>
          </dailyInterventions>
       </episode>
       <episode localID="20120411">
          <episodeDetails>
             <caseNo>0000002</caseNo>
             <address1>Where Person3 Lives</address1>
             <address4>Person 3 Street</address4>
             <postcode>WW1 1WW</postcode>
             <familyName>Person3</familyName>
             <firstName>Person 3 B</firstName>
             <dobEst>0</dobEst>
          </episodeDetails>
          <dailyInterventions>
             <dailyIntervention>
                <activityDate>2012-08-24</activityDate>
                <ecg>true</ecg>
                <cpox>true</cpox>
                <invVentET>true</invVentET>
                <invVentTT>false</invVentTT>
             </dailyIntervention>
          </dailyInterventions>
       </episode>
    </ClientImport>') the_data
      from dual)
    -- The above simulates having your data stored in a table in your DB.
    select x.*, y.*
    from temp_table,
         xmltable('/ClientImport/episode'
                  PASSING temp_table.the_data
                  COLUMNS
                  LOCALID    varchar2(10)  path '@localID',
                  caseNo     varchar2(100) path 'episodeDetails/caseNo',
                  address1   varchar2(100) path 'episodeDetails/address1',
                  address4   varchar2(100) path 'episodeDetails/address4',
                  postcode   varchar2(100) path 'episodeDetails/postcode',
                  familyName varchar2(100) path 'episodeDetails/familyName',
                  firstName  varchar2(100) path 'episodeDetails/firstName',
                  dobEst     varchar2(100) path 'episodeDetails/dobEst',
                  dob        date          path 'episodeDetails/dob',
                  gender     varchar2(100) path 'episodeDetails/gender',
                  dailyInter XMLTYPE       PATH 'dailyInterventions/dailyIntervention') x,
         xmltable('/dailyIntervention'
                  passing x.dailyInter
                  COLUMNS
                  activityDate date          path 'activityDate',
                  ecg          varchar2(100) path 'ecg',
                  cpox         varchar2(100) path 'cpox') y;
    As stated, that returns the same number of rows and the data all appears to match up. If you follow our above advice and store the data into a table, it should take advantage of streaming XPath evaluation as Marc (odie_63) mentioned. This would mean it would perform faster than the current method you are using.
  • 7. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    I overlooked the following-sibling axis bit. Thanks for noticing.

    Just to be sure I tested on the latest and greatest database version ;-) and as you were expecting (and guessed the same after your remark), whatever you try it will do a full XMLTABLE EVALUATION in PGA. This XMLTABLE EVALUATION blows-up the PGA due to the following-sibling axis bit. In the end my session (SQL Developer) gets killed due to lack of resources (PGA limit).

    "Funny" enough this is the EXPLAIN PLAN most effective execution plan (COSTS 32)
  • 8. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    In the meantime tried something along the same way (in my latest greatest version database)

    :-)

    Anyway...

    I did the following. Copy and pasted the original XML content over and over again, up to a total of 1099892 lines in my 1 XML document. Made the whole thing wellformed XML again by adding a root element "<rowset>". Total size of this 1 XML document, called rowset.xml, was now 27.4 MB. Via the following I loaded and started experimenting
    create or replace directory XMLTMP as '/tmp';
    
    drop table xml_data_tmp purge;
    
    CREATE TABLE "XML_DATA_TMP" 
      (     "DATAEXTRACT" "XMLTYPE"
      ) 
      NOCOMPRESS LOGGING
      TABLESPACE "USERS" 
     XMLTYPE COLUMN "DATAEXTRACT" STORE AS SECUREFILE BINARY XML;
    
    -- select dbms_metadata.get_ddl('TABLE','XML_DATA_TMP') from dual;
    
    drop table table_xml_load purge;
    
    CREATE TABLE TABLE_XML_LOAD
    (localid varchar2(10),
    caseNo varchar2(100),
    address1 varchar2(100),
    address4 varchar2(100),
    postcode varchar2(100),
    familyName varchar2(100),
    firstName varchar2(100),
    dobEst varchar2(100),
    dob date,
    gender varchar2(100),
    activityDate date,
    ecg varchar2(100),
    cpox  varchar2(100)
    )
    TABLESPACE USERS;
    
    -- select dbms_metadata.get_ddl('TABLE','TABLE_XML_LOAD') from dual;
    
    INSERT into XML_DATA_TMP
    (DATAEXTRACT)
    VALUES
    (XMLTYPE(bfilename('XMLTMP','rowset.xml'),NLS_CHARSET_ID('AL32UTF8')));
    An insert of this 24.7 MB XML document with the name "rowset.xml" takes about 10 seconds to insert (MAX_TARGET=800)
    Total System Global Area                 835104768                                
    Fixed Size                               2265928                                  
    Variable Size                            620760248                                
    Database Buffers                         209715200                                
    Redo Buffers                             2363392      
    
                                
    NAME                                               TYPE        VALUE                                                                                                
    -------------------------------------------------- ----------- -------------------------
    memory_max_target                                  big integer 800M                    
                                                                                      
    I took a while before I got the query right (I think/hope), although not sure due to different interpretation/function of a "following-sibling" axis...
    insert into table_xml_load (
    localid,
    caseNo,
    address1,
    address4,
    postcode,
    familyName,
    firstName,
    dobEst,
    dob,
    gender,
    activityDate,
    ecg,
    cpox
    )
    select x1.LOCALID
         , x2.caseNo
         , x2.address1
         , x2.address4
         , x2.postcode
         , x2.familyName
         , x2.firstName
         , x2.dobEst
         , x2.dob
         , x2.gender
         , x3.activityDate
         , x3.ecg
         , x3.cpox
    from XML_DATA_TMP XDT
       , xmltable('/rowset/ClientImport/episode'
                  passing XDT.DATAEXTRACT
                  columns
                    LOCALID varchar2(10) path '@localID',
                    episodeDetails    xmltype path '.'  ,
                    dailyIntervention xmltype path '.'
                ) x1
      , xmltable('episode'
                  passing X1.episodeDetails
                  columns
                    caseNo varchar2(100) path 'episodeDetails/caseNo',
                    address1 varchar2(100) path 'episodeDetails/address1',
                    address4 varchar2(100) path 'episodeDetails/address4',
                    postcode varchar2(100) path 'episodeDetails/postcode',
                    familyName varchar2(100) path 'episodeDetails/familyName',
                    firstName varchar2(100) path 'episodeDetails/firstName',
                    dobEst varchar2(100) path 'episodeDetails/dobEst',
                    dob date path 'episodeDetails/dob',
                    gender varchar2(100) path 'episodeDetails/gender'
                ) x2  
      , xmltable('episode/dailyInterventions[1]/dailyIntervention'
                  passing X1.dailyIntervention
                  columns
                    activityDate date path 'activityDate',
                    ecg varchar2(100) path 'ecg',
                    cpox varchar2(100) path 'cpox'
                ) x3  
    ;
    The insert of 98,010 records takes 3.6 seconds to complete.

    Edited by: Marco Gralike on Oct 10, 2012 12:01 AM
  • 9. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Difference in "COSTS" and effect of the SELECT statements
    - XMLTABLE EVALUATION with FULL TABLE ACCESS
    - COSTS = 32
    - Session gets killed by the database after exhausting the PGA
    
    SQL Error: ORA-00028: your session has been killed
    ORA-00028: your session has been killed
    ORA-04036: ...
    
    select x.*
    from XML_DATA_TMP XDT
       , xmltable(
    'for $p in /rowset/ClientImport/episode
    return
    for $i in $p/descendant::episodeDetails
    return
    for $j in $i/following-sibling::dailyInterventions[1]/dailyIntervention
    return element r {$p, $i, $j}'
    passing XDT.DATAEXTRACT
    columns
    LOCALID varchar2(10) path 'episode/@localID',
    caseNo varchar2(100) path 'episodeDetails/caseNo',
    address1 varchar2(100) path 'episodeDetails/address1',
    address4 varchar2(100) path 'episodeDetails/address4',
    postcode varchar2(100) path 'episodeDetails/postcode',
    familyName varchar2(100) path 'episodeDetails/familyName',
    firstName varchar2(100) path 'episodeDetails/firstName',
    dobEst varchar2(100) path 'episodeDetails/dobEst',
    dob date path 'episodeDetails/dob',
    gender varchar2(100) path 'episodeDetails/gender',
    activityDate date path 'dailyIntervention/activityDate',
    ecg varchar2(100) path 'dailyIntervention/ecg',
    cpox varchar2(100) path 'dailyIntervention/cpox'
    ) x;
    - XPATH EVALUATION with FULL TABLE ACCESS and NESTED LOOPS
    - COSTS = 221868
    - (SQL Developer output): Fetched 50 rows in 0.039 seconds
    
    select x.LOCALID
         , x.caseNo
         , x.address1
         , x.address4
         , x.postcode
         , x.familyName
         , x.firstName
         , x.dobEst
         , x.dob
         , x.gender
         , y.activityDate
         , y.ecg
         , y.cpox
    from XML_DATA_TMP XDT,
         xmltable('/rowset/ClientImport/episode'
                  PASSING xdt.dataextract
                  COLUMNS
                  LOCALID    varchar2(10)  path '@localID',
                  caseNo     varchar2(100) path 'episodeDetails/caseNo',
                  address1   varchar2(100) path 'episodeDetails/address1',
                  address4   varchar2(100) path 'episodeDetails/address4',
                  postcode   varchar2(100) path 'episodeDetails/postcode',
                  familyName varchar2(100) path 'episodeDetails/familyName',
                  firstName  varchar2(100) path 'episodeDetails/firstName',
                  dobEst     varchar2(100) path 'episodeDetails/dobEst',
                  dob        date          path 'episodeDetails/dob',
                  gender     varchar2(100) path 'episodeDetails/gender',
                  dailyInter XMLTYPE       PATH 'dailyInterventions/dailyIntervention') x,
         xmltable('/dailyIntervention'
                  passing x.dailyInter
                  COLUMNS
                  activityDate date          path 'activityDate',
                  ecg          varchar2(100) path 'ecg',
                  cpox         varchar2(100) path 'cpox') y;
    - XPATH EVALUATION with Filter predicate (is not null) and FULL TABLE ACCESS and NESTED LOOPS
    - COSTS = 11114
    - (SQL Developer output): Fetched 50 rows in 0.041 seconds
    
    select x1.LOCALID
         , x2.caseNo
         , x2.address1
         , x2.address4
         , x2.postcode
         , x2.familyName
         , x2.firstName
         , x2.dobEst
         , x2.dob
         , x2.gender
         , x3.activityDate
         , x3.ecg
         , x3.cpox
    from XML_DATA_TMP XDT
       , xmltable('/rowset/ClientImport/episode'
                  passing XDT.DATAEXTRACT
                  columns
                    LOCALID varchar2(10) path '@localID',
                    episodeDetails xmltype path '.'
    ,               dailyIntervention xmltype path '.'
                ) x1
      , xmltable('episode'
                  passing X1.episodeDetails
                  columns
                    caseNo varchar2(100) path 'episodeDetails/caseNo',
                    address1 varchar2(100) path 'episodeDetails/address1',
                    address4 varchar2(100) path 'episodeDetails/address4',
                    postcode varchar2(100) path 'episodeDetails/postcode',
                    familyName varchar2(100) path 'episodeDetails/familyName',
                    firstName varchar2(100) path 'episodeDetails/firstName',
                    dobEst varchar2(100) path 'episodeDetails/dobEst',
                    dob date path 'episodeDetails/dob',
                    gender varchar2(100) path 'episodeDetails/gender'
                ) x2  
      , xmltable('episode/dailyInterventions[1]/dailyIntervention'
                  passing X1.dailyIntervention
                  columns
                    activityDate date path 'activityDate',
                    ecg varchar2(100) path 'ecg',
                    cpox varchar2(100) path 'cpox'
                ) x3                  
    ;
    Edited by: Marco Gralike on Oct 10, 2012 12:08 AM
  • 10. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    odie_63 Guru
    Currently Being Moderated
    We need to know more about the actual cardinalities, but based on the sample document I would have written the exact same query as Jason's.
    No descendant or sibling-related axes, no positional predicate.

    @Irene :

    Could you describe the cardinalities in the XML document? i.e. how many <dailyInterventions> and <episodeDetails> per <episode>?
  • 11. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Trying the initial "ALL IN ONE" approach
    insert into table_xml_load (
    localid,
    caseNo,
    address1,
    address4,
    postcode,
    familyName,
    firstName,
    dobEst,
    dob,
    gender,
    activityDate,
    ecg,
    cpox
    )
    select x1.LOCALID
         , x2.caseNo
         , x2.address1
         , x2.address4
         , x2.postcode
         , x2.familyName
         , x2.firstName
         , x2.dobEst
         , x2.dob
         , x2.gender
         , x3.activityDate
         , x3.ecg
         , x3.cpox
    from xmltable('/rowset/ClientImport/episode'
                  passing (XMLTYPE(bfilename('XMLTMP','rowset.xml'),NLS_CHARSET_ID('AL32UTF8')))
                  columns
                    LOCALID varchar2(10) path '@localID',
                    episodeDetails    xmltype path '.'  ,
                    dailyIntervention xmltype path '.'
                ) x1
      , xmltable('episode'
                  passing X1.episodeDetails
                  columns
                    caseNo varchar2(100) path 'episodeDetails/caseNo',
                    address1 varchar2(100) path 'episodeDetails/address1',
                    address4 varchar2(100) path 'episodeDetails/address4',
                    postcode varchar2(100) path 'episodeDetails/postcode',
                    familyName varchar2(100) path 'episodeDetails/familyName',
                    firstName varchar2(100) path 'episodeDetails/firstName',
                    dobEst varchar2(100) path 'episodeDetails/dobEst',
                    dob date path 'episodeDetails/dob',
                    gender varchar2(100) path 'episodeDetails/gender'
                ) x2  
      , xmltable('episode/dailyInterventions[1]/dailyIntervention'
                  passing X1.dailyIntervention
                  columns
                    activityDate date path 'activityDate',
                    ecg varchar2(100) path 'ecg',
                    cpox varchar2(100) path 'cpox'
                ) x3  
    ;
    Those same 98,010 rows were now inserted after 263 seconds.

    (4:38 Minutes - 2 x XMLTABLE EVALUATION, TABLE CONVERNTIONAL LOAD, NESTED LOOPS - COSTS = 18 million++) ...
  • 12. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    I thought about doing the same as Jason (indeed based on the sample document), but was I was guessing there was a explicit purpose/reason why the sibling-related axis etc stuff was used. I was surprised, that breaking it up in three bits instead of 2, was actually more efficient. Still have to check if one of the new features kicked in though.

    Edited by: Marco Gralike on Oct 9, 2012 10:50 PM
  • 13. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    Jason_(A_Non) Expert
    Currently Being Moderated
    I'm still amazed at how fast your first attempt was (< 4 seconds) to load 98K rows. It is amazing what Oracle pulls off with binary XML parsing. Was that on your laptop as well?

    For an updated comparison, you should try loading the XML into a CLOB and passing that XMLType(CLOB) into the SQL.

    Edited by: A_Non on Oct 9, 2012 2:52 PM
    I often go for the simple assumptions and then adjust when they are wrong.
  • 14. Re: ORA-19224: XPTY0004 - XQuery static type mismatch: expected - node()* got -
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Insert on your statements takes 7.2 seconds to complete for the 98K rows
    insert into table_xml_load (
    localid,
    caseNo,
    address1,
    address4,
    postcode,
    familyName,
    firstName,
    dobEst,
    dob,
    gender,
    activityDate,
    ecg,
    cpox
    )
    select x.LOCALID
         , x.caseNo
         , x.address1
         , x.address4
         , x.postcode
         , x.familyName
         , x.firstName
         , x.dobEst
         , x.dob
         , x.gender
         , y.activityDate
         , y.ecg
         , y.cpox
    from XML_DATA_TMP XDT,
         xmltable('/rowset/ClientImport/episode'
                  PASSING xdt.dataextract
                  COLUMNS
                  LOCALID    varchar2(10)  path '@localID',
                  caseNo     varchar2(100) path 'episodeDetails/caseNo',
                  address1   varchar2(100) path 'episodeDetails/address1',
                  address4   varchar2(100) path 'episodeDetails/address4',
                  postcode   varchar2(100) path 'episodeDetails/postcode',
                  familyName varchar2(100) path 'episodeDetails/familyName',
                  firstName  varchar2(100) path 'episodeDetails/firstName',
                  dobEst     varchar2(100) path 'episodeDetails/dobEst',
                  dob        date          path 'episodeDetails/dob',
                  gender     varchar2(100) path 'episodeDetails/gender',
                  dailyInter XMLTYPE       PATH 'dailyInterventions/dailyIntervention') x,
         xmltable('/dailyIntervention'
                  passing x.dailyInter
                  COLUMNS
                  activityDate date          path 'activityDate',
                  ecg          varchar2(100) path 'ecg',
                  cpox         varchar2(100) path 'cpox') y;
    Environment a VirtualBox machine (OEL 5U8 64B) with 1 CPU defined and 2048 MB memory. Physical machine OSX 3.4 Ghz Intel Core I7 with 12 GB 1333 MHz DDR (27 inch :-) )
1 2 3 Previous Next

Legend

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