Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table your_table (doc) as
select xmltype('<RESPONSE>
<INFO>
  <STU_NAME><![CDATA[ Maria ]]></STU_NAME>
  <STU_WARN><![CDATA[ one ]]></STU_WARN>
  <BIRTHDAY><![CDATA[ 2012-03-12 ]]></BIRTHDAY>
</INFO>
<INFO>
  <STU_NAME><![CDATA[ Kevin) ]]></STU_NAME>
  <STU_WARN><![CDATA[ one ]]></STU_WARN>
  <BIRTHDAY><![CDATA[ 2010-07-15 ]]></BIRTHDAY>
</INFO>
</RESPONSE>') from dual
1 rows affected

SELECT t.doc.extract('/RESPONSE/INFO/STU_NAME/text()').getStringVal()
"stu"
FROM your_table t
WHERE  t.doc.extract('/RESPONSE/INFO/STU_NAME/text()').getStringVal()
LIKE '%M%'


stu


<![CDATA[ Maria ]]><![CDATA[ Kevin) ]]>


select x.stu_name, x.birthday,
  trim(stu_name) as stu_name2, to_char(x.birthday,'YYYY-MM-DD') as birthday2
from your_table t
cross join xmltable ('/RESPONSE/INFO' passing t.doc
  columns
    stu_name varchar2(30) path 'STU_NAME',
    birthday date path 'BIRTHDAY'
) x
where x.stu_name like '%M%'


STU_NAME
BIRTHDAY
STU_NAME2
BIRTHDAY2


 Maria
12-MAR-12
Maria
2012-03-12


select xmlquery('/RESPONSE/INFO[contains(BIRTHDAY, "2012-03-12")]/STU_NAME/text()'
  passing doc
  returning content) as stu_name
from your_table t


STU_NAME


<![CDATA[ Maria ]]>


select regexp_replace(
  xmlquery('/RESPONSE/INFO[contains(BIRTHDAY, "2012-03-12")]/STU_NAME/text()'
    passing doc
    returning content),
  '<!\[CDATA\[ *(.*?) *\]\]>', '\1') as stu_name
from your_table t


STU_NAME


Maria