I have got a requirement to query against a XMLTYPE content and fetch the records from it.
XMLTable maps the result of an XQuery evaluation into relational rows and columns. So I am going to use this here.
Assume that we have a table called user_events which has two columns event_data and event_date. event_data is a XMLTYPE column. Now I want to query the user_events table and find out the list of users who have used the system for Jan month.
Here is the syntax.
select distinct userName from user_events e, XMLTABLE ('user_events' PASSING e.event_data COLUMNS userName VARCHAR2(50) PATH '/events/user/userName', userId VARCHAR2(10) PATH '/events/user/userId') where event_date >= '01-JAN-18' AND event_date <= '31-JAN-18' order by userName asc;