Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (xml) AS
SELECT '<a>
   <b>
      <id>1</id>
      <comment-list>
        <comment>
     <value>asd</value>
        </comment>
        <comment>
     <value>23</value>
        </comment>
        <comment>
     <value>5436</value>
        </comment>
        <comment>
     <value>123g</value>
        </comment>
      </comment-list>
  </b>
  <b>
  <id>2</id>
      <comment-list>
        <comment>
     <value>asd</value>
        </comment>
        <comment>
     <value>23</value>
        </comment>
        <comment>
     <value>5436</value>
        </comment>
        <comment>
     <value>123g</value>
        </comment>
      </comment-list>
</b>
    </a>' FROM DUAL;
1 rows affected

SELECT x.*
FROM   table_name t
       CROSS APPLY XMLTABLE(
         '/a/b/comment-list/comment'
         PASSING XMLTYPE(t.xml)
         COLUMNS
           id NUMBER PATH './../../id',
           comm VARCHAR2(10) PATH './value'
         
       ) x;


ID
COMM


1
asd

1
23

1
5436

1
123g

2
asd

2
23

2
5436

2
123g


SELECT u'123' FROM DUAL;


U'123'


123