Search within an attribute section

GB_CHUV

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&#233; a s&#233;journ&#233; 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):

• [email protected]
• [email protected]
• [email protected]
• [email protected]

Here are the QUERIES I have done so far

1. SELECT * FROM myTable WHERE CONTAINS(myCLOBCOLUMN,'susnommé')>0; --> A record is returned
2. 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
3. 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

GB_CHUV

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

chris227

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.

GB_CHUV

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