OraFAQ Forum: JDeveloper, Java & XML » SQL data to be framed the form of XML

Home » Developer & Programmer » JDeveloper, Java & XML » SQL data to be framed the form of XML (oracle 11gR1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL data to be framed the form of XML [message #521383]

Mon, 29 August 2011 07:35

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

Report message to a moderator

Re: SQL data to be framed the form of XML [message #521385 is a reply to message #521383]

Mon, 29 August 2011 08:03

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 01:19:23 CST 2025