SQL data to be framed the form of XML [message #521383] |
Mon, 29 August 2011 07:35 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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
|
|
|