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