Home » Developer & Programmer » JDeveloper, Java & XML » SQL data to be framed the form of XML (oracle 11gR1)
SQL data to be framed the form of XML [message #521383] Mon, 29 August 2011 07:35 Go to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Hi

I have a scenario to frame an xml as below, below i have given a sample data

  
      
Create table xml_type
(msg varchar2(1000),
desp varchar2(1000),
val    number) ;

insert into xml_type values ('Invalid Name ID ','NAME ID',6);
insert into xml_type values ('Invalid Name ID ','NAME ID',7);
insert into xml_type values ('Invalid Name ID ','NAME ID',8);

insert into  xml_type values ('Invalid FAQ ID ','FAQ ID',9);
insert into  xml_type values ('Invalid FAQ ID ','FAQ ID',10);

Commit;


Actually i need the output as below, i tried and succeeded doing it in Procedure , but they are asking me to do it in SQL query.Any idea how to get from an sql query.

<DETAIL>
<ERROR> 'Invalid Name ID' </ERROR>
<ID> 'Name ID' </ID>
<VALUE> 6 </VALUE>
<VALUE> 7 </VALUE>
<VALUE> 8 </VALUE>
</DETAIL>
<DETAIL>
<ERROR> 'Invalid FAQ ID' </ERROR>
<ID> 'FAQ ID' </ID>
<VALUE> 9 </VALUE>
<VALUE> 10 </VALUE>
</DETAIL>


Or i was planning to get the values as below with two columns and pass it to informatica to frame the XML.

     
Col1                            Col2
Invalid Name Id             Error 
Name ID                        ID 
6                                   Value
7                                    Value
8                                    Value
Invalid FAQ ID                  Error
FAQ Id                             ID
9                                       Value
10                                      Value

Let me know the best option and how to do this task.

Thanks,
SRK
Re: SQL data to be framed the form of XML [message #521385 is a reply to message #521383] Mon, 29 August 2011 08:03 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT XMLELEMENT (
  2           "DETAIL",
  3           XMLELEMENT ("ERROR", msg),
  4           XMLELEMENT ("ID", desp),
  5           XMLAGG (XMLELEMENT ("VALUE", val))
  6           )
  7  from xml_type
  8  group by msg, desp
  9  /
XMLELEMENT("DETAIL",XMLELEMENT("ERROR",MSG),XMLELEMENT("ID",DESP),XMLAGG(XMLELEMENT("VALUE",VAL)))
-----------------------------------------------------------------------------------------------------------------
<DETAIL><ERROR>Invalid FAQ ID </ERROR><ID>FAQ ID</ID><VALUE>9</VALUE><VALUE>10</VALUE></DETAIL>
<DETAIL><ERROR>Invalid Name ID </ERROR><ID>NAME ID</ID><VALUE>6</VALUE><VALUE>7</VALUE><VALUE>8</VALUE></DETAIL>


Regards
Michel
Previous Topic: Native Java in Oracle vs PL/SQL
Next Topic: difference among gettransaction,getdbtransaction and getoadbtransaction
Goto Forum:
  


Current Time: Thu Mar 28 17:47:50 CDT 2024