This discussion is archived
10 Replies Latest reply: Sep 19, 2013 11:48 AM by Jason_(A_Non) RSS

XML help

user7431648 Newbie
Currently Being Moderated

Hi,

 

I am having an issue writing this XML query..It was similar to one of the query I posted ..

but with more nested ...

 

{code}

Create table patient (pat_mrn varchar2(100)) ;

  Insert into patient values ('63280');

 

  Create table encount (pat_mrn varchar2(100), encounter_id varchar2(1000));

  Insert into encount values ('63280', '42');

 

  Create table rxnor (medid varchar2(1000),codelevel varchar2(1000),term varchar2(1000));

  Insert into rxnor values ('9721','966253','MED_FORM_STRENGTH');

  Insert into rxnor values ('9721','40144','MED_ONLY');

  Insert into rxnor values ('9721','10582','MED_ONLY');

  Insert into rxnor values ('12886','142439','MED_ONLY');

  Insert into rxnor values ('12886','5489','MED_ONLY');

  Insert into rxnor values ('12886','161','MED_ONLY');

 

 

  create table medications (medid varchar2(1000), encounter_id varchar2(1000),refills number, sig varchar(1000));

 

  Insert into medications values ('9721','42',25, 'take 2');

  Insert into medications values ('12886','42',2, 'take 11');

 

{/code}

 

O/p :

 

o/p

   {code}

 

  <Medications>

  <Medication>

  <ORDID>9721</ORDID>

  <SimpleMed>

  <ERXID>9721</ERXID>

  <RxNormCodes>

  <RxNorm>

  <CodeLevel>966253</CodeLevel>

  <TermType>MED_FORM_STRENGTH</TermType>

  </RxNorm>

  <RxNorm>

  <CodeLevel>40144</CodeLevel>

  <TermType>MED_ONLY</TermType>

  </RxNorm>

  <RxNorm>

  <CodeLevel>10582</CodeLevel>

  <TermType>MED_ONLY</TermType>

  </RxNorm>

  </SimpleMed>

  <Refills>25</Refills>

  <Sig>Take 2</Sig>

  </Medication>

 

  <Medication>

  <ORDID>12886</ORDID>

  <SimpleMed>

  <ERXID>12886</ERXID>

  <RxNormCodes>

  <RxNorm>

  <CodeLevel>142439</CodeLevel>

  <TermType>MED_ONLY</TermType>

  </RxNorm>

  <RxNorm>

  <CodeLevel>5489</CodeLevel>

  <TermType>MED_ONLY</TermType>

  </RxNorm>

  <RxNorm>

  <CodeLevel>161</CodeLevel>

  <TermType>MED_ONLY</TermType>

  </RxNorm>

  </SimpleMed>

  <Refills>2</Refills>

  <Sig>Take 11</Sig>

  </Medication>

 

</Medications>

 

{/code}

 

Only the patient record table is mandatory. They may or may not have the encounter id in the med table.

