This discussion is archived
4 Replies Latest reply: Jul 7, 2003 4:41 PM by mdrake RSS

XMLElement Query

jakaur Newbie
Currently Being Moderated
Hi All,

I'm trying to execute the following example query (from 9i XML DB Developer's Guide):

select xmlelement("Department",
dept_t(deptno, dname,
cast(multiset(
select empno, ename from emp e
where e.deptno = d.deptno) AS emplist_t)))
as deptxml
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 9.2.0.3.

Thanks,
Jatinder
  • 1. Re: XMLElement Query
    mdrake Expert
    Currently Being Moderated
    Don't mix SQL/XML with UDT


    Eg

    This is the correct way to do this post 9.2

    select
    xmlelement
    (
    "Department",
    xmlForest ( d.dname as "Name", d.loc as "Location" ),
    xmlElement
    (
    "EmployeeList",
    (
    select xmlagg
    (
    xmlelement
    (
    "Employee",
    xmlAttributes ( e.empno as "employeeNumber" ),
    xmlForest( e.ename as "Name", e.job as "Title", e.mgr as "Manager", e.hiredate as "StartDate"),
    xmlElement ( "Commission", e.comm )
    )
    )
    from scott.emp e
    where e.deptno = d.deptno
    )
    )
    ).extract('/*') as XML
    from scott.dept d

    XML
    --------------------------------------------------------------------------------
    <Department>
    <Name>ACCOUNTING</Name>
    <Location>NEW YORK</Location>
    <EmployeeList>
    <Employee employeeNumber="7782">
    <Name>CLARK</Name>
    <Title>MANAGER</Title>
    <Manager>7839</Manager>
    <StartDate>09-JUN-81</StartDate>
    <Commission/>
    </Employee>
    <Employee employeeNumber="7839">
    <Name>KING</Name>
    <Title>PRESIDENT</Title>
    <StartDate>17-NOV-81</StartDate>
    <Commission/>
    </Employee>
    <Employee employeeNumber="7934">
    <Name>MILLER</Name>
    <Title>CLERK</Title>
    <Manager>7782</Manager>
    <StartDate>23-JAN-82</StartDate>
    <Commission/>
    </Employee>
    <Employee employeeNumber="9901">
    <Name>DRAKE</Name>
    <Title>MANAGER</Title>
    <Manager>7839</Manager>
    <StartDate>01-SEP-81</StartDate>
    <Commission>0</Commission>
    </Employee>
    </EmployeeList>
    </Department>

    <Department>
    <Name>RESEARCH</Name>
    <Location>DALLAS</Location>
    <EmployeeList>
    <Employee employeeNumber="7369">
    <Name>SMITH</Name>
    <Title>CLERK</Title>
    <Manager>7902</Manager>
    <StartDate>17-DEC-80</StartDate>
    <Commission/>
    </Employee>
    <Employee employeeNumber="7566">
    <Name>JONES</Name>
    <Title>MANAGER</Title>
    <Manager>7839</Manager>
    <StartDate>02-APR-81</StartDate>
    <Commission/>
    </Employee>
    <Employee employeeNumber="7788">
    <Name>SCOTT</Name>
    <Title>ANALYST</Title>
    <Manager>7566</Manager>
    <StartDate>19-APR-87</StartDate>
    <Commission/>
    </Employee>
    <Employee employeeNumber="7876">
    <Name>ADAMS</Name>
    <Title>CLERK</Title>
    <Manager>7788</Manager>
    <StartDate>23-MAY-87</StartDate>
    <Commission/>
    </Employee>
    <Employee employeeNumber="7902">
    <Name>FORD</Name>
    <Title>ANALYST</Title>
    <Manager>7566</Manager>
    <StartDate>03-DEC-81</StartDate>
    <Commission/>
    </Employee>
    </EmployeeList>
    </Department>

    <Department>
    <Name>SALES</Name>
    <Location>CHICAGO</Location>
    <EmployeeList>
    <Employee employeeNumber="7499">
    <Name>ALLEN</Name>
    <Title>SALESMAN</Title>
    <Manager>7698</Manager>
    <StartDate>20-FEB-81</StartDate>
    <Commission>300</Commission>
    </Employee>
    <Employee employeeNumber="7521">
    <Name>WARD</Name>
    <Title>SALESMAN</Title>
    <Manager>7698</Manager>
    <StartDate>22-FEB-81</StartDate>
    <Commission>500</Commission>
    </Employee>
    <Employee employeeNumber="7654">
    <Name>MARTIN</Name>
    <Title>SALESMAN</Title>
    <Manager>7698</Manager>
    <StartDate>28-SEP-81</StartDate>
    <Commission>1400</Commission>
    </Employee>
    <Employee employeeNumber="7698">
    <Name>BLAKE</Name>
    <Title>MANAGER</Title>
    <Manager>7839</Manager>
    <StartDate>01-MAY-81</StartDate>
    <Commission/>
    </Employee>
    <Employee employeeNumber="7844">
    <Name>TURNER</Name>
    <Title>SALESMAN</Title>
    <Manager>7698</Manager>
    <StartDate>08-SEP-81</StartDate>
    <Commission>0</Commission>
    </Employee>
    <Employee employeeNumber="7900">
    <Name>JAMES</Name>
    <Title>CLERK</Title>
    <Manager>7698</Manager>
    <StartDate>03-DEC-81</StartDate>
    <Commission/>
    </Employee>
    </EmployeeList>
    </Department>

    <Department>
    <Name>OPERATIONS</Name>
    <Location>BOSTON</Location>
    <EmployeeList/>
    </Department>


    SQL>
  • 2. Re: XMLElement Query
    mdrake Expert
    Currently Being Moderated
    Note the extract('/*') just forces the pretty print.
  • 3. Re: XMLElement Query
    jakaur Newbie
    Currently Being Moderated
    Hi Mark,

    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.

    Regards,
    Jatinder
  • 4. Re: XMLElement Query
    mdrake Expert
    Currently Being Moderated
    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 9.2.0.3.0 release. Please upgrade.