Home » Developer & Programmer » JDeveloper, Java & XML » Customized XML Ouput
Customized XML Ouput [message #358973] Thu, 13 November 2008 01:41 Go to next message
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 #358981 is a reply to message #358973] Thu, 13 November 2008 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/343352/102589/?#msg_343352

Regards
Michel
Re: Customized XML Ouput [message #359007 is a reply to message #358981] Thu, 13 November 2008 05:21 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #359040 is a reply to message #359033] Thu, 13 November 2008 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste your session as I did and post your Oracle version with 4 decimals.

Regards
Michel
Re: Customized XML Ouput [message #359041 is a reply to message #359040] Thu, 13 November 2008 07:56 Go to previous messageGo to next message
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 #359045 is a reply to message #359041] Thu, 13 November 2008 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, now the first part of my sentence.

Regards
Michel
Re: Customized XML Ouput [message #359073 is a reply to message #359045] Thu, 13 November 2008 11:58 Go to previous messageGo to next message
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 #359076 is a reply to message #359073] Thu, 13 November 2008 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep your code lines in 80 characters.

ORA-19011: Character string buffer too small
 *Cause:  The string result asked for is too big to return back
 *Action:  Get the result as a lob instead


I have not your data but I bet your result is greater than 4000 bytes. Use LENGTH instead of REPLACE to check its length.

Regards
Michel
Re: Customized XML Ouput [message #359079 is a reply to message #359076] Thu, 13 November 2008 12:28 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: Customized XML Ouput [message #359566 is a reply to message #359090] Mon, 17 November 2008 05:45 Go to previous message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Could you pls let me know how to sort the above query as sorted in sql query and also let me know whether the XMl query is an equivalent to sql query.

Thanks

[Updated on: Mon, 17 November 2008 05:46]

Report message to a moderator

Previous Topic: Question: Using newer JRE/JDK than what Oracle 9i provides?
Next Topic: CLOB missing values from XMLType
Goto Forum:
  


Current Time: Wed Apr 16 04:41:33 CDT 2014

Total time taken to generate the page: 0.06728 seconds