This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,116 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

How to Query a XML Column

37f36731-7735-4a0e-a981-4aed6a0ebefa
edited Sep 23, 2015 7:05AM in Text

Hi

I have a table which has 2 columns

1) Id      int pk

2) def    XMLTYPE

The xml stored will be of like

<?xml version="1.0"?><br/><dP><br/><aD><br/><Id>1</Id><br/><aN>Lat</aN><br/></aD><br/><br/><des><br/><dE><br/><dn>ISB</dn><br/><tn>A_DE</tn><br/><dE>String</dE><br/><bDO>Low</bDO><br/><tdo>Admin</tdo><br/></dE><br/><br/><dE><br/><dn>ISB</dn><br/><tn>A_DE</tn><br/><dE>String</dE><br/><bDO>Hig</bDO><br/><tdo>adm</tdo><br/><dQ>A</dQ><br/></dE><br/><br/></des><br/>

</dP>

Tagged:

Answers

  • 37f36731-7735-4a0e-a981-4aed6a0ebefa
    edited Sep 22, 2015 2:55PM

    I want to do a search in node <des>...</des> of the xml

    I tried doing

    CREATE INDEX index_xmltype ON tablename (columnname) INDEXTYPE IS CTXSYS.CONTEXT;

    SELECT id  FROM tablename WHERE contains(columnname, 'String INPATH (/dp/des)') > 0;

    this works fine but creates 4 tables as $MR_ndex_xmltype$I, $MR_ndex_xmltype$K and so on

    37f36731-7735-4a0e-a981-4aed6a0ebefa
  • kevinUCB
    kevinUCB Member Posts: 194 Bronze Badge
    edited Sep 23, 2015 7:05AM

    Yes, those tables (and related indexes) are required for text search to work.

    Depending on your specific needs, there are a couple of ways to tell the text engine to handle the sections and elements of your XML. You can look at the manual for the details on AUTO_SECTION_GROUP, XML_SECTION_GROUP, and PATH_SECTION_GROUP.

This discussion has been closed.