I'm trying to execute the following example query (from 9i XML DB Developer's Guide):
select empno, ename from emp e
where e.deptno = d.deptno) AS emplist_t)))
from dept d;
And I keep getting the following error:
ERROR at line 5:
ORA-00902: invalid datatype
Can anyone please let me know what am I doing wrong? I am on database 18.104.22.168.
Thanks for the response. I have couple of doubts - would you mind clearing these out for me please:
1. For Employee element, you've used XMLForest for a bunch of fields and then used XMLElement for Commission. Why is it so? Could we have added commision to the XMLForest only - what difference would it make as per coding standard (the output remains the same in both the cases).
2. The use of .extract('/*') - I executed the query without using this and the result was still the same. Can you please explain the benefit of using this.
Thanks again for all your time.
XMLForest will omit any node where the column contains null, XMLElement will generate an empty element. It's purely a matter of choice.
If you see no difference between with and without extract() it means you are on a pre 22.214.171.124.0 release. Please upgrade.