create table schematest.dept
(
  deptno  number,
  loc varchar2(100 char),
  dname varchar2(100 char)
);

create table schematest.emp
(
  deptno  number,
  empno  number,
  ename varchar2(100 char),
  sal number
);

declare
xml_var XMLType;
begin
  select XMLType('
  <rowset>
    <employee>
      <deptno>40</deptno>
      <empno>1111</empno>
      <ename>Queen</ename>
      <sal>7000</sal>
    </employee>
    <department>
      <deptno>50</deptno>
      <dname>MARKETING</dname>
      <loc>MOSCOW</loc>
    </department>
    <employee>
      <deptno>40</deptno>
      <empno>2222</empno>
      <ename>Duke</ename>
      <sal>4000</sal>
    </employee>
    <employee>
      <deptno>50</deptno>
      <empno>3333</empno>
      <ename>Earl</ename>
      <sal>6000</sal>
    </employee>
  </rowset>
  ') into xml_var 
  from dual;

  INSERT FIRST
  WHEN empno IS NULL THEN INTO SCHEMATEST.dept ( deptno, dname, loc )
                                        VALUES ( deptno, dname, loc )
  WHEN empno IS NOT NULL THEN INTO SCHEMATEST.emp ( empno, ename, sal, deptno )
                                           VALUES ( empno, ename, sal, deptno )
   
  SELECT  y.dname
        , y.loc
        , y.deptno
        , y.empno
        , y.ename
        , y.sal
  FROM XMLTABLE ('for $i in /ROWSET/(employee|department) return $i'
                 PASSING xml_var
                 COLUMNS   dname   varchar2(100 char) PATH '/department/dname'
                         , loc     VARCHAR2(100 char) PATH '/department/loc' 
                         , deptno  number PATH './deptno'
                         , empno   NUMBER PATH '/employee/empno'
                         , ename   varchar2(100 char) PATH '/employee/ename'
                         , sal     NUMBER PATH '/employee/sal'
                 ) AS y;

  EXCEPTION 
    WHEN NO_DATA_FOUND THEN  DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
    WHEN TOO_MANY_ROWS THEN  DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS'); 
end;

for $i in /rowset/(employee|department) return $i