-- create table

--CREATE TABLE COMPANY (SYM varchar2(12) PRIMARY KEY, TEXT XMLTYPE);
CREATE TABLE COMPANY (ID number PRIMARY KEY, TEXT XMLTYPE);
commit;

sqlldr parfile=loader.par readsize=10000000 bindsize=10000000 direct=t


exec DBMS_OUTPUT.PUT_LINE('Start Time: ' || to_char(SYSDATE, 'dd-Mon-yyyy hh24:mi:ss'));
 
-- Index column 'text' of table 'company'
create index company_idx on company(text)
  indextype is ctxsys.context
  parameters('lexer mylex filter ctxsys.null_filter section group xmlgroup');
 
exec DBMS_OUTPUT.PUT_LINE('End Time: ' || to_char(SYSDATE, 'dd-Mon-yyyy hh24:mi:ss'));

 
select score(1) s, id,
        extractValue(text, '/CompanyRecord/Ticker') Symbol,
	extractValue(text, '/CompanyRecord/CompanyName') Company,
	extractValue(text, '/CompanyRecord/Industry') Industry,
	extractValue(text, '/CompanyRecord/BusinessSummary') BI
from company
where CONTAINS(text, 'Health WITHIN BusinessSummary', 1) > 0
ORDER BY score(1) DESC;


--drop index company_idx;

--drop table company;

