oracle xml to column

Question

User-583959464 posted

i am using oracle 12g. and my table structure has two column, 1st is ID of the record, 2nd column is defined as XMLTYPE.

now the data stored in XMLTYPE is like a xml format. but i don't know how can i write a sql to retrieve the tag to column.

below is my example.

<row id='1'>
<c1>Name</c1>
<c4>Gender</c4>
<c4 m='2'>F</c4>
<c4 m='3'>M</c4>
<c6>Address</c6>
<c8>Nationality</c8>
</row>

every data stored in XMLTYPE is not fixed lenght. how can i write a sql to convert above xml format to column as below expected result.

 

Name    Gender     F     M    Address     Nationality

Answer 1

User-583959464 posted

i know the exactvalue can do that, but it can't return if xml has 3 node name as  <c4>, <c4 m='2'>,<c4 m='3'>

how can i get the value from xmltype?

anyone know?

Answer 2

User269602965 posted

https://docs.oracle.com/database/121/ADXDB/xdb04cre.htm#ADXDB0400

examples here.

Answer 3

User-583959464 posted
how can I select all the following node in a cell per record

<c4>Gender</c4>
<c4 m='2'>F</c4>
<c4 m='3'>M</c4>