Forum Stats

  • 3,814,028 Users
  • 2,258,809 Discussions
  • 7,892,504 Comments

Discussions

Search within an attribute section

GB_CHUV
GB_CHUV Member Posts: 4 Red Ribbon
edited Jun 30, 2020 5:38AM in Text

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):

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

Best Answer

  • GB_CHUV
    GB_CHUV Member Posts: 4 Red Ribbon
    edited Jun 30, 2020 5:37AM 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

  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    edited Mar 10, 2020 8:34AM
    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
    GB_CHUV Member Posts: 4 Red Ribbon
    edited Jun 30, 2020 5:37AM 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