Home » Developer & Programmer » JDeveloper, Java & XML » About a query (10g 10.2.0.1 , windows)
About a query [message #410070] Thu, 25 June 2009 01:08 Go to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Dears
please help me ,


DECLARE
  ctx DBMS_XMLGEN.ctxHandle;
  xml CLOB;
BEGIN
  ctx := dbms_xmlgen.newcontext('select * from emp');
  dbms_xmlgen.setrowtag(ctx, 'MY-ROW-START-HERE');
  xml := dbms_xmlgen.getxml(ctx);
  dbms_output.put_line(substr(xml,1,255));
END;
/


My query is : select empno,ename,job,mgr,hiredate,sal,deptno from emp;


ouput returns as below:
   
 <?xml version="1.0"?>
<EMP>
  <EMPNO>7876</EMPNO>
  <ENAME>ADAMS</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7788</MGR>
  <HIREDATE>23-MAY-1987 00:00:00</HIREDATE>
  <SAL>1100</SAL>
  <DEPTNO>20</DEPTNO>
 ...
</EMP>




But my Required Output will be as below:

 
<?xml version="1.0"?>
<EMP>
  <EMPNO>
	<First>78</First>
	<Last>76</Last>
  </EMPNO>
  <ENAME>ADAMS</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7788</MGR>
  <HIREDATE>23-MAY-1987 00:00:00</HIREDATE>
  <SAL>1100</SAL>
  <DEPTNO>20</DEPTNO>
 ...
</EMP> 


[ EMPNO= 7876 is divided by two parts, which are "first" and "last".]


what should be my sqlquery for above xml output?





Re: About a query [message #410081 is a reply to message #410070] Thu, 25 June 2009 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm not keen in XML stuff but here's a way to do it:
SQL> create or replace type empno as object (
  2    first varchar2(2),
  3    last  varchar2(2)
  4    )
  5  /

Type created.

SQL> select dbms_xmlgen.GETXML(
  2  'select empno(substr(empno,1,2),substr(empno,-2)) empno,
  3         ename,job,mgr,hiredate,sal,deptno 
  4  from emp
  5  where empno=7876') val
  6  from dual
  7  /
VAL
--------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPNO>
   <FIRST>78</FIRST>
   <LAST>76</LAST>
  </EMPNO>
  <ENAME>ADAMS</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7788</MGR>
  <HIREDATE>23/05/1987 00:00:00</HIREDATE>
  <SAL>1100</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
</ROWSET>

1 row selected.

Regards
Michel
Re: About a query [message #410097 is a reply to message #410081] Thu, 25 June 2009 03:53 Go to previous message
halim
Messages: 100
Registered: September 2008
Senior Member

Dear Michel

Thanks , thanks a lot.

My problem is solved by your solution.



Best Regards
Halim
Previous Topic: Casting In XML--Char to Number
Next Topic: OracleResultSet and ResultSet
Goto Forum:
  


Current Time: Thu Mar 28 08:09:58 CDT 2024