Home » Developer & Programmer » JDeveloper, Java & XML » XML Decode Statement (Oracle 11g, XP)
| XML Decode Statement [message #596691] |
Wed, 25 September 2013 12:35  |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
Hi,
Firstly these are the table structures
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 HIST (encounter_id varchar2(1000), CIGARS_YN VARCHAR2(10), SNUFF_YN VARCHAR2(10));
Insert into hist values ('42','Y','N');
The main part :
If CIGARS_YN = "Y"
then o/p should be
<SmokingHistory>
<TobaccoTypes>
<Type>5</Type>
</TobaccoTypes>
</SmokingHistory>
If SNUFF_YN = "Y"
then o/p should be
<SmokingHistory>
<TobaccoTypes>
<Type>4</Type>
</TobaccoTypes>
</SmokingHistory>
If CIGARS_YN = "Y" and SNUFF_YN = "Y"
then o/p should be
<SmokingHistory>
<TobaccoTypes>
<Type>4</Type>
<Type>5</Type>
</TobaccoTypes>
</SmokingHistory>
If CIGARS_YN = "Y" and SNUFF_YN = "N"
then o/p should be
<SmokingHistory>
<TobaccoTypes>
<Type>5</Type>
</TobaccoTypes>
</SmokingHistory>
but the o/p I am getting is :
<SmokingHistory>
<TobaccoTypes>
<Type>5</Type>
<Type></Type> -- NULL TAG (NEED TO ELIMINATE THIS...DISPLAY ONLY WHEN "Y")
</TobaccoTypes>
</SmokingHistory>
This is the code which I wrote:
Select XMLELEMENT("SmokingHistory",
XMLAGG(XMLELEMENT("TobaccoTypes",
XMLELEMENT("Type",
Decode(CS.CIGARS_YN, 'Y', 5)),
XMLELEMENT("Type",
Decode(CS.SNUFF_YN, 'Y', 4)))))
FROM patient p, encount e, HIST CS
WHERE p.pat_mrn = e.pat_mrn
and CS.ENCOUNTER_ID = E.ENCOUNTER_ID
|
|
|
|
|
|
| Re: XML Decode Statement [message #596709 is a reply to message #596691] |
Wed, 25 September 2013 14:35  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
One method would be to use REPLACE.
SCOTT@orcl12c> COLUMN pat_mrn FORMAT A7
SCOTT@orcl12c> SELECT * FROM patient
2 /
PAT_MRN
-------
63280
1 row selected.
SCOTT@orcl12c> COLUMN encounter_id FORMAT A12
SCOTT@orcl12c> SELECT * FROM encount
2 /
PAT_MRN ENCOUNTER_ID
------- ------------
63280 42
1 row selected.
SCOTT@orcl12c> SELECT * FROM hist
2 /
ENCOUNTER_ID CIGARS_YN SNUFF_YN
------------ ---------- ----------
42 Y N
1 row selected.
SCOTT@orcl12c> Select XMLSERIALIZE
2 (DOCUMENT XMLTYPE
3 (REPLACE
4 (XMLELEMENT
5 ("SmokingHistory",
6 XMLAGG
7 (XMLELEMENT
8 ("TobaccoTypes",
9 XMLELEMENT
10 ("Type",
11 Decode (CS.CIGARS_YN, 'Y', 5)),
12 XMLELEMENT
13 ("Type",
14 Decode (CS.SNUFF_YN, 'Y', 4))))),
15 '<Type></Type>',
16 '')) INDENT)
17 FROM patient p, encount e, HIST CS
18 WHERE p.pat_mrn = e.pat_mrn
19 and CS.ENCOUNTER_ID = E.ENCOUNTER_ID
20 /
XMLSERIALIZE(DOCUMENTXMLTYPE(REPLACE(XMLELEMENT("SMOKINGHISTORY",XMLAGG(XMLELEME
--------------------------------------------------------------------------------
<SmokingHistory>
<TobaccoTypes>
<Type>5</Type>
</TobaccoTypes>
</SmokingHistory>
1 row selected.
|
|
|
|
| Re: XML Decode Statement [message #596710 is a reply to message #596708] |
Wed, 25 September 2013 14:38 |
Frank Naude
Messages: 4589
Registered: April 1998
|
Senior Member |
|
|
Quote:<Type></Type> -- NULL TAG (NEED TO ELIMINATE THIS...DISPLAY ONLY WHEN "Y")
Try to change:
XMLELEMENT("Type", Decode(CS.SNUFF_YN, 'Y', 4))
to
Decode(CS.SNUFF_YN, 'Y', XMLELEMENT("Type", 4))
|
|
|
|
|
|
| Re: XML Decode Statement [message #596714 is a reply to message #596710] |
Wed, 25 September 2013 15:14 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Frank Naude wrote on Wed, 25 September 2013 12:38Quote:<Type></Type> -- NULL TAG (NEED TO ELIMINATE THIS...DISPLAY ONLY WHEN "Y")
Try to change:
XMLELEMENT("Type", Decode(CS.SNUFF_YN, 'Y', 4))
to
Decode(CS.SNUFF_YN, 'Y', XMLELEMENT("Type", 4))
Yes, that works, as shown below. I erroneously posted that it did not, because I posted the wrong test results. I have deleted that post, so if you read it, then just ignore it.
SCOTT@orcl12c> Select XMLSERIALIZE
2 (DOCUMENT XMLELEMENT
3 ("SmokingHistory",
4 XMLAGG
5 (XMLELEMENT
6 ("TobaccoTypes",
7 DECODE (cs.cigars_yn, 'Y', XMLELEMENT ("Type", 5)),
8 DECODE (cs.snuff_yn, 'Y', XMLELEMENT ("Type", 4))))) INDENT)
9 FROM patient p, encount e, HIST CS
10 WHERE p.pat_mrn = e.pat_mrn
11 and CS.ENCOUNTER_ID = E.ENCOUNTER_ID
12 /
XMLSERIALIZE(DOCUMENTXMLELEMENT("SMOKINGHISTORY",XMLAGG(XMLELEMENT("TOBACCOTYPES
--------------------------------------------------------------------------------
<SmokingHistory>
<TobaccoTypes>
<Type>5</Type>
</TobaccoTypes>
</SmokingHistory>
1 row selected.
|
|
|
|
|
|
 Re: XML Decode Statement [message #596737 is a reply to message #596715] |
Thu, 26 September 2013 01:59 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
If performances is a concern for you, here's a script that allows to compare both statements (using T. Kyte's runStats_pkg package):
Drop table patient purge ;
Drop table encount purge;
Drop TABLE HIST purge;
Create table patient (pat_mrn varchar2(100) primary key) ;
Create table encount (pat_mrn varchar2(100), encounter_id varchar2(1000) primary key);
CREATE TABLE HIST (encounter_id varchar2(1000) primary key, CIGARS_YN VARCHAR2(10), SNUFF_YN VARCHAR2(10));
def N=100000
insert all
into patient values (val)
into encount values (val, val||val)
into hist values (val||val, YN1, YN2)
select to_char(level) val,
decode(mod(floor(dbms_random.value(0,2)),2), 0, 'N', 'Y') YN1,
decode(mod(floor(dbms_random.value(0,4)),4), 0, 'N', 1, 'N', 'Y') YN2
from dual
connect by level <= &N
/
delete hist CS where CS.CIGARS_YN = 'N' and CS.SNUFF_YN = 'N';
commit;
begin
dbms_stats.gather_table_stats (user, 'PATIENT', cascade=>true);
dbms_stats.gather_table_stats (user, 'ENCOUNT', cascade=>true);
dbms_stats.gather_table_stats (user, 'HIST', cascade=>true);
end;
/
select count(*)
FROM patient p, encount e, HIST CS
WHERE p.pat_mrn = e.pat_mrn
and CS.ENCOUNTER_ID = E.ENCOUNTER_ID
/
exec runStats_pkg.rs_start
declare
l_curs pls_integer := dbms_sql.open_cursor;
l_sta pls_integer;
l_val varchar2(4000);
l_sql varchar2(1000) :=
q'[select xmltype('<SmokingHistory>
<TobaccoTypes>
'||decode(CIGARS_YN, 'Y', ' <Type>5</Type>
')||decode(SNUFF_YN, 'Y', ' <Type>4</Type>
')||' </TobaccoTypes>
</SmokingHistory>') res
FROM patient p, encount e, HIST CS
WHERE p.pat_mrn = e.pat_mrn
and CS.ENCOUNTER_ID = E.ENCOUNTER_ID]';
begin
dbms_sql.parse (l_curs, l_sql, dbms_sql.native);
dbms_sql.define_column (l_curs, 1, l_val, 4000);
l_sta := dbms_sql.execute (l_curs);
l_sta := dbms_sql.fetch_rows (l_curs);
dbms_sql.close_cursor(l_curs);
end;
/
exec runStats_pkg.rs_middle;
declare
l_curs pls_integer := dbms_sql.open_cursor;
l_sta pls_integer;
l_val varchar2(4000);
l_sql varchar2(1000) :=
q'[Select XMLSERIALIZE
(DOCUMENT XMLELEMENT
("SmokingHistory",
XMLAGG
(XMLELEMENT
("TobaccoTypes",
DECODE (cs.cigars_yn, 'Y', XMLELEMENT ("Type", 5)),
DECODE (cs.snuff_yn, 'Y', XMLELEMENT ("Type", 4))))) INDENT)
FROM patient p, encount e, HIST CS
WHERE p.pat_mrn = e.pat_mrn
and CS.ENCOUNTER_ID = E.ENCOUNTER_ID]';
begin
dbms_sql.parse (l_curs, l_sql, dbms_sql.native);
dbms_sql.define_column (l_curs, 1, l_val, 4000);
l_sta := dbms_sql.execute (l_curs);
l_sta := dbms_sql.fetch_rows (l_curs);
dbms_sql.close_cursor(l_curs);
end;
/
exec runStats_pkg.rs_stop(1000);
The execution I made on my laptop in 11.2.0.1 gave:
SQL> select count(*)
2 FROM patient p, encount e, HIST CS
3 WHERE p.pat_mrn = e.pat_mrn
4 and CS.ENCOUNTER_ID = E.ENCOUNTER_ID
5 /
COUNT(*)
----------
74773
1 row selected.
SQL> exec runStats_pkg.rs_start
PL/SQL procedure successfully completed.
SQL> declare
2 l_curs pls_integer := dbms_sql.open_cursor;
3 l_sta pls_integer;
4 l_val varchar2(4000);
5 l_sql varchar2(1000) :=
6 q'[select xmltype('<SmokingHistory>
7 <TobaccoTypes>
8 '||decode(CIGARS_YN, 'Y', ' <Type>5</Type>
9 ')||decode(SNUFF_YN, 'Y', ' <Type>4</Type>
10 ')||' </TobaccoTypes>
11 </SmokingHistory>') res
12 FROM patient p, encount e, HIST CS
13 WHERE p.pat_mrn = e.pat_mrn
14 and CS.ENCOUNTER_ID = E.ENCOUNTER_ID]';
15 begin
16 dbms_sql.parse (l_curs, l_sql, dbms_sql.native);
17 dbms_sql.define_column (l_curs, 1, l_val, 4000);
18 l_sta := dbms_sql.execute (l_curs);
19 l_sta := dbms_sql.fetch_rows (l_curs);
20 dbms_sql.close_cursor(l_curs);
21 end;
22 /
PL/SQL procedure successfully completed.
SQL> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> declare
2 l_curs pls_integer := dbms_sql.open_cursor;
3 l_sta pls_integer;
4 l_val varchar2(4000);
5 l_sql varchar2(1000) :=
6 q'[Select XMLSERIALIZE
7 (DOCUMENT XMLELEMENT
8 ("SmokingHistory",
9 XMLAGG
10 (XMLELEMENT
11 ("TobaccoTypes",
12 DECODE (cs.cigars_yn, 'Y', XMLELEMENT ("Type", 5)),
13 DECODE (cs.snuff_yn, 'Y', XMLELEMENT ("Type", 4))))) INDENT)
14 FROM patient p, encount e, HIST CS
15 WHERE p.pat_mrn = e.pat_mrn
16 and CS.ENCOUNTER_ID = E.ENCOUNTER_ID]';
17 begin
18 dbms_sql.parse (l_curs, l_sql, dbms_sql.native);
19 dbms_sql.define_column (l_curs, 1, l_val, 4000);
20 l_sta := dbms_sql.execute (l_curs);
21 l_sta := dbms_sql.fetch_rows (l_curs);
22 dbms_sql.close_cursor(l_curs);
23 end;
24 /
PL/SQL procedure successfully completed.
SQL> exec runStats_pkg.rs_stop (1000);
Run1 ran in 15 hsecs
Run2 ran in 117 hsecs
Run1 ran in 12.82% of Run2 time
Name Run1 Run2 Diff
STAT..lob reads 0 1,141 1,141
STAT..free buffer requested 10 1,288 1,278
STAT..no work - consistent rea 309 1,617 1,308
LATCH.object queue header oper 13 2,565 2,552
STAT..buffer is pinned count 0 5,364 5,364
STAT..consistent gets - examin 5 6,333 6,328
STAT..lob writes 0 7,024 7,024
STAT..lob writes unaligned 0 7,024 7,024
LATCH.simulator hash latch 27 9,918 9,891
STAT..consistent gets from cac 328 10,546 10,218
STAT..calls to get snapshot sc 4 15,194 15,190
STAT..consistent gets 333 16,879 16,546
STAT..consistent gets from cac 333 16,879 16,546
STAT..db block changes 75 20,284 20,209
STAT..consistent changes 60 20,269 20,209
STAT..db block gets 58 74,668 74,610
STAT..db block gets from cache 58 74,668 74,610
STAT..index fetch by key 2 74,773 74,771
STAT..session logical reads 391 91,547 91,156
STAT..table scan rows gotten 100,382 200,000 99,618
LATCH.cache buffers chains 876 223,038 222,162
STAT..temp space allocated (by 0 11,534,336 11,534,336
STAT..session pga memory max 3,866,624 21,954,560 18,087,936
STAT..session uga memory max 3,346,332 21,941,444 18,595,112
STAT..session uga memory 222,420 25,065,356 24,842,936
STAT..session pga memory 458,752 25,362,432 24,903,680
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,209 236,122 234,913 0.51%
PL/SQL procedure successfully completed.
As you can see XML functions need much more space (UGA/PGA) and need much more latches.
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 15:20:52 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
