Oracle Version :
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
please help me in Parsing this XML
With xml_tab AS (Select
'<?xml version="1.0" encoding="UTF-8"?>
<ODM xmlns="http://www.disc.org/ns/odm/v1.3" xmlns:mdsol="http://www.mdsol.com/ns/odm/metadata" xmlns:xlink="http://www.w3.org/1999/xlink" FileType="Snapshot" FileOID="e718ff79-df0f-4a28-83c9-dc4af069ec69" CreationDateTime="2019-08-28T17:25:03.604-00:00" ODMVersion="1.3">
<employees>
<employee empno="7369" ename="SMITH" job="CLERK" hiredate="17-DEC-1980"/>
<employee empno="7499" ename="ALLEN" job="SALESMAN" hiredate="20-FEB-1981"/>
<employee empno="7521" ename="WARD" job="SALESMAN" hiredate="22-FEB-1981"/>
<employee empno="7566" ename="JONES" job="MANAGER" hiredate="02-APR-1981"/>
<employee empno="7654" ename="MARTIN" job="SALESMAN" hiredate="28-SEP-1981"/>
<employee empno="7698" ename="BLAKE" job="MANAGER" hiredate="01-MAY-1981"/>
<employee empno="7782" ename="CLARK" job="MANAGER" hiredate="09-JUN-1981"/>
<employee empno="7788" ename="SCOTT" job="ANALYST" hiredate="19-APR-1987"/>
<employee empno="7839" ename="KING" job="PRESIDENT" hiredate="17-NOV-1981"/>
<employee empno="7844" ename="TURNER" job="SALESMAN" hiredate="08-SEP-1981"/>
<employee empno="7876" ename="ADAMS" job="CLERK" hiredate="23-MAY-1987"/>
<employee empno="7900" ename="JAMES" job="CLERK" hiredate="03-DEC-1981"/>
<employee empno="7902" ename="FORD" job="ANALYST" hiredate="03-DEC-1981"/>
<employee empno="7934" ename="MILLER" job="CLERK" hiredate="23-JAN-1982"/>
</employees>
</ODM>' Xml_Data From Dual)
SELECT xt.*
FROM xml_tab x,
XMLTABLE(XMLNAMESPACES(default 'http://www.disc.org/ns/odm/v1.3'),
'/ODM'
PASSING xmltype.createxml(x.xml_data)
COLUMNS
FileType VARCHAR2(100) PATH '@FileType',
FileOID VARCHAR2(100) PATH '@FileOID',
empno VARCHAR2(100) PATH 'ODM/employees/employee@empno'
) xt;