convert column data to xml format; query fails because all columns types are currently not supported

Question

I am trying to convert column data to xml format, but I get this error message:

The query fails because all columns types are currently not supported.

CREATE TABLE EMP(NAME VARCHAR2(10 BYTE))

INSERT INTO EMP VALUES ('C');
INSERT INTO EMP VALUES ('A');
INSERT INTO EMP VALUES ('T');

SELECT xmlelement("NAME",NAME) FROM EMP;

I am using:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

SQLTools 1.5.0 Beta build 9 as EDITOR

Why is this error arising??? What is the solution for this?

Answer 1

I've found the answer:

select dbms_xmlquery.getxml('select * from EMP') from dual;

Answer 2

This is more of a workaround and not a solution.

I was having the same problems as sam - also running a SELECT xmlelement statement, also using SQLTools. One difference is that I was running Oracle DB version 11.2.0.2.0.

I found that if I ran the statement in SQLPlus, it was able to display the result.

SQL> SELECT XMLELEMENT("name",ename) FROM scott.emp WHERE ROWNUM < 3;

XMLELEMENT("NAME",ENAME)
--------------------------------------------------------------------------------
<name>SMITH</name>
<name>ALLEN</name>

If I ran the statement in SQL Developer, it tried to display the results, but only showed (XMLTYPE).