This discussion is archived
1 Reply Latest reply: Oct 2, 2013 5:43 AM by odie_63 RSS

xml error -- missing simple logic

user7431648 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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

    ;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points