Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Distinct XML Retured in Stored Proc

Re: Distinct XML Retured in Stored Proc

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Tue, 15 Jul 2003 00:03:07 GMT
Message-ID: <Xns93B8CBAB3D047pobox002bebubcom@204.127.204.17>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US