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


How to Query a XML Column

edited Sep 23, 2015 7:05AM in Text


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/>




  • 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

  • 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.