Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Distinct XML Retured in Stored Proc
pmason09_at_yahoo.com (Paul) wrote in
news:f5d9a16a.0307140809.3f929d2b_at_posting.google.com:
> I have an PL/SQL stored proc that is currently working fine. I need
> to modify it so I can use the DISTINCT key word. The SP returns the
> results in XML. Here it is. Any help would be appreciated.
>
> SELECT xmlagg(XMLELEMENT("JobNumber", ISR.ISR_NO)) AS "XMLReturned"
> INTO xmlTypeRet
> FROM isr
>
> PM
You can use an in line view to wrap the distinct
SQL> select xmlagg(xmlelement("JobNumber",deptno)) as "XMLReturned" 2 from (select distinct deptno from emp);
XMLReturned
<JobNumber>10</JobNumber> <JobNumber>20</JobNumber> <JobNumber>30</JobNumber>Received on Mon Jul 14 2003 - 19:03:07 CDT