4 Replies Latest reply: Jul 7, 2003 6:41 PM by mdrake-Oracle RSS

    XMLElement Query

    jakaur-Oracle
      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-Oracle
          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-Oracle
            Note the extract('/*') just forces the pretty print.
            • 3. Re: XMLElement Query
              jakaur-Oracle
              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-Oracle
                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.