How to use xmltable in oracle?

Question

I am using Oracle XML DB to create user profiles. I saved user profiles in one XMLTYPE column with other relational columns (id, username, password) in the table. XML has the following format:

<profile> <subject>I <action>like <object>sports</object> ... <object>music</object </action> </subject> </profile> 

I used the following query:

 SELECT * FROM user, XMLTABLE( '//profile' PASSING user.profile return COLUMNS action VARCHAR2(20) PATH '/subject/action', object VARCHAR2(30) PATH '/subject/action/object' ); 

which gives me nothing. How can I make this work work?

Answer 1

action and object in your example are not at the same level, so your request must complete additional steps. Here is 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 node, not what you want, so add 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 sports 1 like music