Create table patient (pat_mrn varchar2(100)) ;
  Create table encount (pat_mrn varchar2(100), encounter_id varchar2(1000));
  
 Insert into patient values ('63280');
 Insert into encount values ('63280', '42');

create table encounter_dx (CSN_ID VARCHAR2(100), encounter_id varchar2(100), dx_id varchar2(100));
Insert into encounter_dx values (300, 42, 106);
Insert into encounter_dx values (300, 42, 107);


Create table dx (dx_id varchar2(100),ic varchar2(100));
Insert into dx values (106, 410);
Insert into dx values (106, 420);

<EncounterDiagnosis>
<CSN>300</CSN>
<Diagnosis>
<ID>106</ID>
<DiagnosisCodes>
<Code>410</Code>
<Code>420</Code>
</DiagnosisCodes>
</Diagnosis>
</EncounterDiagnosis>

SELECT
(
SELECT 
 XMLELEMENT("EncounterDiagnosis",
      XMLELEMENT("PrimaryDiagnosis",CE.CSN_ID), -- Once I include this I get an error saying not a group group function
                    XMLAGG(XMLELEMENT("Diagnosis",
                                            XMLELEMENT("ID", CE.DX_ID),
                                            XMLELEMENT("DiagnosisCodes",
                                                       XMLAGG(XMLELEMENT("Code",
                                                                         DX.IC))))))
        
          FROM encounter_dx CE, DX 
         WHERE CE.ENCOUNTER_ID = E.encounter_id
          AND  CE.DX_id = DX.DX_ID
         GROUP BY CE.DX_ID) 
         AS Orderxml

  FROM PATIENT P,ENCOUNT E
 WHERE P.Pat_Mrn = E.Pat_Mrn(+);

SELECT p.pat_mrn,
       e.encounter_id,
       (SELECT XMLElement("EncounterDiagnosis",
                          XMLElement("CSN", ce.csn_id),
                          XMLAgg((SELECT XMLAgg(XMLElement("Diagnosis",
                                                          XMLElement("ID",
                                                                     dx.dx_id),
                                                          XMLElement("DiagnosisCodes",
                                                                     XMLAgg(XMLElement("Code",
                                                                                       dx.ic)))))
                                   FROM dx
                                  WHERE dx.dx_id = ce.dx_id
                                  GROUP BY dx.dx_id)))
          FROM encounter_dx ce
         WHERE ce.encounter_id = e.encounter_id
         GROUP BY ce.csn_id) AS Orderxml
  FROM patient p
  LEFT OUTER JOIN encount e
    ON p.pat_mrn = e.pat_mrn