Home » Developer & Programmer » JDeveloper, Java & XML » convert column data to xml format (Oracle 10g, Win7)
convert column data to xml format [message #560029] Mon, 09 July 2012 23:15 Go to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
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 trying to convert column data to xml format, but I get this error message:
Quote:
The query fails because all columns types are currently not supported.

i am using:

Quote:
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?
Re: convert column data to xml format [message #560031 is a reply to message #560029] Mon, 09 July 2012 23:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this what you want?
SQL> select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;
DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>
      <HIREDATE>12/17/1980 0:0:0</HIREDATE>
      <SAL>800</SAL>
      <DEPTNO>20</DEPTNO>
   </ROW>
   <ROW num="2">
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/20/1981 0:0:0</HIREDATE>
      <SAL>1600</SAL>
      <COMM>300</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
   <ROW num="3">
      <EMPNO>7521</EMPNO>
      <ENAME>WARD</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/22/1981 0:0:0</HIREDATE>
      <SAL>1250</SAL>
      <COMM>500</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
</ROWSET>

Regards
Michel
Re: convert column data to xml format [message #560033 is a reply to message #560031] Mon, 09 July 2012 23:36 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
yes. thank you. but why was my code giving the error, can you make me clear??
Re: convert column data to xml format [message #560035 is a reply to message #560033] Tue, 10 July 2012 00:07 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

@nischalinn
i tried in 11g version it's working...
Re: convert column data to xml format [message #560037 is a reply to message #560035] Tue, 10 July 2012 00:20 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
so whats the problem with 10th version???
Re: convert column data to xml format [message #560040 is a reply to message #560037] Tue, 10 July 2012 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT xmlelement("NAME",ename) from emp;
XMLELEMENT("NAME",ENAME)
-------------------------------------------------
<NAME>SMITH</NAME>
<NAME>ALLEN</NAME>
<NAME>WARD</NAME>
<NAME>JONES</NAME>
<NAME>MARTIN</NAME>
<NAME>BLAKE</NAME>
<NAME>CLARK</NAME>
<NAME>SCOTT</NAME>
<NAME>KING</NAME>
<NAME>TURNER</NAME>
<NAME>ADAMS</NAME>
<NAME>JAMES</NAME>
<NAME>FORD</NAME>
<NAME>MILLER</NAME>

14 rows selected.

SQL> @v

Version Oracle : 10.2.0.4.0

Use SQL*Plus and copy and paste your session as I did, including your version number (query v$version).

Regards
Michel
Re: convert column data to xml format [message #560081 is a reply to message #560040] Tue, 10 July 2012 07:12 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
i did not understand your reply. I've already provided the version of Oracle.
Re: convert column data to xml format [message #560088 is a reply to message #560081] Tue, 10 July 2012 07:50 Go to previous message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I provided the as requested in OraFAQ Forum Guide, you did not.
There is nothing to understand to my reply just to see it and repeat the same thing.

Regards
Michel

[Updated on: Tue, 10 July 2012 07:50]

Report message to a moderator

Previous Topic: How to enable a DFF field from PUI to ESS page(OAF) through personalization
Next Topic: xml data to html table with <ROW> tag data not displayed
Goto Forum:
  


Current Time: Thu Oct 02 09:54:08 CDT 2014

Total time taken to generate the page: 0.10705 seconds