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

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

    Reneet
      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 -
          Marco Gralike
          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 -
            Marco Gralike
            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)
              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
                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
                  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)
                    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 -
                      Marco Gralike
                      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 -
                        Marco Gralike
                        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 -
                          Marco Gralike
                          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
                            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 -
                              Marco Gralike
                              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 -
                                Marco Gralike
                                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)
                                  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 -
                                    Marco Gralike
                                    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