How to select values from XML type column in oracle 12g

Question

Below is my xml column with name xml_col. I want to select c11

<row id="GHKY5678" xml:space="preserve">
    <c1>ITVC</c1>
    <c2>USD1271500010001</c2>
    <c4>1</c4>
    <c5>USD</c5>
    <c6>275000.00</c6>
    <c7>20211001</c7>
    <c9>008107318011021</c9>
    <c10>Cr.Advice0085962</c10>
    <c11>101100002684</c11>
    <c12>1</c12>
    <c13>USD</c13>
    <c15>20211001</c15>
    <c18>20211001</c18>
</row>

Answer 1

Please try the following solution.

A CTE simulates your real table.

dbfiddle

SQL

with tbl as
(
    select
        XMLType(
        '<row id="GHKY5678" xml:space="preserve">
            <c1>ITVC</c1>
            <c2>USD1271500010001</c2>
            <c4>1</c4>
            <c5>USD</c5>
            <c6>275000.00</c6>
            <c7>20211001</c7>
            <c9>008107318011021</c9>
            <c10>Cr.Advice0085962</c10>
            <c11>101100002684</c11>
            <c12>1</c12>
            <c13>USD</c13>
            <c15>20211001</c15>
            <c18>20211001</c18>
        </row>'
        ) xml_col
    from dual
)
select c11
from   tbl,
       xmltable('/row' 
           PASSING tbl.xml_col 
           COLUMNS c11 VARCHAR2(30) PATH 'c11');

Output

C11
101100002684