how to use xmltable in oracle?

action and object on your example aren't on the same level, so your query has to perform additional steps. Here's an example:

SQL> create table users (id number, profile xmltype);Table created.SQL> insert into users values (1, XMLTYPE('<profile>  2      <subject>I  3         <action>like  4             <object>sports</object>  5             <object>music</object>  6         </action>  7      </subject>  8  </profile>'));1 row created.SQL> select u.id, x.action, x.object.getStringVal()  2    from users u,  3         XMLTABLE('/profile/subject/action'  4                  passing u.profile  5                  columns action VARCHAR2(30) PATH 'text()',  6                          object XMLTYPE PATH 'object') x;ID  ACTION  X.OBJECT.GETSTRINGVAL()--- ------- --------------------------------------------------1   like    <object>sports</object> <object>music</object>

As you can see we got the node, not really what you want so we add an XMLTABLE:

SQL> select u.id, x.action, y.object  2    from users u,  3         XMLTABLE('/profile/subject/action'  4                  passing u.profile  5                  columns action VARCHAR2(30) PATH 'text()',  6                          object XMLTYPE PATH 'object') x,  7         XMLTABLE('/object'  8                  passing x.object  9                  columns object VARCHAR2(30) PATH '.') y;ID  ACTION  OBJECT--- ------- -------1   like    sports1   like    music