7 Replies Latest reply: Sep 17, 2013 2:27 PM by Jason_(A_Non) RSS

    Forming query using xml forest

    user7431648

      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

          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)

            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

              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

                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)

                  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

                    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)

                      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.