| dbms_xmlgen.getxml problem [message #129522] |
Mon, 25 July 2005 10:20  |
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
|
Senior Member |
|
|
Hi all,
following is the out put of the following query:-
select dbms_xmlgen.getxml('select * from emp e,dept d where e.deptno=d.deptno AND e.deptno=10 and e.job=''PRESIDENT''') from dual;
output:--
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-NOV-81</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</ROW>
</ROWSET>
But we need an output as below:-
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMP>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-NOV-81</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</EMP>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</DEPT>
</ROW>
</ROWSET>
THAT IS WE NEED THE XML OUTPUT AS SEPARATE FOR EACH TABLE.
how it is possible.
FYI ,we are using 9.2 database and w2k os.
Thanks
dinesh
|
|
|
|
|
|
| Re: dbms_xmlgen.getxml problem [message #130123 is a reply to message #129670] |
Thu, 28 July 2005 05:21  |
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
|
Senior Member |
|
|
Hi all,
here is a query i got to execute after a two 2 days experiment:
SELECT XMLELEMENT("ROOT",
XMLCONCAT(
XMLELEMENT("DEPT",
XMLFOREST(D.DEPTNO,D.DNAME)),
XMLELEMENT("EMP",
XMLFOREST(E.EMPNO,E.ENAME)))) AS XMLDOC
FROM EMP E,DEPT D
WHERE
E.DEPTNO=D.DEPTNO
AND E.JOB='PRESIDENT'
AND D.DEPTNO=10
/
The output is as below:
XMLDOC
------------------------------------------
<ROOT>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
</DEPT>
<EMP>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
</EMP>
</ROOT>
It is giving everything except one important row:-
that is
<?xml version="1.0"?>
So can any one pls give some idea this time how i will add the above line to the above output.
Thanks
Dinesh
[Updated on: Thu, 28 July 2005 05:22] Report message to a moderator |
|
|
|
| Re: dbms_xmlgen.getxml problem [message #131004 is a reply to message #129522] |
Wed, 03 August 2005 16:45 |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
Hello,
the basic SQL/XML (or SQLX) functions will not give you the XML prolog, you'd have to "add" it manually.
Some of Oracle's proprietary SQLX functions do put it on however,
i.e.
SQL> SELECT SYS_XMLAGG(XMLELEMENT("dummy", dummy)) FROM dual;
SYS_XMLAGG(XMLELEMENT("DUMMY",DUMMY))
-------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<dummy>X</dummy></ROWSET>
So you could look into this, probably using XMLFORMAT object to change the name of your root element, i.e.
SQL> SELECT SYS_XMLAGG(XMLELEMENT("dummy", dummy),
2 XMLFORMAT('XYZ'))
3 FROM dual;
SYS_XMLAGG(XMLELEMENT("DUMMY",DUMMY),XMLFORMAT('XYZ'))
----------------------------------------------------------
<?xml version="1.0"?>
<XYZ>
<dummy>X</dummy>
</XYZ>
Rgds
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
