Home » Developer & Programmer » JDeveloper, Java & XML » XML Decode Statement (Oracle 11g, XP)
XML Decode Statement [message #596691] Wed, 25 September 2013 12:35 Go to next message
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

                    
icon5.gif  Re: XML Decode Statement [message #596708 is a reply to message #596691] Wed, 25 September 2013 14:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why don't you just use the good old || to build the XML string you want?

Re: XML Decode Statement [message #596709 is a reply to message #596691] Wed, 25 September 2013 14:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
Frank Naude
Messages: 4579
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))
icon3.gif  Re: XML Decode Statement [message #596713 is a reply to message #596708] Wed, 25 September 2013 15:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select xmltype('<SmokingHistory>
  2  <TobaccoTypes>
  3  '||decode(CIGARS_YN, 'Y', '<Type>5</Type>
  4  ')||decode(SNUFF_YN, 'Y', '<Type>4</Type>
  5  ')||'</TobaccoTypes>
  6  </SmokingHistory>') res
  7   FROM patient p, encount e, HIST CS
  8   WHERE p.pat_mrn = e.pat_mrn
  9     and CS.ENCOUNTER_ID = E.ENCOUNTER_ID
 10  /
RES
---------------------------------------------------
<SmokingHistory>
<TobaccoTypes>
<Type>5</Type>
</TobaccoTypes>
</SmokingHistory>

Re: XML Decode Statement [message #596714 is a reply to message #596710] Wed, 25 September 2013 15:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Frank Naude wrote on Wed, 25 September 2013 12:38
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))


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 #596715 is a reply to message #596710] Wed, 25 September 2013 15:22 Go to previous messageGo to next message
rajivn786
Messages: 161
Registered: January 2010
Senior Member
Thanks..It works..
icon4.gif  Re: XML Decode Statement [message #596737 is a reply to message #596715] Thu, 26 September 2013 01:59 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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.
Previous Topic: Return a ResultSet as a ref cursor
Next Topic: XMLAGG for clob fields
Goto Forum:
  


Current Time: Thu Mar 28 07:37:09 CDT 2024