This discussion is archived
7 Replies Latest reply: Sep 17, 2013 12:27 PM by Jason_(A_Non) RSS

Forming query using xml forest

user7431648 Newbie
Currently Being Moderated

Hi,

 

I need the code for the following. I did try...but getting additional tags.

 

  Create table patient (pat_mrn varchar2(100)) ; Create table encount (pat_mrn varchar2(100), encounter_id varchar2(1000)); Create table oper    (encounter_id varchar2(1000), comp_name varchar2(1000));  Insert into patient values ('63280'); Insert into encount values ('63280', '42'); Insert  into oper values  (42, 'sugar'); Insert  into oper  values (42, 'sbp'); Insert  into oper  values (42, 'dbp');  CREATE OR REPLACE TYPE COMPONENT AS OBJECT (    "ID" VARCHAR2(1000));  CREATE OR REPLACE TYPE component_list_t AS TABLE OF COMPONENT;  CREATE OR REPLACE TYPE cm_results_o_t AS OBJECT (RES_LIST component_list_t);    O/p required : <Patient> <pat_mrn> 63280 </pat_mrn> <Results> <Component> <ID>sugar</ID> </Component> <Component> <ID>sbp</ID> </Component> <Component> <ID>dbp</ID> </Component>  </Results> </patient> 

 

 

Code I wrote :

 

Select P.PAT_MRN, XMLELEMENT("Patient", (XMLELEMENT("pat_mrn", P.pat_mrn)), (XMLELEMENT("Results", XMLForest(cm_results_o_t(CAST(MULTISET (SELECT O.COMP_NAME AS "ID" FROM oper O WHERE O.ENCOUNTER_ID = E.ENCOUNTER_ID) AS component_list_t)) AS "Results")))) AS Orderxml FROM PATIENT P JOIN ENCOUNT E ON P.PAT_MRN = E.PAT_MRN AND P.PAT_MRN = '63280' AND E.ENCOUNTER_ID = 42  

 

 

So, we can clearly see there are lot of additional tags .. o/p i am getting

 

 <Patient> <pat_mrn>63280</pat_mrn> <Results> <Results> <RES_LIST> <COMPONENT> <ID>sugar</ID> </COMPONENT> <COMPONENT> <ID>sbp</ID> </COMPONENT> <COMPONENT> <ID>dbp</ID> </COMPONENT> </RES_LIST> </Results> </Results> </Patient> 

 

 

I am new to xml..So, any help is appreciated.

 

Thanks.

  • 1. Re: Forming query using xml forest
    user7431648 Newbie
    Currently Being Moderated

    I modified the code :

    [code]

     

    Select P.PAT_MRN,

               XMLELEMENT("Patient",

                          (XMLELEMENT("pat_mrn", P.pat_mrn)),

                          (XMLForest(cm_results_o_t(CAST(MULTISET

                                                                    (SELECT O.COMP_NAME AS "ID"

                                                                       FROM oper O

                                                                      WHERE O.ENCOUNTER_ID =

                                                                            E.ENCOUNTER_ID) AS

                                                                    component_list_t)) AS

                                               "Results"))) AS Orderxml

         FROM PATIENT P

         JOIN ENCOUNT E

           ON P.PAT_MRN = E.PAT_MRN

          AND P.PAT_MRN = '63280'

          AND E.ENCOUNTER_ID = 42

    [/code]

     

    Need the Res_LIST tag to be removed which is coming from object type.

  • 2. Re: Forming query using xml forest
    Jason_(A_Non) Expert
    Currently Being Moderated

    Is there a reason you are using an object type to try and create the XML?  I ask simply because your example can be re-written to not use an object.

    SELECT p.pat_mrn,
           XMLElement("Patient",
             XMLElement("pat_mrn", P.pat_mrn),
             XMLAgg(XMLElement("Component",
                      XMLElement("ID",o.comp_name)))) rslt
      FROM patient p
           INNER JOIN encount e
             ON (P.PAT_MRN = E.PAT_MRN
                 AND P.PAT_MRN = '63280'
                 AND E.ENCOUNTER_ID = 42)
           INNER JOIN oper o
             ON (e.encounter_id = o.encounter_id)
    GROUP by p.pat_mrn;

    produces

    
    
    
    PAT_MRN
    RSLT
    ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
    
    
    
    63280  
    <Patient><pat_mrn>63280</pat_mrn><Component><ID>dbp</ID></Component><Component><ID>sbp</ID></Component><Component><ID>sugar</ID></Component></Patient>

    There are other variations that work too.

  • 3. Re: Forming query using xml forest
    user7431648 Newbie
    Currently Being Moderated

    Thanks...I am new to XML..So, I don't have any idea as to which approach I have to use.

    This works too...Thanks a ton!!!!

  • 4. Re: Forming query using xml forest
    user7431648 Newbie
    Currently Being Moderated

    Here I can't do an inner join  on oper at the end...

    I have so many tables to join & mostly outer join. So, I have to do a select in the select statement otherwise the results will be repeated as we keep on joining the tables.

  • 5. Re: Forming query using xml forest
    Jason_(A_Non) Expert
    Currently Being Moderated

    Then the following is one of the variations that produces the same result

    SELECT p.pat_mrn,
           XMLElement("Patient",
             XMLElement("pat_mrn", P.pat_mrn),
             (SELECT XMLAgg(XMLElement("Component",
                             XMLElement("ID",o.comp_name)))
                      FROM oper o
                     WHERE e.encounter_id = o.encounter_id))
      FROM patient p
           INNER JOIN encount e
             ON (P.PAT_MRN = E.PAT_MRN
                 AND P.PAT_MRN = '63280'
                 AND E.ENCOUNTER_ID = 42);
  • 6. Re: Forming query using xml forest
    user7431648 Newbie
    Currently Being Moderated

    Thanks Jason..It works,

    I have a table close to million records...So, which option will be faster : ur approach or Cast/XMLForest??

     

    Modifying ur code to add result tag:

     

    [code]

    SELECT p.pat_mrn,

           XMLElement("Patient",

             XMLElement("pat_mrn", P.pat_mrn),

             XMLELEMENT("Results",(SELECT XMLAgg(XMLElement("Component",

                             XMLElement("ID",o.comp_name)))

                      FROM oper o

                     WHERE e.encounter_id = o.encounter_id)))

      FROM patient p

           INNER JOIN encount e

             ON (P.PAT_MRN = E.PAT_MRN

                 AND P.PAT_MRN = '63280'

                 AND E.ENCOUNTER_ID = 42);

    [/code]

  • 7. Re: Forming query using xml forest
    Jason_(A_Non) Expert
    Currently Being Moderated

    The options I wrote up should be faster because there is not a cast into an object and then the work to parse that object.  In other words, less structures to deal with.  That said, the difference may be minor in execution time.  The only real answer to that question is for you to test both methods and pick the one that works better for you.

Incoming Links

Legend

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