Home » Developer & Programmer » JDeveloper, Java & XML » Customized XML Ouput
| Customized XML Ouput [message #358973] |
Thu, 13 November 2008 01:41  |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi,
Below is the view created on 3 tables with tables alias names as P,PI,PO.
The ouput of the query is
select P.H, PI.Q,PO.O from view1;
H Q O1
1 1 NULL
1 2 B
2 1 NULL
2 2 C
3 3 NULL
When tired to generate XML from DBMS_XMLGEN.getxml. Its generating a different XMl which is auto. I need to customixe the XMl format to below one.
Required ouput in XML
<P>
<H>1</H>
<PI>
<Q>1</Q>
<PO/>
</PI>
<PI>
<Q>2</Q>
<PO>
<O1>B</O1>
</PO>
</PI>
</P>
<P>
<H>2</H>
<PI>
<Q>1</Q>
<PO/>
</PI>
<PI>
<Q>2</Q>
<PO>
<O1>C</O1>
</PO>
</PI>
</P>
<P>
<H>2</H>
<PI>
<Q>3</Q>
<PO/>
</PI>
</P>
Any help really appreciated
Thanks in advance
|
|
|
|
|
|
| Re: Customized XML Ouput [message #359007 is a reply to message #358981] |
Thu, 13 November 2008 05:21  |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi,
Here below is the query which gives XML output.
SELECT XMLELEMENT(
"dept_list",
XMLAGG (
XMLELEMENT(
"dept",
XMLATTRIBUTEs(deptno AS "deptno"),
XMLFOREST(
d.deptno AS "deptno",
d.dname AS "dname",
d.loc AS "loc",
(SELECT XMLAGG(
XMLELEMENT(
"emp",
XMLATTRIBUTEs(empno AS "empno"),
XMLFOREST(
e.empno AS "empno",
e.ename AS "ename",
e.job AS "job",
e.mgr AS "mgr",
e.hiredate AS "hiredate",
e.sal AS "sal",
e.comm AS "comm"
)
)
)
FROM emp e
WHERE e.deptno = d.deptno
) "emp_list" -- This tag not required.
)
)
)
) AS "depts"
FROM dept d
WHERE d.deptno = 10;
It giving an xml output with unwanted tag. How to customize the query inorder to avoid the tag "emp_list".
And also if the values of below columns
e.empno
e.ename
e.job
e.mgr
e.hiredate
e.sal
e.comm
are null then it given an XML as <emp><emp/> instead of that my requirement is to give onely one tag with </emp>
XML OUTPUT
<ROWSET>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
<EMP_LIST>--not required
<ROWSET>
<EMP>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>09-JUN-81</HIREDATE>
<SAL>2450</SAL>
</EMP>
<EMP>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-NOV-81</HIREDATE>
<SAL>5000</SAL>
</EMP>
<EMP>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>23-JAN-82</HIREDATE>
<SAL>1300</SAL>
</EMP>
</ROWSET>
</EMP_LIST>-- Not required
</DEPT>
</ROWSET>
Thanks in advance
[Updated on: Thu, 13 November 2008 05:40] Report message to a moderator |
|
|
|
| Re: Customized XML Ouput [message #359017 is a reply to message #359007] |
Thu, 13 November 2008 05:47 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> select replace(replace("depts",'<emp_list>',''),'</emp_list>','')
2 from (
3 SELECT XMLELEMENT(
4 "dept_list",
5 XMLAGG (
6 XMLELEMENT(
7 "dept",
8 XMLATTRIBUTEs(deptno AS "deptno"),
9 XMLFOREST(
10 d.deptno AS "deptno",
11 d.dname AS "dname",
12 d.loc AS "loc",
13 (SELECT XMLAGG(
14 XMLELEMENT(
15 "emp",
16 XMLATTRIBUTEs(empno AS "empno"),
17 XMLFOREST(
18 e.empno AS "empno",
19 e.ename AS "ename",
20 e.job AS "job",
21 e.mgr AS "mgr",
22 e.hiredate AS "hiredate",
23 e.sal AS "sal",
24 e.comm AS "comm"
25 )
26 )
27 )
28 FROM emp e
29 WHERE e.deptno = d.deptno
30 ) "emp_list" -- This tag not required.
31 )
32 )
33 )
34 ) AS "depts"
35 FROM dept d
36 WHERE d.deptno = 10
37 );
REPLACE(REPLACE("DEPTS",'<EMP_LIST>',''),'</EMP_LIST>','')
--------------------------------------------------------------------------------
<dept_list><dept deptno="10"><deptno>10</deptno><dname>ACCOUNTING</dname><loc>NE
W YORK</loc><emp empno="7782"><empno>7782</empno><ename>CLARK</ename><job>MANAGE
R</job><mgr>7839</mgr><hiredate>1981-06-09</hiredate><sal>2450</sal></emp><emp e
mpno="7839"><empno>7839</empno><ename>KING</ename><job>PRESIDENT</job><mgr>7369<
/mgr><hiredate>1981-11-17</hiredate><sal>5000</sal></emp><emp empno="7934"><empn
o>7934</empno><ename>MILLER</ename><job>CLERK</job><mgr>7782</mgr><hiredate>1982
-01-23</hiredate><sal>1300</sal></emp></dept></dept_list>
1 row selected.
Regards
Michel
|
|
|
|
| Re: Customized XML Ouput [message #359033 is a reply to message #359017] |
Thu, 13 November 2008 07:13 |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi Michel,
Thanks for your response.
When I tried to do so. Its giving an below error
ERROR:
ORA-19011: Character string buffer too small
Just let me know how to over come this error.
Thanks in advance.
|
|
|
|
|
|
| Re: Customized XML Ouput [message #359041 is a reply to message #359040] |
Thu, 13 November 2008 07:56 |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi,
Below is the version details.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>
Thanks
|
|
|
|
|
|
| Re: Customized XML Ouput [message #359073 is a reply to message #359045] |
Thu, 13 November 2008 11:58 |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi,
Here below is the session and error
select replace (replace,"PI",<A>,''),</A>,'') from (SELECT XMLAGG ( XMLELEMENT( "P",
XMLFOREST( P.process_id AS Ppid,
(SELECT XMLAGG( XMLELEMENT( "PI", XMLFOREST( PI.question_id AS PIqid, PI.process_id AS PIpid,
PI.innertext AS PItext, PI.itemtype AS PItype, PI.linkfrom AS PIfrom, PI.linkto AS PIto,
PI.associated AS PIas, PI.content_id AS PIc, PI.exitpoint1_id AS PIe1,
PI.exitpoint2_id AS PIe2, PI.exitpoint3_id AS PIe3, PI.followoncall AS PIfoc,
PI.userinput AS PIui, PI.resolveidentifier AS PIri, PI.libquestion_idfk AS PIlqid,
PI.isLocked AS PIstls, PI.PreviousAnswer AS PIPAns, PI.VisibleToAgent AS PIVAgent,
PI.RetryAttempt AS PIRetry, PI.Tags AS PITag,
(SELECT XMLAGG( XMLELEMENT( "PO", XMLFOREST( PO.option_id AS POoid, PO.question_id AS POqid,
PO.process_id AS popid, PO.opt_innertext AS POtext, PO.opt_linkfrom AS POfrom,
PO.opt_linkto AS POto, PO.libquestion_idfk AS POlqid, PO.liboption_idfk AS POloid ) ) )
FROM vw_liveProcessOption_Sim_v6 PO
WHERE PI.question_id = PO.question_id
AND PI.process_id = PO.process_id
) "A" ) ) ) AS "PO"
FROM vw_liveProcessItem_Sim_v6 PI
WHERE P.process_id = PI.process_id
) "A" ) ) ) AS "PI"
FROM liveProcess_ec P
WHERE (P.process_id = 450))
ERROR:
ORA-19011: Character string buffer too small
Thanks
[Updated on: Thu, 13 November 2008 12:17] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Customized XML Ouput [message #359079 is a reply to message #359076] |
Thu, 13 November 2008 12:28 |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi,
If it is more than 400kb then how to see the output.
could you pls give one small example to do so.
Every time I am getting the same problem.
Thanks
|
|
|
|
| Re: Customized XML Ouput [message #359090 is a reply to message #359079] |
Thu, 13 November 2008 12:58 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I have no material to work with.
Try:
SQL> l
1 select replace(replace(to_clob("depts"),'<emp_list>',''),'</emp_list>','')
2 from (
3 SELECT XMLELEMENT(
4 "dept_list",
5 XMLAGG (
6 XMLELEMENT(
7 "dept",
8 XMLATTRIBUTEs(deptno AS "deptno"),
9 XMLFOREST(
10 d.deptno AS "deptno",
11 d.dname AS "dname",
12 d.loc AS "loc",
13 (SELECT XMLAGG(
14 XMLELEMENT(
15 "emp",
16 XMLATTRIBUTEs(empno AS "empno"),
17 XMLFOREST(
18 e.empno AS "empno",
19 e.ename AS "ename",
20 e.job AS "job",
21 e.mgr AS "mgr",
22 e.hiredate AS "hiredate",
23 e.sal AS "sal",
24 e.comm AS "comm"
25 )
26 )
27 )
28 FROM emp e
29 WHERE e.deptno = d.deptno
30 ) "emp_list" -- This tag not required.
31 )
32 )
33 )
34 ) AS "depts"
35 FROM dept d
36 WHERE d.deptno = 10
37* )
SQL> /
REPLACE(REPLACE(TO_CLOB("DEPTS"),'<EMP_LIST>',''),'</EMP_LIST>','')
--------------------------------------------------------------------------------
<dept_list><dept deptno="10"><deptno>10</deptno><dname>ACCOUNTING</dname><loc>NE
W YORK</loc><emp empno="7782"><empno>7782</empno><ename>CLARK</ename><job>MANAGE
R</job><mgr>7839</mgr><hiredate>1981-06-09</hiredate><sal>2450</sal></emp><emp e
mpno="7839"><empno>7839</empno><ename>KING</ename><job>PRESIDENT</job><hiredate>
1981-11-17</hiredate><sal>5000</sal></emp><emp empno="7934"><empno>7934</empno><
ename>MILLER</ename><job>CLERK</job><mgr>7782</mgr><hiredate>1982-01-23</hiredat
e><sal>1300</sal></emp></dept></dept_list>
1 row selected.
Note the TO_CLOB in first line.
Regards
Michel
|
|
|
|
| Re: Customized XML Ouput [message #359145 is a reply to message #359090] |
Thu, 13 November 2008 19:57 |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi MIchel,
Thanks for your replies.
Here is the sql query which has to be written as XML query.
Could you pls let us know how to use order by clause, joins with XML query.
SQL QUERY
SELECT
P.process_id AS Ppid,
PI.question_id AS PIqid,
PI.process_id AS PIpid,
PI.innertext AS PItext,
PI.itemtype AS PItype,
PI.linkfrom AS PIfrom,
PI.linkto AS PIto,
PI.associated AS PIas,
PI.content_id AS PIc,
PI.exitpoint1_id AS PIe1,
PI.exitpoint2_id AS PIe2,
PI.exitpoint3_id AS PIe3,
PI.followoncall AS PIfoc,
PI.userinput AS PIui,
PI.resolveidentifier AS PIri,
PI.libquestion_idfk AS PIlqid,
PI.isLocked AS PIstls,
PI.PreviousAnswer as PIPAns,
PI.VisibleToAgent as PIVAgent,
PI.RetryAttempt as PIRetry,
PI.Tags as PITag,
PO.option_id AS POoid,
PO.question_id AS POqid,
PO.process_id AS popid,
PO.opt_innertext AS POtext,
PO.opt_linkfrom AS POfrom,
PO.opt_linkto AS POto,
PO.libquestion_idfk AS POlqid,
PO.liboption_idfk AS POloid
FROM liveProcess_ec P INNER JOIN
vw_liveProcessItem_Sim_v6 PI ON P.process_id = PI.process_id LEFT OUTER JOIN
vw_liveProcessOption_Sim_v6 PO ON PI.question_id = PO.question_id AND PI.process_id = PO.process_id
where p.process_id=450
order by Ppid, PIqid, POoid;
XML QUERY IS IT EQUIVALENT TO ABOVE QUERY
select replace (replace,"PI",<A>,''),</A>,'') from (SELECT XMLAGG ( XMLELEMENT( "P",
XMLFOREST( P.process_id AS Ppid,
(SELECT XMLAGG( XMLELEMENT( "PI", XMLFOREST( PI.question_id AS PIqid, PI.process_id AS PIpid,
PI.innertext AS PItext, PI.itemtype AS PItype, PI.linkfrom AS PIfrom, PI.linkto AS PIto,
PI.associated AS PIas, PI.content_id AS PIc, PI.exitpoint1_id AS PIe1,
PI.exitpoint2_id AS PIe2, PI.exitpoint3_id AS PIe3, PI.followoncall AS PIfoc,
PI.userinput AS PIui, PI.resolveidentifier AS PIri, PI.libquestion_idfk AS PIlqid,
PI.isLocked AS PIstls, PI.PreviousAnswer AS PIPAns, PI.VisibleToAgent AS PIVAgent,
PI.RetryAttempt AS PIRetry, PI.Tags AS PITag,
(SELECT XMLAGG( XMLELEMENT( "PO", XMLFOREST( PO.option_id AS POoid, PO.question_id AS POqid,
PO.process_id AS popid, PO.opt_innertext AS POtext, PO.opt_linkfrom AS POfrom,
PO.opt_linkto AS POto, PO.libquestion_idfk AS POlqid, PO.liboption_idfk AS POloid ) ) )
FROM vw_liveProcessOption_Sim_v6 PO
WHERE PI.question_id = PO.question_id (+)
AND PI.process_id = PO.process_id (+)
) "A" ) ) ) AS "PO"
FROM vw_liveProcessItem_Sim_v6 PI
WHERE P.process_id = PI.process_id
) "A" ) ) ) AS "PI"
FROM liveProcess_ec P
WHERE (P.process_id = 450))
Any help really appreciated.
Thanks
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 00:39:20 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
