Home » Developer & Programmer » JDeveloper, Java & XML » XML issue (Oracle 11g, XP)
| XML issue [message #596076] |
Tue, 17 September 2013 16:39  |
rajivn786
Messages: 161
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 #596103 is a reply to message #596095] |
Wed, 18 September 2013 01:33  |
 |
Barbara Boehmer
Messages: 9104
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 |
rajivn786
Messages: 161
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 |
rajivn786
Messages: 161
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 #596182 is a reply to message #596181] |
Wed, 18 September 2013 12:17 |
rajivn786
Messages: 161
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 #596189 is a reply to message #596187] |
Wed, 18 September 2013 12:45 |
rajivn786
Messages: 161
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 |
|
Barbara Boehmer
Messages: 9104
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 |
|
Barbara Boehmer
Messages: 9104
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 |
rajivn786
Messages: 161
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 |
rajivn786
Messages: 161
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 |
|
Barbara Boehmer
Messages: 9104
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 |
|
Barbara Boehmer
Messages: 9104
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 |
rajivn786
Messages: 161
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 #596280 is a reply to message #596278] |
Thu, 19 September 2013 15:27 |
rajivn786
Messages: 161
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 |
|
Barbara Boehmer
Messages: 9104
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 15:23:57 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
