Home » Developer & Programmer » JDeveloper, Java & XML » XML Problem
XML Problem [message #360324] Thu, 20 November 2008 07:22 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Below is the query need to rewrite inorder to generate XML output.
SQL query

 SELECT lq.libquestion_id     ,
  lq.innertext                ,
  lq.content_id               ,
  lq.tags                     ,
  lo.innertext AS lo_innertext,
  lo.shared_id
   FROM libraryquestions_ec lq
INNER JOIN libraryoptions_ec lo
     ON lq.libquestion_id = lo.libquestion_idfk
  WHERE lq.resolveRef    IS NULL
ORDER BY lq.libquestion_id,
  lo.shared_id



xml query

SQL>  select xmlelement("lq",xmlforest(lq.libquestion_id     ,
  2    lq.innertext                ,
  3    lq.content_id               ,
  4    lq.tags  ),xmlagg(xmlelement("lo",xmlforest(lo.innertext AS lo_innertext,
  5    lo.shared_id)) )) from  libraryquestions_ec lq
  6  INNER JOIN libraryoptions_ec lo
  7       ON lq.libquestion_id = lo.libquestion_idfk
  8    WHERE lq.resolveRef    IS NULL ORDER BY lq.libquestion_id,
  9    lo.shared_id;
 select xmlelement("lq",xmlforest(lq.libquestion_id     ,
                                  *
ERROR at line 1:
ORA-00937: not a single-group group function 


SQL> spool off



Required Ouput

<lq>
  <libquestion_id>5</libquestion_id>
  <innertext>Check Oneview, has customer previously left their V-Box tuned to Setanta (CH34) (with viewing card inserted) for one hour in order for service to work?</innertext>
  <content_id>0</content_id>
  <lo>
    <lo_innertext>Yes</lo_innertext>
    <shared_id>1</shared_id>
  </lo>
  <lo>
    <lo_innertext>No</lo_innertext>
    <shared_id>2</shared_id>
  </lo>
</lq>
<lq>
  <libquestion_id>7</libquestion_id>
  <innertext>Can the customer view webpages via the Ethernet/USB connection (for example, www.bt.com)?</innertext>
  <content_id>0</content_id>
  <lo>
    <lo_innertext>Yes</lo_innertext>
    <shared_id>1</shared_id>
  </lo>
  <lo>
    <lo_innertext>No</lo_innertext>
    <shared_id>2</shared_id>
  </lo>
  <lo>
    <lo_innertext>Ethernet/USB Unavailable</lo_innertext>
    <shared_id>4</shared_id>
  </lo>
</lq>


Thanks in advance
Re: XML Problem [message #360334 is a reply to message #360324] Thu, 20 November 2008 07:56 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you give us some test data and table scripts, then we can test your sql and our sql to make sure that they work.

Expecting us to create our own test data in order to help you is. perhaps, a little rude.
Previous Topic: How to use left outer joins ,right outer joins and order by clause for below query (merged)
Next Topic: ORA-29535,load java ,class not found
Goto Forum:
  


Current Time: Thu Aug 28 08:08:22 CDT 2014

Total time taken to generate the page: 0.11282 seconds