How to parse xml data from a table and iterate each xml for showing inside perticular data

Question

I have a table as following

table 1
=======
ID          |   XMLcol
----------------------------
1              <?xml version="...</users>
2              <?xml version="...</users>

In my each xml data in the XMLcol contains informations about severel users. As a example firs row 1 contain three users. row 2 contains two users.

row 1 xml data

<?xml version="1.0"?>
                       <users>
                         <user>
                               <name>user1</name>
                           </user>
                           <user>
                              <name>user2</name>
                          </user>
                       <user>
                              <name>user3</name>
                          </user>
                      </users>

row 2 xml data

<?xml version="1.0"?>
                       <users>
                         <user>
                               <name>user4</name>
                           </user>
                           <user>
                              <name>user5</name>
                          </user>
                      </users>

I want to create a view by allow to repeating the ID and show the each user name in another column as follows. so final view should be like this,

ID          |  name
----------------------------
1              user1
1              user2
1              user3
2              user4
2              user5

Is there anyway to do such a kind of thing?

Answer 1

Use an XMLTABLE:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table1 ( id, xml ) AS
SELECT 1, '<?xml version="1.0"?>
           <users>
             <user><name>user1</name></user>
             <user><name>user2</name></user>
             <user><name>user3</name></user>
           </users>' FROM DUAL UNION ALL
SELECT 2, '<?xml version="1.0"?>
           <users>
             <user><name>user4</name></user>
             <user><name>user5</name></user>
           </users>' FROM DUAL

Query 1:

SELECT t.id,
       x.name
FROM   table1 t
       CROSS JOIN
       XMLTABLE(
         '/users/user'
         PASSING XMLType( t.xml )
         COLUMNS name VARCHAR2(200) PATH 'name'
       ) x

Results:

| ID |  NAME |
|----|-------|
|  1 | user1 |
|  1 | user2 |
|  1 | user3 |
|  2 | user4 |
|  2 | user5 |