Oracle xmltype to display as column

Question

My xmltype data set will be as below:-

Record 1:

<row id=“1”>     
<c1>test|10</c1>   
<c2>teste|20</c2>    
</row>   

Record 2:

<row id=“2”>
<c1>2test|10</c1>
<c2>2teste|20</c2>
</row>

I need to display data in the below format, Also column will not be fixed it wil but the value in the columns will be in same syntax.

Id   Name    Value
------------------
1    test    10
1    teste   20
2    2test   10
2    2teste  20

Answer 1

You can use xmltable for transform the xml to tabular form. Here, I have added a root element <R> and some other test elements. You may also get the value as is to the SELECT and then use substr() (instead of tokenize()).

select x.id, x.key, x.val from xmltable (
      '//row/*'

      passing xmltype (
      '<R>
        <row id="1">
          <c1>test|10</c1>
          <c2>teste|20</c2>
        </row> 
        <row id="2">
          <c1>2test|10</c1>
          <c2>2teste|20</c2>
          <c11>2test|11</c11>
          <c22>2teste|22</c22>
          <c33>2teste</c33>
          <c44>|44</c44>
          <c55></c55>
        </row>
      </R>') 

      columns 
        id number        path './../@id',
        key varchar2(30) path 'fn:tokenize(., "\|")[1]',
        val varchar2(30) path 'fn:tokenize(., "\|")[2]'
    ) x

            ID  KEY                             VAL                           
    ----------  ------------------------------  ------------------------------
             1  test                            10                              
             1  teste                           20                              
             2  2test                           10                              
             2  2teste                          20                              
             2  2test                           11                              
             2  2teste                          22                              
             2  2teste                                                          
             2                                  44                              
             2                                                                  

    9 rows selected.