Skip to Main Content

SQL & PL/SQL

XML Section group

user13117585Apr 10 2022

Hello again,

Could anyone advise on how to use data sections for xml section groups? Here is a quick sample:


CREATE TABLE books(
 id number, 
 val clob
);


insert into books(id, val) VALUES(1, q'#
<book>
  <references>
    <ref name="isbn10">0123456789</ref>
    <ref name="isbn13">0123456789123</ref>
  </references>
  <titles>
    <title language="english">
      The Lord of the rings
    </title>
    <title language="spanish">
      El Señor de los Anillos
    </title>
    <title language="german">
      Der Herr der Ringe
    </title>
  </titles>
</book>
#');

insert into books(id, val) VALUES(2, q'#
<book>
  <references>
    <ref name="isbn10">9876543210</ref>
    <ref name="isbn13">3219876543321</ref>
  </references>
  <titles>
    <title language="english">
      Harry Potter and the Order of the Phoenix
    </title>
    <title language="italian">
      Harry Potter e l'ordine della fenice
    </title>
    <title language="dutch">
      Harry Potter en de Orde van de Feniks
    </title>
  </titles>
</book>
#');
COMMIT;

Since it's XML content, I would like to use the XML SECTION GROUP and create the sections like this:

BEGIN
 ctx_ddl.create_section_group ('books_xml_sg', 'XML_SECTION_GROUP');
END;
/

BEGIN
 ctx_ddl.add_field_section('books_xml_sg', 'reference', 'book/references/ref/name', true);
END;
/

BEGIN
 ctx_ddl.add_attr_section('books_xml_sg', 'language', 'book/titles/title@language');
END;
/

And I create my index like this:

CREATE INDEX books_idx on books (val) indextype is ctxsys.context
PARAMETERS 
('DATASTORE CTXSYS.DIRECT_DATASTORE
 SECTION GROUP books_xml_sg
 MEMORY 5M
 '
); 

When I do a SELECT, it works:  

SELECT id, score(99)
 FROM books
 WHERE contains(val, 'harry', 99) > 1
 ORDER BY score(99) DESC;

But, when I take the example from this link:

set long 500000
set pagesize 0

variable displayrs clob;

declare
  rs clob;
begin
  ctx_query.result_set('books_idx', 'harry',
  '<ctx_result_set_descriptor>
	 	<count/>
	 	<group sdata="reference" topn="5" sortby="count" order="desc">
  	 	<count exact="true"/>
	 	</group>
	 	<group sdata="language" topn="3" sortby="value" order="asc">
	 	<count exact="true"/>
	 	</group>
	 	</ctx_result_set_descriptor>',
	 	rs);

/* Pretty-print the result set (rs) for display purposes.
It is not required if you are going to manipulate it in XML.*/

  select xmlserialize(Document XMLType(rs) as clob indent size=2) into :displayrs from dual;
  dbms_lob.freetemporary(rs);
end;
/
select :displayrs from dual;

I get the following error:

Error report -
ORA-20000: Oracle Text error:
DRG-50857: oracle error in ctx_query.result_set
ORA-20000: Oracle Text error:
DRG-13600: Syntax error in the result set descriptor at group
DRG-10837: section REFERENCE does not exist

ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.CTX_QUERY", line 853
ORA-06512: at line 4
20000. 00000 - "%s"
*Cause: The stored procedure 'raise_application_error'
  was called which causes this error to be generated.
*Action: Correct the problem as described in the error message or contact
  the application administrator or DBA for more information.


Could anyone help in here and guide me on how to use sections in XML_SECTION_GROUP?

Regards,

Comments
Post Details
Added on Apr 10 2022
2 comments
108 views