How to Query a XML Column

37f36731-7735-4a0e-a981-4aed6a0ebefa

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>

37f36731-7735-4a0e-a981-4aed6a0ebefa

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

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.