How to use Oracle XMLTable syntax

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;