Re: Distinct XML Retured in Stored Proc

Martin Burbridge <pobox002_at_bebub.com> wrote in message news:<Xns93B8CBAB3D047pobox002bebubcom_at_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>

THANKS FOR THE HELP. I appreciate it, but I have one more question. Can you have a JOIN in the in line view??? The below code gives me an invalid identifier problem on the joined table.

SELECT xmlagg(XMLELEMENT("JobInfo",

	   	 XMLELEMENT("JobNumber",  isr.ISR_NO ),
		 XMLELEMENT("SourceSystem", isr.P_ORIGIN ),
		 XMLELEMENT("OMSFieldReport",  r01_job_Report.ISR_NO))
  		   		  ) AS "XMLReturned"   
	   INTO xmlTypeRet
	   from (select distinct isr.isr_no, isr.p_origin from isr
	   LEFT JOIN r01_job_Report on r01_job_Report.isr_No = isr.Isr_No);