Home » Developer & Programmer » JDeveloper, Java & XML » XML issue (Oracle 11g, XP)
XML issue [message #596076] Tue, 17 September 2013 16:39 Go to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Hi,

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


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

   
   
   o/p
   
   <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>


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..

Here is what I tried..but couldn't get beyond this :

[/code]

     
CREATE OR REPLACE TYPE RxNorm      AS OBJECT (    CodeLevel VARCHAR2(1000),
                                                  "Code" VARCHAR2(1000),
                                                  PrimaryFlag  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      ( Medication_id NUMBER(18),
                                                   RxNormCodes RxNorm_list_t);
                     
     select                  Med_list_t(ERXID -- Not sure how to get ERXid from below cast      
                              ,CAST(MULTISET
                                       (SELECT  r.medication_id         AS "ERXID",
                                                r.rxnorm_code_level     AS "CodeLevel",
                                                r.rxnorm_code           AS "Code",
                                                r.rxnorm_primary_yn     AS "PrimaryFlag",
                                                r.rxnorm_term_type      AS "Termtype"
                                                
                                            FROM RxNor        r,
                                                 medications m
                                          where r.medication_id = m.medid
                                            and m.encounter_id = e.encounter_id) AS RxNorm_list_t))  
                                         

 FROM PATIENT P JOIN encount E
                                   ON P.PAT_ID = E.PAT_ID 
                                   AND P.PATIENT_MRN = '63280' AND E.ENCOUNTER_ID = 42
                                   




Thanks.

[Updated on: Tue, 17 September 2013 20:33]

Report message to a moderator

Re: XML issue [message #596095 is a reply to message #596076] Wed, 18 September 2013 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You could investigate the way Solomon gave you in your previous topic.
It does not need any object type and is much easier to read and understand.

Re: XML issue [message #596103 is a reply to message #596095] Wed, 18 September 2013 01:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7994
Registered: November 2002
Location: California, USA
Senior Member
Take your pick of the methods below. I used outer joins in all of them.

SCOTT@orcl12c> -- test data:
SCOTT@orcl12c> COLUMN pat_mrn	   FORMAT A7
SCOTT@orcl12c> COLUMN encounter_id FORMAT A12
SCOTT@orcl12c> COLUMN medid	   FORMAT A5
SCOTT@orcl12c> COLUMN codelevel    FORMAT A9
SCOTT@orcl12c> COLUMN term	   FORMAT A20
SCOTT@orcl12c> COLUMN sig	   FORMAT A7
SCOTT@orcl12c> SELECT * FROM patient
  2  /

PAT_MRN
-------
63280

1 row selected.

SCOTT@orcl12c> SELECT * FROM encount
  2  /

PAT_MRN ENCOUNTER_ID
------- ------------
63280   42

1 row selected.

SCOTT@orcl12c> SELECT * FROM medications
  2  /

MEDID ENCOUNTER_ID    REFILLS SIG
----- ------------ ---------- -------
9721  42                   25 take 2
12886 42                    2 take 11

2 rows selected.

SCOTT@orcl12c> SELECT * FROM rxnor
  2  /

MEDID CODELEVEL TERM
----- --------- --------------------
9721  966253    MED_FORM_STRENGTH
9721  40144     MED_ONLY
9721  10582     MED_ONLY
12886 142439    MED_ONLY
12886 5489      MED_ONLY
12886 161       MED_ONLY

6 rows selected.

SCOTT@orcl12c> -- object types:
SCOTT@orcl12c> CREATE OR REPLACE TYPE "RxNorm" AS OBJECT
  2    ("CodeLevel"    VARCHAR2(1000),
  3  	"TermType"     VARCHAR2(1000));
  4  /

Type created.

SCOTT@orcl12c> CREATE OR REPLACE TYPE RxNorm_list_t AS TABLE OF "RxNorm";
  2  /

Type created.

SCOTT@orcl12c> CREATE OR REPLACE TYPE Med_list_t AS OBJECT
  2    ("ERXID"        NUMBER(18),
  3  	"RxNormCodes"  RxNorm_list_t);
  4  /

Type created.

SCOTT@orcl12c> -- query to get xml:
SCOTT@orcl12c> SELECT XMLELEMENT
  2  	      ("Medication",
  3  	       XMLELEMENT ("ORDID", m.medid),
  4  	       XMLFOREST
  5  		(med_list_t
  6  		  (m.medid,
  7  		    (CAST
  8  		      (MULTISET
  9  			(SELECT r.codelevel, r.term
 10  			 FROM	rxnor r
 11  			 WHERE	m.medid = r.medid (+))
 12  		       AS rxnorm_list_t))) AS "SimpleMed"),
 13  	       XMLELEMENT ("Refills", m.refills),
 14  	       XMLELEMENT ("Sig", m.sig)) AS "o/p"
 15  FROM   patient p, encount e, medications m
 16  WHERE  p.pat_mrn = e.pat_mrn (+)
 17  AND    e.encounter_id = m.encounter_id (+)
 18  /

o/p
--------------------------------------------------------------------------------
<Medication><ORDID>9721</ORDID><SimpleMed><ERXID>9721</ERXID><RxNormCodes><RxNor
m><CodeLevel>966253</CodeLevel><TermType>MED_FORM_STRENGTH</TermType></RxNorm><R
xNorm><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><RxN
orm><CodeLevel>142439</CodeLevel><TermType>MED_ONLY</TermType></RxNorm><RxNorm><
CodeLevel>5489</CodeLevel><TermType>MED_ONLY</TermType></RxNorm><RxNorm><CodeLev
el>161</CodeLevel><TermType>MED_ONLY</TermType></RxNorm></RxNormCodes></SimpleMe
d><Refills>2</Refills><Sig>take 11</Sig></Medication>


2 rows selected.

SCOTT@orcl12c> -- query to get indented xml document:
SCOTT@orcl12c> SELECT XMLSERIALIZE
  2  	      (DOCUMENT
  3  		(XMLELEMENT
  4  		  ("Medication",
  5  		   XMLELEMENT ("ORDID", m.medid),
  6  		   XMLFOREST
  7  		    (med_list_t
  8  		      (m.medid,
  9  			(CAST
 10  			  (MULTISET
 11  			    (SELECT r.codelevel, r.term
 12  			     FROM   rxnor r
 13  			     WHERE  m.medid = r.medid (+))
 14  			   AS rxnorm_list_t))) AS "SimpleMed"),
 15  		   XMLELEMENT ("Refills", m.refills),
 16  		   XMLELEMENT ("Sig", m.sig)))
 17  	       INDENT) AS "o/p"
 18  FROM   patient p, encount e, medications m
 19  WHERE  p.pat_mrn = e.pat_mrn (+)
 20  AND    e.encounter_id = m.encounter_id (+)
 21  /

o/p
--------------------------------------------------------------------------------
<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>


2 rows selected.

SCOTT@orcl12c> -- Solomon's method:
SCOTT@orcl12c> SELECT  XMLSERIALIZE
  2  	       (DOCUMENT
  3  		XMLELEMENT
  4  		  ("Medication",
  5  		   XMLELEMENT ("ORDID", m.medid),
  6  		   XMLELEMENT
  7  		     ("SimpleMed",
  8  		      XMLELEMENT ("ERXID", m.medid),
  9  		      XMLELEMENT
 10  			("RxNormCodes",
 11  			 XMLAGG
 12  			   (XMLELEMENT
 13  			     ("RxNorm",
 14  			      XMLELEMENT ("CodeLevel", r.codelevel),
 15  			      XMLELEMENT ("TermType", r.term))))),
 16  		   XMLELEMENT ("Refills", m.refills),
 17  		   XMLELEMENT ("Sig", m.sig)) AS CLOB INDENT) "o/p"
 18  FROM   patient p, encount e, medications m, rxnor r
 19  WHERE  p.pat_mrn = e.pat_mrn (+)
 20  AND    e.encounter_id = m.encounter_id (+)
 21  AND    m.medid = r.medid(+)
 22  GROUP  BY m.medid, m.refills, m.sig
 23  /

o/p
--------------------------------------------------------------------------------
<Medication>
  <ORDID>9721</ORDID>
  <SimpleMed>
    <ERXID>9721</ERXID>
    <RxNormCodes>
      <RxNorm>
        <CodeLevel>966253</CodeLevel>
        <TermType>MED_FORM_STRENGTH</TermType>
      </RxNorm>
      <RxNorm>
        <CodeLevel>10582</CodeLevel>
        <TermType>MED_ONLY</TermType>
      </RxNorm>
      <RxNorm>
        <CodeLevel>40144</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>161</CodeLevel>
        <TermType>MED_ONLY</TermType>
      </RxNorm>
      <RxNorm>
        <CodeLevel>5489</CodeLevel>
        <TermType>MED_ONLY</TermType>
      </RxNorm>
    </RxNormCodes>
  </SimpleMed>
  <Refills>2</Refills>
  <Sig>take 11</Sig>
</Medication>


2 rows selected.

Re: XML issue [message #596165 is a reply to message #596103] Wed, 18 September 2013 08:18 Go to previous messageGo to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
This was very helpful.
How can this be modified if i would like to use only patient & encounter table in the from clause & use the medications table in the select clause...Is it possible?

The reason I am asking is...i have to join so many other tables ...So, we have orders table as well which is based on encounter id..So, if we join that to the above query u wrote, the result set will be repeated for medications. So, I am trying to write an individual select for each block
& not trying to join all the tables at the end (from clause).

May be like this..

 Select
    xmlforest (......
           from medications)

    from patient join encounter
..

Re: XML issue [message #596179 is a reply to message #596165] Wed, 18 September 2013 11:45 Go to previous messageGo to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
The issue comes in when u add some more tables based on encounter id...
Say, I am adding results table ...you can see that the medid is repeated for all component name...(file attached)


    
    Select p.patient_mrn, e.encounter_id, M.MEDICATION_ID, R.RXNORM_CODE, O.COMPONENT_NAME, O.RESULT_DATE
  from PATIENT             P,
       ENCOUNTER           E,
       MEDICATIONS m,
       RXNORM        R,
       RESULTS       O
 where P.PAT_ID = E.PAT_ID(+)
   and E.ENCOUNTER_ID = m.encounter_id(+)
   and M.MEDICATION_ID = R.MEDICATION_ID(+)
   and E.ENCOUNTER_ID = O.ENCOUNTER_ID (+)
    AND P.PATIENT_MRN = '6328083' AND E.ENCOUNTER_ID = 42



See, if do an xml on this, the medid is repeated for all tests which I wanted to avoid. So, I want the results, meds to be independent of each other.

So, let me know if we can join medications at the select level & not in from clause.

Thanks,
Re: XML issue [message #596181 is a reply to message #596179] Wed, 18 September 2013 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Barbara lives in California, she will be there in a couple of hours, you have to wait a bit.
I can't help you, it is above my XML knowledge.

Regards
Michel
Re: XML issue [message #596182 is a reply to message #596181] Wed, 18 September 2013 12:17 Go to previous messageGo to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Thanks Mike for the reply. We will wait.
I was thinking of passing encounter id as input argument to the function & storing the output in the select . Any idea?


 Select 
         XMLELEMENT("p", XMLELEMENT("Patient", (XMLELEMENT("EPTID", P.PAT_ID))
                       , XMLELEMENT("Medications", fn(enc_id))
     from ....



Can we try this approach?

[Updated on: Wed, 18 September 2013 12:18]

Report message to a moderator

Re: XML issue [message #596187 is a reply to message #596179] Wed, 18 September 2013 12:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7994
Registered: November 2002
Location: California, USA
Senior Member
I don't see the problem. You are probably not doing all of the joins correctly. You need to provide create table and insert statements for the additional tables and data, a copy and paste of the query you are running and the result that you are getting from that query on that data, and an example of what you want instead.
Re: XML issue [message #596189 is a reply to message #596187] Wed, 18 September 2013 12:45 Go to previous messageGo to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Here it is :

  Create table results (pat_mrn varchar2(1000), encounter_id varchar2(1000), medid varchar2(1000),
COMPONENT_NAME	varchar2(1000));

Insert into results values ('63280','42','12886','TX');
Insert into results values ('63280','42','12886','EGFR');
Insert into results values ('63280','42','12886','LDL');
Insert into results values ('63280','42','12886','HDL');

QUERY :

 Select p.pat_mrn,
        e.encounter_id,
        M.MEDID,
        R.CODELEVEL,
        O.COMPONENT_NAME
   from PATIENT P, ENCOUNT E, MEDICATIONS m, RXNOR R, RESULTS O
  where P.PAT_MRN = E.PAT_MRN(+)
    and E.ENCOUNTER_ID = m.encounter_id(+)
    and M.MEDID = R.MEDID(+)
    and E.ENCOUNTER_ID = O.ENCOUNTER_ID(+)
    AND P.PAT_MRN = '63280'
    AND E.ENCOUNTER_ID = 42



If I use the above to form an xml, the data set is repeated.

The o/p should be on the lines :

<p>
<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> 

<Results>
<Results>
 <Component>
   <id>HDL</id>
 </Component>
 <Component>
   <id>LDL</id>
 </Component>
 <Component>
   <id>EGFR</id>
 </Component>
 <Component>
   <id>TX</id>
 </Component>
</Results> 
</p>


But if we use the above query to form XML, the medid will be repeated for each test.
I wanted join at each select rather than joining everything at the from clause..There are so many tables to be added like results.


[Updated on: Wed, 18 September 2013 12:46]

Report message to a moderator

Re: XML issue [message #596206 is a reply to message #596189] Wed, 18 September 2013 15:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7994
Registered: November 2002
Location: California, USA
Senior Member
It looks like your medications and your results are two different queries concatenated together, so the following does that.

SCOTT@orcl12c> -- test data:
SCOTT@orcl12c> COLUMN pat_mrn	     FORMAT A7
SCOTT@orcl12c> COLUMN encounter_id   FORMAT A12
SCOTT@orcl12c> COLUMN medid	     FORMAT A5
SCOTT@orcl12c> COLUMN codelevel      FORMAT A9
SCOTT@orcl12c> COLUMN term	     FORMAT A20
SCOTT@orcl12c> COLUMN sig	     FORMAT A7
SCOTT@orcl12c> COLUMN component_name FORMAT A14
SCOTT@orcl12c> SELECT * FROM patient
  2  /

PAT_MRN
-------
63280

1 row selected.

SCOTT@orcl12c> SELECT * FROM encount
  2  /

PAT_MRN ENCOUNTER_ID
------- ------------
63280   42

1 row selected.

SCOTT@orcl12c> SELECT * FROM medications
  2  /

MEDID ENCOUNTER_ID    REFILLS SIG
----- ------------ ---------- -------
9721  42                   25 take 2
12886 42                    2 take 11

2 rows selected.

SCOTT@orcl12c> SELECT * FROM rxnor
  2  /

MEDID CODELEVEL TERM
----- --------- --------------------
9721  966253    MED_FORM_STRENGTH
9721  40144     MED_ONLY
9721  10582     MED_ONLY
12886 142439    MED_ONLY
12886 5489      MED_ONLY
12886 161       MED_ONLY

6 rows selected.

SCOTT@orcl12c> SELECT * FROM results
  2  /

PAT_MRN ENCOUNTER_ID MEDID COMPONENT_NAME
------- ------------ ----- --------------
63280   42           12886 TX
63280   42           12886 EGFR
63280   42           12886 LDL
63280   42           12886 HDL

4 rows selected.

SCOTT@orcl12c> -- query:
SCOTT@orcl12c> SELECT XMLSERIALIZE (DOCUMENT XMLTYPE (t1.op1 || t2.op2) AS CLOB INDENT) AS "op"
  2  FROM   (SELECT '<p>' ||
  3  		     XMLELEMENT
  4  		       ("Medications",
  5  			XMLAGG
  6  			  (XMLELEMENT
  7  			    ("Medication",
  8  			     XMLELEMENT ("ORDID", m.medid),
  9  			     XMLELEMENT
 10  			       ("SimpleMed",
 11  				XMLELEMENT ("ERXID", m.medid),
 12  				XMLELEMENT
 13  				  ("RxNormCodes",
 14  				   XMLAGG
 15  				     (XMLELEMENT
 16  				       ("RxNorm",
 17  					XMLELEMENT ("CodeLevel", r.codelevel),
 18  					XMLELEMENT ("TermType", r.term))))),
 19  			     XMLELEMENT ("Refills", m.refills),
 20  			     XMLELEMENT ("Sig", m.sig)))) op1
 21  	     FROM   patient p, encount e, medications m, rxnor r
 22  	     WHERE  p.pat_mrn = e.pat_mrn (+)
 23  	     AND    e.encounter_id = m.encounter_id (+)
 24  	     AND    m.medid = r.medid(+)
 25  	     AND    p.pat_mrn = '63280'
 26  	     AND    e.encounter_id = 42
 27  	     GROUP  BY m.medid, m.refills, m.sig) t1,
 28  	    (SELECT XMLELEMENT
 29  		      ("Results",
 30  		       XMLAGG
 31  			 (XMLELEMENT
 32  			   ("Component",
 33  			    XMLELEMENT ("id", o.component_name))))
 34  		    || '</p>' op2
 35  	     FROM   patient p, encount e, results o
 36  	     WHERE  p.pat_mrn = e.pat_mrn (+)
 37  	     AND    e.encounter_id = o.encounter_id (+)
 38  	     AND    p.pat_mrn = '63280'
 39  	     AND    e.encounter_id = 42) t2
 40  /

op
--------------------------------------------------------------------------------
<p>
  <Medications>
    <Medication>
      <ORDID>9721</ORDID>
      <SimpleMed>
        <ERXID>9721</ERXID>
        <RxNormCodes>
          <RxNorm>
            <CodeLevel>966253</CodeLevel>
            <TermType>MED_FORM_STRENGTH</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>10582</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>40144</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>161</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>5489</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
        </RxNormCodes>
      </SimpleMed>
      <Refills>2</Refills>
      <Sig>take 11</Sig>
    </Medication>
  </Medications>
  <Results>
    <Component>
      <id>TX</id>
    </Component>
    <Component>
      <id>EGFR</id>
    </Component>
    <Component>
      <id>LDL</id>
    </Component>
    <Component>
      <id>HDL</id>
    </Component>
  </Results>
</p>


1 row selected.

Re: XML issue [message #596210 is a reply to message #596206] Wed, 18 September 2013 16:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7994
Registered: November 2002
Location: California, USA
Senior Member
I think the following is closer to what you are asking for.

SCOTT@orcl12c> SELECT XMLSERIALIZE
  2  	      (DOCUMENT XMLTYPE
  3  		 ((SELECT '<p>' FROM DUAL)
  4  		  ||
  5  		  (SELECT XMLELEMENT
  6  			    ("Medications",
  7  			     XMLAGG
  8  			       (XMLELEMENT
  9  				 ("Medication",
 10  				  XMLELEMENT ("ORDID", m.medid),
 11  				  XMLELEMENT
 12  				    ("SimpleMed",
 13  				     XMLELEMENT ("ERXID", m.medid),
 14  				     XMLELEMENT
 15  				       ("RxNormCodes",
 16  					XMLAGG
 17  					  (XMLELEMENT
 18  					    ("RxNorm",
 19  					     XMLELEMENT ("CodeLevel", r.codelevel),
 20  					     XMLELEMENT ("TermType", r.term))))),
 21  				  XMLELEMENT ("Refills", m.refills),
 22  				  XMLELEMENT ("Sig", m.sig))))
 23  		   FROM   medications m, rxnor r
 24  		   WHERE  e.encounter_id = m.encounter_id (+)
 25  		   AND	  m.medid = r.medid(+)
 26  		   GROUP  BY m.medid, m.refills, m.sig)
 27  		  ||
 28  		   (SELECT XMLELEMENT
 29  			     ("Results",
 30  			      XMLAGG
 31  				(XMLELEMENT
 32  				  ("Component",
 33  				   XMLELEMENT ("id", o.component_name))))
 34  		    FROM   results o
 35  		    WHERE  e.encounter_id = o.encounter_id (+))
 36  		   ||
 37  		   (SELECT '</p>' FROM DUAL))
 38  	       AS CLOB INDENT) "op"
 39  FROM   patient p, encount e
 40  WHERE  p.pat_mrn = e.pat_mrn (+)
 41  AND    p.pat_mrn = '63280'
 42  AND    e.encounter_id = 42
 43  /

op
--------------------------------------------------------------------------------
<p>
  <Medications>
    <Medication>
      <ORDID>9721</ORDID>
      <SimpleMed>
        <ERXID>9721</ERXID>
        <RxNormCodes>
          <RxNorm>
            <CodeLevel>966253</CodeLevel>
            <TermType>MED_FORM_STRENGTH</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>10582</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>40144</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>161</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>5489</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
        </RxNormCodes>
      </SimpleMed>
      <Refills>2</Refills>
      <Sig>take 11</Sig>
    </Medication>
  </Medications>
  <Results>
    <Component>
      <id>TX</id>
    </Component>
    <Component>
      <id>EGFR</id>
    </Component>
    <Component>
      <id>LDL</id>
    </Component>
    <Component>
      <id>HDL</id>
    </Component>
  </Results>
</p>


1 row selected.

[Updated on: Wed, 18 September 2013 16:35]

Report message to a moderator

Re: XML issue [message #596266 is a reply to message #596210] Thu, 19 September 2013 13:06 Go to previous messageGo to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Looks fine..except a small doubt..
If I want to include another table say abc & introduce a new tag after " Medications "where should I add group by clause.

SELECT XMLSERIALIZE
  	      (DOCUMENT XMLTYPE
  		 ((SELECT '<p>' FROM DUAL)
  		  ||
  		  (SELECT XMLELEMENT
  			    ("Medications",
                               [b]  XMLELEMENT ("LastReviewUser", abc.ID)[/b],
  			     XMLAGG
  			       (XMLELEMENT
  				 ("Medication",
  				  XMLELEMENT ("ORDID", m.medid),
  				  XMLELEMENT
  				    ("SimpleMed",
  				     XMLELEMENT ("ERXID", m.medid),
  				     XMLELEMENT
  				       ("RxNormCodes",
  					XMLAGG
  					  (XMLELEMENT
  					    ("RxNorm",
  					     XMLELEMENT ("CodeLevel", r.codelevel),
  					     XMLELEMENT ("TermType", r.term))))),
  				  XMLELEMENT ("Refills", m.refills),
  				  XMLELEMENT ("Sig", m.sig))))
  		   FROM   medications m, rxnor r, [b]abc[/b]
  		   WHERE  e.encounter_id = m.encounter_id (+)
  		   AND	  m.medid = r.medid(+)
                   AND    e.encounter_id = abc.encounter_id(+)
  		   GROUP  BY m.medid, m.refills, m.sig) -- If I add group by abc.id it gives me an error.
  		  ||
  		   (SELECT XMLELEMENT
  			     ("Results",
  			      XMLAGG
  				(XMLELEMENT
  				  ("Component",
  				   XMLELEMENT ("id", o.component_name))))
  		    FROM   results o
  		    WHERE  e.encounter_id = o.encounter_id (+))
  		   ||
  		   (SELECT '</p>' FROM DUAL))
  	       AS CLOB INDENT) "op"
  FROM   patient p, encount e
  WHERE  p.pat_mrn = e.pat_mrn (+)
  AND    p.pat_mrn = '63280'
  AND    e.encounter_id = 42
  /

[Updated on: Thu, 19 September 2013 13:20]

Report message to a moderator

Re: XML issue [message #596268 is a reply to message #596266] Thu, 19 September 2013 13:32 Go to previous messageGo to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
I think this should work!! What say??
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;

[Updated on: Thu, 19 September 2013 13:33]

Report message to a moderator

Re: XML issue [message #596270 is a reply to message #596266] Thu, 19 September 2013 13:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7994
Registered: November 2002
Location: California, USA
Senior Member
Here are three different methods, depending on what line you want it on.

SCOTT@orcl12c> -- test data:
SCOTT@orcl12c> COLUMN pat_mrn	     FORMAT A7
SCOTT@orcl12c> COLUMN encounter_id   FORMAT A12
SCOTT@orcl12c> COLUMN medid	     FORMAT A5
SCOTT@orcl12c> COLUMN codelevel      FORMAT A9
SCOTT@orcl12c> COLUMN term	     FORMAT A20
SCOTT@orcl12c> COLUMN sig	     FORMAT A7
SCOTT@orcl12c> COLUMN component_name FORMAT A14
SCOTT@orcl12c> SELECT * FROM patient
  2  /

PAT_MRN
-------
63280

1 row selected.

SCOTT@orcl12c> SELECT * FROM encount
  2  /

PAT_MRN ENCOUNTER_ID
------- ------------
63280   42

1 row selected.

SCOTT@orcl12c> SELECT * FROM medications
  2  /

MEDID ENCOUNTER_ID    REFILLS SIG
----- ------------ ---------- -------
9721  42                   25 take 2
12886 42                    2 take 11

2 rows selected.

SCOTT@orcl12c> SELECT * FROM rxnor
  2  /

MEDID CODELEVEL TERM
----- --------- --------------------
9721  966253    MED_FORM_STRENGTH
9721  40144     MED_ONLY
9721  10582     MED_ONLY
12886 142439    MED_ONLY
12886 5489      MED_ONLY
12886 161       MED_ONLY

6 rows selected.

SCOTT@orcl12c> SELECT * FROM results
  2  /

PAT_MRN ENCOUNTER_ID MEDID COMPONENT_NAME
------- ------------ ----- --------------
63280   42           12886 TX
63280   42           12886 EGFR
63280   42           12886 LDL
63280   42           12886 HDL

4 rows selected.

SCOTT@orcl12c> SELECT * FROM abc
  2  /

ENCOUNTER_ID         ID
------------ ----------
42                    1

1 row selected.

SCOTT@orcl12c> -- queries:
SCOTT@orcl12c> SELECT XMLSERIALIZE
  2  	      (DOCUMENT XMLTYPE
  3  		 ((SELECT '<p>' FROM DUAL)
  4  		  ||
  5  		  (SELECT XMLELEMENT ("LastReviewUser", abc.id)
  6  		    FROM   abc
  7  		    WHERE  e.encounter_id = abc.encounter_id (+))
  8  		  ||
  9  		  (SELECT XMLELEMENT
 10  			    ("Medications",
 11  			     XMLAGG
 12  			       (XMLELEMENT
 13  				 ("Medication",
 14  				  XMLELEMENT ("ORDID", m.medid),
 15  				  XMLELEMENT
 16  				    ("SimpleMed",
 17  				     XMLELEMENT ("ERXID", m.medid),
 18  				     XMLELEMENT
 19  				       ("RxNormCodes",
 20  					XMLAGG
 21  					  (XMLELEMENT
 22  					    ("RxNorm",
 23  					     XMLELEMENT ("CodeLevel", r.codelevel),
 24  					     XMLELEMENT ("TermType", r.term))))),
 25  				  XMLELEMENT ("Refills", m.refills),
 26  				  XMLELEMENT ("Sig", m.sig))))
 27  		   FROM   medications m, rxnor r
 28  		   WHERE  e.encounter_id = m.encounter_id (+)
 29  		   AND	  m.medid = r.medid(+)
 30  		   GROUP  BY m.medid, m.refills, m.sig)
 31  		   ||
 32  		   (SELECT XMLELEMENT
 33  			     ("Results",
 34  			      XMLAGG
 35  				(XMLELEMENT
 36  				  ("Component",
 37  				   XMLELEMENT ("id", o.component_name))))
 38  		    FROM   results o
 39  		    WHERE  e.encounter_id = o.encounter_id (+))
 40  		   ||
 41  		   (SELECT '</p>' FROM DUAL))
 42  	       AS CLOB INDENT) "op"
 43  FROM   patient p, encount e, abc
 44  WHERE  p.pat_mrn = e.pat_mrn (+)
 45  AND    e.encounter_id = abc.encounter_id
 46  AND    p.pat_mrn = '63280'
 47  AND    e.encounter_id = 42
 48  /

op
--------------------------------------------------------------------------------
<p>
  <LastReviewUser>1</LastReviewUser>
  <Medications>
    <Medication>
      <ORDID>9721</ORDID>
      <SimpleMed>
        <ERXID>9721</ERXID>
        <RxNormCodes>
          <RxNorm>
            <CodeLevel>966253</CodeLevel>
            <TermType>MED_FORM_STRENGTH</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>10582</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>40144</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>161</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>5489</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
        </RxNormCodes>
      </SimpleMed>
      <Refills>2</Refills>
      <Sig>take 11</Sig>
    </Medication>
  </Medications>
  <Results>
    <Component>
      <id>TX</id>
    </Component>
    <Component>
      <id>EGFR</id>
    </Component>
    <Component>
      <id>LDL</id>
    </Component>
    <Component>
      <id>HDL</id>
    </Component>
  </Results>
</p>


1 row selected.

SCOTT@orcl12c> 
SCOTT@orcl12c> SELECT XMLSERIALIZE
  2  	      (DOCUMENT XMLTYPE
  3  		 ((SELECT '<p><Medications>' FROM DUAL)
  4  		  ||
  5  		  (SELECT XMLELEMENT ("LastReviewUser", abc.id)
  6  		    FROM   abc
  7  		    WHERE  e.encounter_id = abc.encounter_id (+))
  8  		  ||
  9  		  (SELECT    XMLAGG
 10  			       (XMLELEMENT
 11  				 ("Medication",
 12  				  XMLELEMENT ("ORDID", m.medid),
 13  				  XMLELEMENT
 14  				    ("SimpleMed",
 15  				     XMLELEMENT ("ERXID", m.medid),
 16  				     XMLELEMENT
 17  				       ("RxNormCodes",
 18  					XMLAGG
 19  					  (XMLELEMENT
 20  					    ("RxNorm",
 21  					     XMLELEMENT ("CodeLevel", r.codelevel),
 22  					     XMLELEMENT ("TermType", r.term))))),
 23  				  XMLELEMENT ("Refills", m.refills),
 24  				  XMLELEMENT ("Sig", m.sig)))
 25  		   FROM   medications m, rxnor r
 26  		   WHERE  e.encounter_id = m.encounter_id (+)
 27  		   AND	  m.medid = r.medid(+)
 28  		   GROUP  BY m.medid, m.refills, m.sig)
 29  		   || '</Medications>' ||
 30  		   (SELECT XMLELEMENT
 31  			     ("Results",
 32  			      XMLAGG
 33  				(XMLELEMENT
 34  				  ("Component",
 35  				   XMLELEMENT ("id", o.component_name))))
 36  		    FROM   results o
 37  		    WHERE  e.encounter_id = o.encounter_id (+))
 38  		   ||
 39  		   (SELECT '</p>' FROM DUAL))
 40  	       AS CLOB INDENT) "op"
 41  FROM   patient p, encount e, abc
 42  WHERE  p.pat_mrn = e.pat_mrn (+)
 43  AND    e.encounter_id = abc.encounter_id
 44  AND    p.pat_mrn = '63280'
 45  AND    e.encounter_id = 42
 46  /

op
--------------------------------------------------------------------------------
<p>
  <Medications>
    <LastReviewUser>1</LastReviewUser>
    <Medication>
      <ORDID>9721</ORDID>
      <SimpleMed>
        <ERXID>9721</ERXID>
        <RxNormCodes>
          <RxNorm>
            <CodeLevel>966253</CodeLevel>
            <TermType>MED_FORM_STRENGTH</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>10582</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>40144</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>161</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>5489</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
        </RxNormCodes>
      </SimpleMed>
      <Refills>2</Refills>
      <Sig>take 11</Sig>
    </Medication>
  </Medications>
  <Results>
    <Component>
      <id>TX</id>
    </Component>
    <Component>
      <id>EGFR</id>
    </Component>
    <Component>
      <id>LDL</id>
    </Component>
    <Component>
      <id>HDL</id>
    </Component>
  </Results>
</p>


1 row selected.

SCOTT@orcl12c> 
SCOTT@orcl12c> 
SCOTT@orcl12c> SELECT XMLSERIALIZE
  2  	      (DOCUMENT XMLTYPE
  3  		 ((SELECT '<p>' FROM DUAL)
  4  		  ||
  5  		  (SELECT XMLELEMENT
  6  			    ("Medications",
  7  			     XMLAGG
  8  			       (XMLELEMENT
  9  				 ("Medication",
 10  				  XMLELEMENT ("LastReviewUser", abc.id),
 11  				  XMLELEMENT ("ORDID", m.medid),
 12  				  XMLELEMENT
 13  				    ("SimpleMed",
 14  				     XMLELEMENT ("ERXID", m.medid),
 15  				     XMLELEMENT
 16  				       ("RxNormCodes",
 17  					XMLAGG
 18  					  (XMLELEMENT
 19  					    ("RxNorm",
 20  					     XMLELEMENT ("CodeLevel", r.codelevel),
 21  					     XMLELEMENT ("TermType", r.term))))),
 22  				  XMLELEMENT ("Refills", m.refills),
 23  				  XMLELEMENT ("Sig", m.sig))))
 24  		   FROM   medications m, rxnor r, abc
 25  		   WHERE  e.encounter_id = m.encounter_id (+)
 26  		   AND	  e.encounter_id = abc.encounter_id (+)
 27  		   AND	  m.medid = r.medid(+)
 28  		   GROUP  BY m.medid, m.refills, m.sig, abc.id)
 29  		   ||
 30  		   (SELECT XMLELEMENT
 31  			     ("Results",
 32  			      XMLAGG
 33  				(XMLELEMENT
 34  				  ("Component",
 35  				   XMLELEMENT ("id", o.component_name))))
 36  		    FROM   results o
 37  		    WHERE  e.encounter_id = o.encounter_id (+))
 38  		   ||
 39  		   (SELECT '</p>' FROM DUAL))
 40  	       AS CLOB INDENT) "op"
 41  FROM   patient p, encount e
 42  WHERE  p.pat_mrn = e.pat_mrn (+)
 43  AND    p.pat_mrn = '63280'
 44  AND    e.encounter_id = 42
 45  /

op
--------------------------------------------------------------------------------
<p>
  <Medications>
    <Medication>
      <LastReviewUser>1</LastReviewUser>
      <ORDID>9721</ORDID>
      <SimpleMed>
        <ERXID>9721</ERXID>
        <RxNormCodes>
          <RxNorm>
            <CodeLevel>966253</CodeLevel>
            <TermType>MED_FORM_STRENGTH</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>10582</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>40144</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
        </RxNormCodes>
      </SimpleMed>
      <Refills>25</Refills>
      <Sig>take 2</Sig>
    </Medication>
    <Medication>
      <LastReviewUser>1</LastReviewUser>
      <ORDID>12886</ORDID>
      <SimpleMed>
        <ERXID>12886</ERXID>
        <RxNormCodes>
          <RxNorm>
            <CodeLevel>142439</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>161</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>5489</CodeLevel>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
        </RxNormCodes>
      </SimpleMed>
      <Refills>2</Refills>
      <Sig>take 11</Sig>
    </Medication>
  </Medications>
  <Results>
    <Component>
      <id>TX</id>
    </Component>
    <Component>
      <id>EGFR</id>
    </Component>
    <Component>
      <id>LDL</id>
    </Component>
    <Component>
      <id>HDL</id>
    </Component>
  </Results>
</p>


1 row selected.


Re: XML issue [message #596271 is a reply to message #596270] Thu, 19 September 2013 14:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7994
Registered: November 2002
Location: California, USA
Senior Member
It looks like you figured it out while I was posting. I corrected a couple of mis-matched column names for the abc table, then put it all together below.

SCOTT@orcl12c> -- test data:
SCOTT@orcl12c> COLUMN pat_mrn	     FORMAT A7
SCOTT@orcl12c> COLUMN encounter_id   FORMAT A12
SCOTT@orcl12c> COLUMN medid	     FORMAT A5
SCOTT@orcl12c> COLUMN codelevel      FORMAT A9
SCOTT@orcl12c> COLUMN term	     FORMAT A20
SCOTT@orcl12c> COLUMN sig	     FORMAT A7
SCOTT@orcl12c> COLUMN component_name FORMAT A14
SCOTT@orcl12c> SELECT * FROM patient
  2  /

PAT_MRN
-------
63280

1 row selected.

SCOTT@orcl12c> SELECT * FROM encount
  2  /

PAT_MRN ENCOUNTER_ID
------- ------------
63280   42

1 row selected.

SCOTT@orcl12c> SELECT * FROM medications
  2  /

MEDID ENCOUNTER_ID    REFILLS SIG
----- ------------ ---------- -------
9721  42                   25 take 2
12886 42                    2 take 11

2 rows selected.

SCOTT@orcl12c> SELECT * FROM rxnor
  2  /

MEDID CODELEVEL TERM
----- --------- --------------------
9721  966253    MED_FORM_STRENGTH
9721  40144     MED_ONLY
9721  10582     MED_ONLY
12886 142439    MED_ONLY
12886 5489      MED_ONLY
12886 161       MED_ONLY

6 rows selected.

SCOTT@orcl12c> SELECT * FROM results
  2  /

PAT_MRN ENCOUNTER_ID MEDID COMPONENT_NAME
------- ------------ ----- --------------
63280   42           12886 TX
63280   42           12886 EGFR
63280   42           12886 LDL
63280   42           12886 HDL

4 rows selected.

SCOTT@orcl12c> SELECT * FROM abc
  2  /

ENCOUNTER_ID         ID
------------ ----------
42                    1

1 row selected.

SCOTT@orcl12c> -- queries:
SCOTT@orcl12c> SELECT XMLSERIALIZE
  2  	      (DOCUMENT XMLTYPE
  3  		 ((SELECT '<p>' FROM DUAL)
  4  		  ||
  5  		  (SELECT XMLELEMENT("Medications",
  6  			       XMLELEMENT("UserID", abc.id),
  7  			       (SELECT XMLAGG(XMLELEMENT("Medication",
  8  							 XMLELEMENT("ORDID", m.medid),
  9  							 XMLELEMENT("SimpleMed",
 10  								    XMLELEMENT("ERXID",
 11  									       m.medid),
 12  								    XMLELEMENT("RxNormCodes",
 13  									       XMLAGG(XMLELEMENT("RxNorm",
 14  												 XMLELEMENT("CodeLevel",
 15  													    r.codelevel),
 16  												 XMLELEMENT("Code",
 17  													    NULL),
 18  												 XMLELEMENT("TermType",
 19  													    r.term))))),
 20  							 XMLELEMENT("Refills",
 21  								    m.refills),
 22  							 XMLELEMENT("Sig", m.sig)))
 23  				  FROM medications m, rxnor r
 24  				 WHERE e.encounter_id = m.encounter_id(+)
 25  				   AND m.medid = r.medid(+)
 26  				 GROUP BY m.MedId, m.refills, m.sig))
 27  	       FROM abc
 28  	      WHERE abc.ENCOUNTER_ID(+) = e.encounter_id
 29  	      GROUP BY abc.id)
 30  		   ||
 31  		   (SELECT XMLELEMENT
 32  			     ("Results",
 33  			      XMLAGG
 34  				(XMLELEMENT
 35  				  ("Component",
 36  				   XMLELEMENT ("id", o.component_name))))
 37  		    FROM   results o
 38  		    WHERE  e.encounter_id = o.encounter_id (+))
 39  		   ||
 40  		   (SELECT '</p>' FROM DUAL))
 41  	       AS CLOB INDENT) "op"
 42  FROM   patient p, encount e
 43  WHERE  p.pat_mrn = e.pat_mrn (+)
 44  AND    p.pat_mrn = '63280'
 45  AND    e.encounter_id = 42
 46  /

op
--------------------------------------------------------------------------------
<p>
  <Medications>
    <UserID>1</UserID>
    <Medication>
      <ORDID>9721</ORDID>
      <SimpleMed>
        <ERXID>9721</ERXID>
        <RxNormCodes>
          <RxNorm>
            <CodeLevel>966253</CodeLevel>
            <Code/>
            <TermType>MED_FORM_STRENGTH</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>10582</CodeLevel>
            <Code/>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>40144</CodeLevel>
            <Code/>
            <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>
            <Code/>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>161</CodeLevel>
            <Code/>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
          <RxNorm>
            <CodeLevel>5489</CodeLevel>
            <Code/>
            <TermType>MED_ONLY</TermType>
          </RxNorm>
        </RxNormCodes>
      </SimpleMed>
      <Refills>2</Refills>
      <Sig>take 11</Sig>
    </Medication>
  </Medications>
  <Results>
    <Component>
      <id>TX</id>
    </Component>
    <Component>
      <id>EGFR</id>
    </Component>
    <Component>
      <id>LDL</id>
    </Component>
    <Component>
      <id>HDL</id>
    </Component>
  </Results>
</p>


1 row selected.

Re: XML issue [message #596275 is a reply to message #596271] Thu, 19 September 2013 14:25 Go to previous messageGo to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Thanks...Sorry 1 more question.
Empty tags don't show up when we don't have meds for an encounter (say encounter 43 inserted as below)...



Insert into abc values ('43',1);
SELECT XMLSERIALIZE
  	      (DOCUMENT XMLTYPE
  		 ((SELECT '<p>' FROM DUAL)
  		  ||
  		  (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.id)
  		   ||
  		   (SELECT XMLELEMENT
  			     ("Results",
  			      XMLAGG
  				(XMLELEMENT
  				  ("Component",
  				   XMLELEMENT ("id", o.component_name))))
  		    FROM   results o
  		    WHERE  e.encounter_id = o.encounter_id (+))
  		   ||
  		   (SELECT '</p>' FROM DUAL))
  	       AS CLOB INDENT) "op"
  FROM   patient p, encount e
  WHERE  p.pat_mrn = e.pat_mrn (+)
  AND    p.pat_mrn = '63280'



O/p showing for encounter 43 :
<p>
       <Medications/> -- This one is Missing...
  <Results/>
</p>

[Updated on: Thu, 19 September 2013 14:32]

Report message to a moderator

Re: XML issue [message #596278 is a reply to message #596275] Thu, 19 September 2013 15:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7994
Registered: November 2002
Location: California, USA
Senior Member
Remember that you have declared patient as your top level and outer joined that to encont, then outer joined that to abc. So, if the following record does not exist,

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

then you get nothing. If you add that record, then you get:

<p>
  <Medications>
    <UserID>1</UserID>
    <Medication>
    ...
    </Medication>
    <Medication>
    ...
    </Medication>
  </Medications>
  <Results>
  ...
  </Results>
</p>

<p>
  <Medications>
    <UserID>1</UserID>
  </Medications>
  <Results/>
</p>

Re: XML issue [message #596280 is a reply to message #596278] Thu, 19 September 2013 15:27 Go to previous messageGo to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Sorry, didn't get it.
I have patient entry & encounter entry & no medication data for patient.
Since it's an outer join with patient, encounter, abc so irrespective of whether they have a medication or not,
they should get a null tag for medications for encounter 43.?? Am I mis-understanding something here?

But for results, the patient's encounter 43 doesn;t have results..But still, we are getting an empty tag...
SO, we should get empty tag for medications right...

[Updated on: Thu, 19 September 2013 15:29]

Report message to a moderator

Re: XML issue [message #596281 is a reply to message #596280] Thu, 19 September 2013 15:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7994
Registered: November 2002
Location: California, USA
Senior Member
You have elected to put the abc.id within Medications, so you get:

<p>
  <Medications>
    <UserID>1</UserID>
  </Medications>
  <Results/>
</p>


Maybe you need to rethink the XML structure that you are generating. Perhaps your abc.id belongs somewhere else:

<p>
  <UserID>1</UserID>
  <Medications/>
  <Results/>
</p>



[Updated on: Thu, 19 September 2013 15:34]

Report message to a moderator

Re: XML issue [message #596282 is a reply to message #596281] Thu, 19 September 2013 15:39 Go to previous messageGo to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
ok.

[Updated on: Fri, 20 September 2013 10:22]

Report message to a moderator

Re: XML issue [message #605262 is a reply to message #596282] Wed, 08 January 2014 05:16 Go to previous messageGo to next message
KimberlyDeborah
Messages: 15
Registered: December 2013
Location: United States
Junior Member
Thanks for this valuable information.I am beginner and i am getting very good tips from here.
Re: XML issue [message #605427 is a reply to message #596278] Thu, 09 January 2014 04:47 Go to previous messageGo to next message
KimberlyDeborah
Messages: 15
Registered: December 2013
Location: United States
Junior Member
Can we create JSON from this????
Re: XML issue [message #605429 is a reply to message #605427] Thu, 09 January 2014 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes.

Re: XML issue [message #605522 is a reply to message #605429] Fri, 10 January 2014 04:31 Go to previous messageGo to next message
KimberlyDeborah
Messages: 15
Registered: December 2013
Location: United States
Junior Member
Can we use that JSON as an web services for dot net and java formation.???
Re: XML issue [message #605565 is a reply to message #605522] Fri, 10 January 2014 10:08 Go to previous message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How is this an Oracle question?

Previous Topic: oracle dbms_xmlquery.getxml error bind name identifier does not exist
Next Topic: BFILE CLOSURE WAIT EVENT
Goto Forum:
  


Current Time: Mon Oct 20 05:27:14 CDT 2014

Total time taken to generate the page: 0.13990 seconds