Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 535.7K On-Premises Infrastructure
- 138.1K Analytics Software
- 38.6K Application Development Software
- 5.6K Cloud Platform
- 109.3K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
Search within an attribute section
Hi folks,
I can't figure how to to search for text within a specific section.
Here is my XML:
<section id="Salutation" caption="En-tête et salutations">
<paragraph id="Salutation" phlitemid="Salutation">
<p>Faxmed de sortie Le patient susnommé a séjourné dans notre service</p>
</paragraph>
</section>
<section id="Anamnese_Faxmed" caption="Anamnèse">
<paragraph id="TexteLibre" phlitemid="TexteLibre"/>
<paragraph id="CHUV_AsmtPg_Entree_20170628 16:51:00"
phlitemid="CHUV_AsmtPg_Entree">Anamnèse à l'entrée
</paragraph>
</section>
Here is my INDEX CREATION script
[START]
-- PREFERENCES SETTING
EXEC CTX_DDL.DROP_PREFERENCE('LETTER_DS')
-- PARAMETER: DATASTORE
EXEC CTX_DDL.CREATE_PREFERENCE('LETTER_DS', 'DIRECT_DATASTORE')
-- PARAMETER: SECTIONER
EXEC CTX_DDL.DROP_SECTION_GROUP('LETTRE_SECGRP')
EXEC CTX_DDL.CREATE_SECTION_GROUP('LETTRE_SECGRP', 'AUTO_SECTION_GROUP')
-- PARAMETER: LEXER
EXEC CTX_DDL.DROP_PREFERENCE('LETTRE_LXR')
exec CTX_DDL.CREATE_PREFERENCE('LETTRE_LXR', 'BASIC_LEXER')
-- ATTRIBUTE: LEXER
exec CTX_DDL.SET_ATTRIBUTE('LETTRE_LXR', 'PRINTJOINS', '_')
-- CREATE INDEX
CREATE INDEX myIndex on myTable(myCLOBCOLUMN)
INDEXTYPE is ctxsys.context
PARAMETERS ('
datastore LETTER_DS
section group LETTRE_SECGRP
lexer LETTRE_LXR
memory 1g
')
parallel 16;
[END]
I'm trying to ping every record that has the word 'susnommé' within the section having id="Salutation"
AUTO_SECTION_GROUP does the job as it even creates in the I$ table the following attribute sections (token type = 5):
Here are the QUERIES I have done so far
- SELECT * FROM myTable WHERE CONTAINS(myCLOBCOLUMN,'susnommé')>0; --> A record is returned
- SELECT * FROM GBE_TEST_LETTRE_XSLT WHERE CONTAINS(TEXT,'susnommé WITHIN SALUTATION')>0; --> No record is returned (even if SALUTATION appers in the I$ table with token type = 4
- SELECT * FROM GBE_TEST_LETTRE_XSLT WHERE CONTAINS(TEXT,'SALUTATION WITHIN [email protected] AND anamnèse')>0; --> A record is returned while anamnèse is not in the SALUTATION section
Any help would be greatly appreciated
Best Answer
-
I finally implemented the following solution:
XMLTable command with XQUERY extracting section and paragraph information and inserting it in specific columns (and therefore being able to query on it).
SELECT CHUV_OBSV_CLOB.obsv_id, details.SECTION_ID, details.SECTION_POS, details.SECTION_CAPTION, details.PARAGRAPH_ID, details.PARAGRAPH_ITEMID, details.PARAGRAPH_POS,
details.PARAGRAPH_CONTENT
FROM HDM.CHUV_OBSV_CLOB,
XMLTABLE('
for $section at $sec_pos in /levelone/body/section, $paragraph at $par_pos in $section/paragraph
where $section/@phlused="yes"
return
<section sectionID="{fn:data($section/@ID)}" sectionPos = "{$sec_pos}">
{
$section/caption
}
<paragraph paragraphID="{fn:data($paragraph/@ID)}" phlitemid="{fn:data($paragraph/@phlitemid)}" paragraphPos = "{$par_pos}">
{$paragraph/content/text()}
</paragraph>
</section>
' passing XMLTYPE.CREATEXML(OBS_TXT_VAL)
columns
SECTION_ID VARCHAR(50) path '@sectionID',
SECTION_POS INTEGER path '@sectionPos',
SECTION_CAPTION VARCHAR(200) path 'caption',
PARAGRAPH_ID VARCHAR(50) path 'paragraph/@paragraphID',
PARAGRAPH_ITEMID VARCHAR(50) path 'paragraph/@phlitemid',
PARAGRAPH_POS INTEGER path 'paragraph/@paragraphPos',
PARAGRAPH_CONTENT CLOB path 'paragraph/text()'
) details
Answers
-
GB_CHUV wrote:Here are the QUERIES I have done so farSELECT * FROM myTable WHERE CONTAINS(myCLOBCOLUMN,'susnommé')>0; --> A record is returnedSELECT * FROM GBE_TEST_LETTRE_XSLT WHERE CONTAINS(TEXT,'susnommé WITHIN SALUTATION')>0; --> No record is returned (even if SALUTATION appers in the I$ table with token type = 4SELECT * FROM GBE_TEST_LETTRE_XSLT WHERE CONTAINS(TEXT,'SALUTATION WITHIN [email protected] AND anamnèse')>0; --> A record is returned while anamnèse is not in the SALUTATION sectionAny help would be greatly appreciated
You tried 2 version to retrieve the section:
WITHIN SALUTATION
WITHIN [email protected]
For the second you got a result but you claim it wrong since anamnèse is not in this section.
But the answer is correct anyway since anamnèse in in the text, resp. content itself.
So WITHIN [email protected] seems to be the correct way to ask for the section.
-
I finally implemented the following solution:
XMLTable command with XQUERY extracting section and paragraph information and inserting it in specific columns (and therefore being able to query on it).
SELECT CHUV_OBSV_CLOB.obsv_id, details.SECTION_ID, details.SECTION_POS, details.SECTION_CAPTION, details.PARAGRAPH_ID, details.PARAGRAPH_ITEMID, details.PARAGRAPH_POS,
details.PARAGRAPH_CONTENT
FROM HDM.CHUV_OBSV_CLOB,
XMLTABLE('
for $section at $sec_pos in /levelone/body/section, $paragraph at $par_pos in $section/paragraph
where $section/@phlused="yes"
return
<section sectionID="{fn:data($section/@ID)}" sectionPos = "{$sec_pos}">
{
$section/caption
}
<paragraph paragraphID="{fn:data($paragraph/@ID)}" phlitemid="{fn:data($paragraph/@phlitemid)}" paragraphPos = "{$par_pos}">
{$paragraph/content/text()}
</paragraph>
</section>
' passing XMLTYPE.CREATEXML(OBS_TXT_VAL)
columns
SECTION_ID VARCHAR(50) path '@sectionID',
SECTION_POS INTEGER path '@sectionPos',
SECTION_CAPTION VARCHAR(200) path 'caption',
PARAGRAPH_ID VARCHAR(50) path 'paragraph/@paragraphID',
PARAGRAPH_ITEMID VARCHAR(50) path 'paragraph/@phlitemid',
PARAGRAPH_POS INTEGER path 'paragraph/@paragraphPos',
PARAGRAPH_CONTENT CLOB path 'paragraph/text()'
) details