create table emp (doc XML);

INSERT INTO EMP VALUES ('
    
        
            John
            Doe
        
        344
        55000
    
    
        
            Peter
            Pan
        
        216
        905-416-5004
    
');

 
SELECT X.* 
FROM emp, 
XMLTABLE ('$d/dept/employee' passing doc as "d"
   COLUMNS 
   empID    INTEGER     PATH '@id',
   firstname    VARCHAR(20)     PATH 'name/first',
   lastname     VARCHAR(25)     PATH 'name/last') AS X