LEFT OUTER JOIN with XMLTABLE doesn't work?

Question

Here is the sample query-

WITH empdata AS (SELECT xmltype ('<office>
                            <emp>
                                <empno>1</empno>
                                <ename>Abraham</ename>
                                <deptno>10</deptno>
                            </emp>
                            <emp>
                                <empno>2</empno>
                                <ename>Alexander</ename>
                                <deptno>10</deptno>
                            </emp>
                            <emp>
                                <empno>3</empno>
                                <ename>Benjamin</ename>
                                <deptno>20</deptno>
                            </emp>
                            <emp>
                                <empno>4</empno>
                                <ename>Bradley</ename>
                                <deptno>20</deptno>
                            </emp>
                         </office>') AS xcol FROM dual),
     dept AS
         (SELECT 10 deptno, 'Accounting' dname FROM dual
          UNION ALL
          SELECT 20, 'Broking' FROM dual
          UNION ALL
          SELECT 30, 'HR' FROM dual)
SELECT d.dname, e.ename, e.empno
  FROM dept d
       CROSS JOIN empdata e_data
       LEFT OUTER JOIN
       xmltable (
           'office/emp'
           PASSING e_data.xcol
           COLUMNS deptno NUMBER (28, 0) PATH 'deptno',
                   ename VARCHAR2 (10) PATH 'ename',
                   empno NUMBER (28, 0) PATH 'empno') e
           ON d.deptno = e.deptno;

Result I'm getting-

DNAME      ENAME           EMPNO
---------- ---------- ----------
Accounting Abraham             1
Accounting Alexander           2
Broking    Benjamin            3
Broking    Bradley             4

Why isn't the third row from dept, i.e. that of HR isn't showing in the result set? Ideally according to the rules of a LEFT JOIN all the records from the table in the left should show. Why is that one being filtered out?

Answer 1

You have the outer join and cross join the wrong way round. You need to cross-join empdata to xmltable as a subquery, and use that subquery (inline view) as the target for the outer join:

...
SELECT d.dname, e.ename, e.empno
  FROM dept d
       LEFT OUTER JOIN
       (
         SELECT x.*
          FROM empdata e_data
               CROSS JOIN 
               xmltable (
                   'office/emp'
                   PASSING e_data.xcol
                   COLUMNS deptno NUMBER (28, 0) PATH 'deptno',
                           ename VARCHAR2 (10) PATH 'ename',
                           empno NUMBER (28, 0) PATH 'empno') x
        ) e
           ON d.deptno = e.deptno;

DNAME      ENAME           EMPNO
---------- ---------- ----------
Accounting Abraham             1
Accounting Alexander           2
Broking    Benjamin            3
Broking    Bradley             4
HR