1 Reply Latest reply: Oct 2, 2013 7:43 AM by odie_63 RSS

    xml error -- missing simple logic

    user7431648

      Hi,

       

       

      Firstly table structure :

       

       

      [code]

        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);

       

       

      [/code]

       

       

       

       

      O/p :

       

       

      [code]

      <EncounterDiagnosis>

      <CSN>300</CSN>

      <Diagnosis>

      <ID>106</ID>

      <DiagnosisCodes>

      <Code>410</Code>

      <Code>420</Code>

      </DiagnosisCodes>

      </Diagnosis>

      </EncounterDiagnosis>

      [/code]

       

       

       

       

      SELECT

      (

      SELECT

      XMLELEMENT("EncounterDiagnosis",

            XMLELEMENT("PrimaryDiagnosis",CE.CSN_ID), -- ONCE I KEEP THIS I GET AN ERROR SYAING 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(+);

        • 1. Re: xml error -- missing simple logic
          odie_63

          The following query kinda works, but not very elegant nor optimized.

          I'll try to see if I can come up with something better :

           

          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

          ;