So, please don't join all 3 tables in the from clause..

  • 1. Re: XML help
    Jason_(A_Non) Expert
    Currently Being Moderated

    FYI, you are missing the closing </RxNormCodes> tag from both locations.

    > They may or may not have the encounter id in the med table.

    So what does the XML look like when there is only the patient table and no child encount, medications, rxnor rows?  As it appears your data model is patient -0:M-> encount -1:M-> medications -1:M-> rxnor.  What is the minimum set of data that needs to exist in order for XML to be reported on it?

     

    It would still appear to be a one level deeper variation of either answers to you in Forming query using xml forest but more details are needed still.

  • 2. Re: XML help
    user7431648 Newbie
    Currently Being Moderated

    Jason,

     

      For a patient there should be 1 entry.  Remaining tables having encounter id is optional. If we don't have medication for that encounter we report it as null.

    like

    {code}

    <p>

       <patient> 123 </patient>

       <encounter></encounter>

         <Medications

           <Medication>

            <RxNormcodes>

            <RxNorm>

            </RxNorm>

            </RxNormcodes>
         </Medication>   

        </Medications>

     

    As long as there is only entry in patient table ...we report it whether they have encounter or meds.

         

     

    This is the code which was written..But here they are adding 3 tables at the end & joining.

    But I wanted to form a select statement for each block. Each block I mean ...If I need to add say lab results tomorrow for an encounter, if I join at the end, the lab result set will be repeated for all medications...

     

    So, can u modify this code to add medications table in the Select statement & not at the from clause.

     

    {code}

     

    SELECT XMLELEMENT

             ("Medication",

              XMLELEMENT ("ORDID", m.medid),

              XMLFOREST

        (med_list_t

         (m.medid,

           (CAST

             (MULTISET

        (SELECT r.codelevel, r.term

        FROM rxnor r

        WHERE m.medid = r.medid (+))

              AS rxnorm_list_t))) AS "SimpleMed"),

              XMLELEMENT ("Refills", m.refills),

              XMLELEMENT ("Sig", m.sig)) AS "o/p"

       FROM   patient p, encount e, medications m

       WHERE  p.pat_mrn = e.pat_mrn

       AND    e.encounter_id = m.encounter_id

     

    {/code}

     

     

    This is the o/p :

     

    {code

    <Medication>

      <ORDID>9721</ORDID>

      <SimpleMed>

      <ERXID>9721</ERXID>

      <RxNormCodes>

      <RxNorm>

      <CodeLevel>966253</CodeLevel>

      <TermType>MED_FORM_STRENGTH</TermType>

      </RxNorm>

      <RxNorm>

      <CodeLevel>40144</CodeLevel>

      <TermType>MED_ONLY</TermType>

      </RxNorm>

      <RxNorm>

      <CodeLevel>10582</CodeLevel>

      <TermType>MED_ONLY</TermType>

      </RxNorm>

      </RxNormCodes>

      </SimpleMed>

      <Refills>25</Refills>

      <Sig>take 2</Sig>

    </Medication>

     

    <Medication>

      <ORDID>12886</ORDID>

      <SimpleMed>

      <ERXID>12886</ERXID>

      <RxNormCodes>

      <RxNorm>

      <CodeLevel>142439</CodeLevel>

      <TermType>MED_ONLY</TermType>

      </RxNorm>

      <RxNorm>

      <CodeLevel>5489</CodeLevel>

      <TermType>MED_ONLY</TermType>

      </RxNorm>

      <RxNorm>

      <CodeLevel>161</CodeLevel>

      <TermType>MED_ONLY</TermType>

      </RxNorm>

      </RxNormCodes>

      </SimpleMed>

      <Refills>2</Refills>

      <Sig>take 11</Sig>

    </Medication>

    {/code}

     

    Let me know if u still have issues.

  • 3. Re: XML help
    user7431648 Newbie
    Currently Being Moderated

    Any luck?? Is it still unclear??

  • 4. Re: XML help
    Jason_(A_Non) Expert
    Currently Being Moderated

    As a reminder, I am just one of the many volunteers on this forum and so come here to answer questions as my time and knowledge allow.

     

    That said, one of your sample XMLs shows <p><patient>123</patient><encounter></encounter> ...

    Where is that additional XML coming from since it is not part SQL/XML statement we are working with above?  Just trying to make sure see the bigger picture to understand how all the information you are providing ties together as right now your query only needs to be against the medications and rxnor tables as it pulls nothing else from the other two tables.  Depending upon what version of Oracle you have, the optimizer may even prune those additional tables from the query.

  • 5. Re: XML help
    user7431648 Newbie
    Currently Being Moderated

    Thanks Jason for taking time replying back.

     

    Here is the o/p I needed.

     

    {code}

    <Medications>

      <Medication>

      <ORDID>9721</ORDID>

      <SimpleMed>

      <ERXID>9721</ERXID>

      <RxNormCodes>

      <RxNorm>

      <CodeLevel>966253</CodeLevel>

      <TermType>MED_FORM_STRENGTH</TermType>

      </RxNorm>

      <RxNorm>

      <CodeLevel>40144</CodeLevel>

      <TermType>MED_ONLY</TermType>

      </RxNorm>

      <RxNorm>

      <CodeLevel>10582</CodeLevel>

      <TermType>MED_ONLY</TermType>

      </RxNorm>

    </RxNormCodes>

      </SimpleMed>

      <Refills>25</Refills>

      <Sig>Take 2</Sig>

      </Medication>

     

      <Medication>

      <ORDID>12886</ORDID>

      <SimpleMed>

      <ERXID>12886</ERXID>

      <RxNormCodes>

      <RxNorm>

      <CodeLevel>142439</CodeLevel>

      <TermType>MED_ONLY</TermType>

      </RxNorm>

      <RxNorm>

      <CodeLevel>5489</CodeLevel>

      <TermType>MED_ONLY</TermType>

      </RxNorm>

      <RxNorm>

      <CodeLevel>161</CodeLevel>

      <TermType>MED_ONLY</TermType>

      </RxNorm>

    </RxNormCodes>

      </SimpleMed>

      <Refills>2</Refills>

      <Sig>Take 11</Sig>

      </Medication>

     

    </Medications>

     

    {/code}

     

    I do have the code which does this : But the issue here is instead of joining the table medication at from clause at the end, can we do it in the Select (like we did it for other query last time)

     

    {code}

     

    CREATE OR REPLACE TYPE "RxNorm" AS OBJECT

       ("CodeLevel" VARCHAR2(1000),

       "TermType" VARCHAR2(1000));

     

     

    CREATE OR REPLACE TYPE RxNorm_list_t AS TABLE OF "RxNorm";

     

     

    CREATE OR REPLACE TYPE Med_list_t AS OBJECT

    ("ERXID" NUMBER(18),

    "RxNormCodes" RxNorm_list_t);

     

     

    SELECT XMLELEMENT

      ("Medication",

      XMLELEMENT ("ORDID", m.medid),

      XMLFOREST

      (med_list_t

      (m.medid,

      (CAST

      (MULTISET

      (SELECT r.codelevel, r.term

      FROM rxnor r

      WHERE m.medid = r.medid (+))

      AS rxnorm_list_t))) AS "SimpleMed"),

      XMLELEMENT ("Refills", m.refills),

      XMLELEMENT ("Sig", m.sig)) AS "o/p"

      FROM patient p, encount e, medications m

      WHERE p.pat_mrn = e.pat_mrn

      AND e.encounter_id = m.encounter_id

     

    {/code}

     

    Can we remove the medications from the FROM clause & use it along with rxnor table ...

    The reason is : There are so many other tables that needs to be joined based on  encounter id. 

                           Say for example : orders table...A patient encounter has 5 orders. If I join it along

                         with 3 tables in the from clause, I will end up with 5*3 = 15 records ...data repeating.

                

    Let me know if it's still unclear.

     

    Thanks.

  • 6. Re: XML help
    Jason_(A_Non) Expert
    Currently Being Moderated

    This produces your output given your sample input.  I'm sure it needs some tweaking to handle all your various data situations, but it is a starter and shows you how some of the nesting works.

    SELECT XMLElement("Medications",
           (SELECT XMLAgg(XMLElement("Medication",
                            XMLELEMENT("ORDID", m.medid),
                            XMLElement("SimpleMed",
                              XMLElement("ERXID", m.medid),
                              XMLElement("RxNormCodes",
                              XMLAgg(XMLElement("RxNorm",
                                       XMLForest(r.codelevel AS "CodeLevel",
                                                 r.term AS "TermType")))
                              )),
                           XMLForest(m.refills AS "Refills",
                                     m.sig AS "Sig"))
                          )
              FROM medications m
                   LEFT OUTER JOIN rxnor r
                     ON (m.medid = r.medid)
             WHERE e.encounter_id = m.encounter_id
             GROUP BY m.medid, m.refills, m.sig)) AS "o/p"
      FROM patient p, encount e
     WHERE p.pat_mrn = e.pat_mrn; 

    produces

    <Medications>
        <Medication>
            <ORDID>9721</ORDID>
            <SimpleMed>
                <ERXID>9721</ERXID>
                <RxNormCodes>
                    <RxNorm>
                        <CodeLevel>966253</CodeLevel>
                        <TermType>MED_FORM_STRENGTH</TermType>
                    </RxNorm>
                    <RxNorm>
                        <CodeLevel>40144</CodeLevel>
                        <TermType>MED_ONLY</TermType>
                    </RxNorm>
                    <RxNorm>
                        <CodeLevel>10582</CodeLevel>
                        <TermType>MED_ONLY</TermType>
                    </RxNorm>
                </RxNormCodes>
            </SimpleMed>
            <Refills>25</Refills>
            <Sig>take 2</Sig>
        </Medication>
        <Medication>
            <ORDID>12886</ORDID>
            <SimpleMed>
                <ERXID>12886</ERXID>
                <RxNormCodes>
                    <RxNorm>
                        <CodeLevel>142439</CodeLevel>
                        <TermType>MED_ONLY</TermType>
                    </RxNorm>
                    <RxNorm>
                        <CodeLevel>5489</CodeLevel>
                        <TermType>MED_ONLY</TermType>
                    </RxNorm>
                    <RxNorm>
                        <CodeLevel>161</CodeLevel>
                        <TermType>MED_ONLY</TermType>
                    </RxNorm>
                </RxNormCodes>
            </SimpleMed>
            <Refills>2</Refills>
            <Sig>take 11</Sig>
        </Medication>
    </Medications>

     

    Hint:  If you use the Advanced Editor, one of the formatting options looks like >> and you can pick Syntax Highlighting -> SQL or XML as appropriate.  The advanced editor is not the default when creating or replying to a post.

  • 7. Re: XML help
    user7431648 Newbie
    Currently Being Moderated

    Jason,

     

      Looks like there is 1 defect in this..

    1. SELECT XMLElement("Medications"
    2.        (SELECT XMLAgg(XMLElement("Medication"
    3.                         XMLELEMENT("ORDID", m.medid), 
    4.                         XMLElement("SimpleMed"
    5.                           XMLElement("ERXID", m.medid), 
    6.                           XMLElement("RxNormCodes"
    7.                           XMLAgg(XMLElement("RxNorm"
    8.                                    XMLForest(r.codelevel AS "CodeLevel"
    9.                                                         NULL as "Code"
    10.                                              r.term AS "TermType"))) 
    11.                           )), 
    12.                        XMLForest(m.refills AS "Refills"
    13.                                  m.sig AS "Sig")) 
    14.                       ) 
    15.           FROM medications m 
    16.                LEFT OUTER JOIN rxnor r 
    17.                  ON (m.medid = r.medid) 
    18.          WHERE e.encounter_id = m.encounter_id 
    19.          GROUP BY m.medid, m.refills, m.sig)) AS "o/p" 
    20.   FROM patient p, encount e 
    21. WHERE p.pat_mrn = e.pat_mrn;  


    If u introduce a null value field (italicized)  it doesn't show the tag "Code" at all.


  • 8. Re: XML help
    user7431648 Newbie
    Currently Being Moderated

    Jason,

     

    I think this should work :

     

    Select
    (SELECT XMLELEMENT
                ("Medications",
                 XMLAGG
                   (XMLELEMENT
               ("Medication",
                XMLELEMENT ("ORDID", m.medid),
                XMLELEMENT
                  ("SimpleMed",
                   XMLELEMENT ("ERXID", m.medid),
                   XMLELEMENT
                     ("RxNormCodes",
                XMLAGG
                  (XMLELEMENT
                    ("RxNorm",
                     XMLELEMENT ("CodeLevel", r.codelevel),
                     XMLELEMENT ("Code", NULL),
                     XMLELEMENT ("TermType", r.term))))),
                XMLELEMENT ("Refills", m.refills),
                XMLELEMENT ("Sig", m.sig))))
             FROM   medications m, rxnor r
             WHERE  e.encounter_id = m.encounter_id 
             AND    m.medid = r.medid(+)
             GROUP  BY m.medid, m.refills, m.sig
    )
     FROM patient p, encount e 
    WHERE p.pat_mrn = e.pat_mrn;  

     

    But I got a small doubt on this code :

    If I want to include another table say abc & introduce a new tag after " Medications "where should I add group by clause.

     

    Ex :

     

    Select
    (SELECT XMLELEMENT
                ("Medications",
                         XMLELEMENT("User", abc.id)
                 XMLAGG
                   (XMLELEMENT
               ("Medication",
                XMLELEMENT ("ORDID", m.medid),
                XMLELEMENT
                  ("SimpleMed",
                   XMLELEMENT ("ERXID", m.medid),
                   XMLELEMENT
                     ("RxNormCodes",
                XMLAGG
                  (XMLELEMENT
                    ("RxNorm",
                     XMLELEMENT ("CodeLevel", r.codelevel),
                     XMLELEMENT ("Code", NULL),
                     XMLELEMENT ("TermType", r.term))))),
                XMLELEMENT ("Refills", m.refills),
                XMLELEMENT ("Sig", m.sig))))
             FROM   medications m, rxnor r, abc
             WHERE  e.encounter_id = m.encounter_id 
             AND    m.medid = r.medid(+)
             AND    abc.encounter_id(+) = e.encounter_id
             GROUP  BY m.medid, m.refills, m.sig) -- If I add group by abc.id it gives me an error.
     FROM patient p, encount e 
    WHERE p.pat_mrn = e.pat_mrn;  
  • 9. Re: XML help
    user7431648 Newbie
    Currently Being Moderated

    Never mind..I got it.

    Select (SELECT XMLELEMENT("Medications",
                              XMLELEMENT("UserID", abc.id),
                              (SELECT XMLAGG(XMLELEMENT("Medication",
                                                        XMLELEMENT("ORDID", m.medid),
                                                        XMLELEMENT("SimpleMed",
                                                                   XMLELEMENT("ERXID",
                                                                              m.medid),
                                                                   XMLELEMENT("RxNormCodes",
                                                                              XMLAGG(XMLELEMENT("RxNorm",
                                                                                                XMLELEMENT("CodeLevel",
                                                                                                           r.codelevel),
                                                                                                XMLELEMENT("Code",
                                                                                                           NULL),
                                                                                                XMLELEMENT("TermType",
                                                                                                           r.term))))),
                                                        XMLELEMENT("Refills",
                                                                   m.refills),
                                                        XMLELEMENT("Sig", m.sig)))
                                 FROM medications m, rxnor r
                                WHERE e.encounter_id = m.encounter_id(+)
                                  AND m.medid = r.medid(+)
                                GROUP BY m.MedId, m.refills, m.sig))
              FROM abc 
             WHERE abc.ENCOUNTER_ID(+) = e.encounter_id
             GROUP BY abc.MEDS_HX_REV_USER_ID)
      FROM patient p, encount e
     WHERE p.pat_mrn = e.pat_mrn;
  • 10. Re: XML help
    Jason_(A_Non) Expert
    Currently Being Moderated

    Per the XMLFOREST documentation

    - If value_expr is null, then no element is created for that value_expr.

    That is why the tag did not show when the data source was null.

     

    As you noticed, the GROUP by was associated with the XMLAgg and you were trying to group by a column that was not within the XMLAgg.  Glad you made progress.

Legend

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