Home » SQL & PL/SQL » SQL & PL/SQL » convert xml column value into columns (Oracle 19c)
convert xml column value into columns [message #687494] Mon, 20 March 2023 21:53 Go to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
hi i have a question. when checking execution plan notes from v$sql_plan.other_xml column i want to convert each value into one column forexample if i run below query
SELECT
'sql_profile ' || extractvalue(xmlval, '/*/info[@type = "sql_profile"]')||'
sql_patch ' || extractvalue(xmlval, '/*/info[@type = "sql_patch"]')||'
baseline ' || extractvalue(xmlval, '/*/info[@type = "baseline"]')||'
outline ' || extractvalue(xmlval, '/*/info[@type = "outline"]')||'
dyn_sampling ' || extractvalue(xmlval, '/*/info[@type = "dynamic_sampling"]')||'
dop ' || extractvalue(xmlval, '/*/info[@type = "dop"]')||'
dop_reason ' || extractvalue(xmlval, '/*/info[@type = "dop_reason"]')||'
pdml_reason ' || extractvalue(xmlval, '/*/info[@type = "pdml_reason"]')||'
idl_reason ' || extractvalue(xmlval, '/*/info[@type = "idl_reason"]')||'
queuing_reason ' || extractvalue(xmlval, '/*/info[@type = "queuing_reason"]')||'
px_in_memory ' || extractvalue(xmlval, '/*/info[@type = "px_in_memory"]')||'
px_in_memory_imc ' || extractvalue(xmlval, '/*/info[@type = "px_in_memory_imc"]')||'
row_shipping ' || extractvalue(xmlval, '/*/info[@type = "row_shipping"]')||'
index_size ' || extractvalue(xmlval, '/*/info[@type = "index_size"]')||'
result_checksum ' || extractvalue(xmlval, '/*/info[@type = "result_checksum"]')||'
card_feedback ' || extractvalue(xmlval, '/*/info[@type = "cardinality_feedback"]')||'
perf_feedback ' || extractvalue(xmlval, '/*/info[@type = "performance_feedback"]')||'
xml_suboptimal ' || extractvalue(xmlval, '/*/info[@type = "xml_suboptimal"]')||'
adaptive_plan ' || extractvalue(xmlval, '/*/info[@type = "adaptive_plan"]')||'
spd_used ' || extractvalue(xmlval, '/*/spd/cu')||'
spd_valid ' || extractvalue(xmlval, '/*/spd/cv')||'
gtt_sess_stat ' || extractvalue(xmlval, '/*/info[@type = "gtt_session_st"]')||'
db_version ' || extractvalue(xmlval, '/*/info[@type = "db_version"]')||'
plan_hash_full ' || extractvalue(xmlval, '/*/info[@type = "plan_hash_full"]')||'
plan_hash ' || extractvalue(xmlval, '/*/info[@type = "plan_hash"]')||'
plan_hash_2 ' || extractvalue(xmlval, '/*/info[@type = "plan_hash_2"]') as Full_Notes
from
(select xmltype(other_xml) xmlval from
(select other_xml
from dba_hist_sql_plan
where sql_id = '&&sql_id'
-- and plan_hash_value = nvl('&&plan_hash',0)
and other_xml is not null
and not exists (select 1 from gv$sql_plan where
sql_id = '&&sql_id'
-- and plan_hash_value = nvl('&&plan_hash',0)
and other_xml is not null)
union all
select other_xml
from gv$sql_plan
where sql_id = '&&sql_id'
-- and plan_hash_value = nvl('&&plan_hash',0)
and other_xml is not null
and (inst_id, child_number) in (select inst_id, child_number from gv$sql_plan where sql_id = '&&sql_id'
--and plan_hash_value = nvl('&&plan_hash', 0)
and rownum <= 1)
)
)
/



it generates me below output in one row (multiple lines).

sql_profile SYS_SQLPROF_01687c32214f0026
sql_patch
baseline
outline
dyn_sampling
dop
dop_reason
pdml_reason
idl_reason
queuing_reason
px_in_memory
px_in_memory_imc
row_shipping
index_size
result_checksum
card_feedback
perf_feedback
xml_suboptimal
adaptive_plan
spd_used
spd_valid
gtt_sess_stat
db_version 19.0.0.0
plan_hash_full 1342132429
plan_hash 1184223408
plan_hash_2 1342132429

basically i need to convert each line into column some kind of below not putting all the column_names here thanks...

plan_hash_full plan_hash plan_hash_2 db_version sql_profile sql_patch
1342132429 1184223408 1342132429 19.0.0.0 SYS_SQLPROF_01687c32214f0026


Re: convert xml column value into columns [message #687495 is a reply to message #687494] Tue, 21 March 2023 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68600
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Tue, 28 February 2023 07:22

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
...

Michel Cadot wrote on Tue, 28 February 2023 17:52

Please read How to use [code] tags and make your code easier to read.
...

Michel Cadot wrote on Wed, 01 March 2023 07:09

1/ FORMAT your post
...

Re: convert xml column value into columns [message #687496 is a reply to message #687494] Tue, 21 March 2023 03:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You are getting everything all in one column because you are concatenating line feeds. If you want separate columns, then you need to select them as columns followed by any aliases and separated by commas. You also need to use some method such as column definitions or to_char and set your linesize so that the columns are not so wide and the linesize so narrow that the columns wrap around. A better newer method is to use xmltable. I have demonstrated both below.

get sql_id and plan_hash for testing:
SCOTT@orcl_12.1.0.2.0> column sql_id new_value sql_id
SCOTT@orcl_12.1.0.2.0> column plan_hash new_value plan_hash
SCOTT@orcl_12.1.0.2.0> select sql_id, '1' plan_hash
  2  from   dba_hist_sql_plan
  3  where  sql_id is not null
  4  and    rownum = 1
  5  /

SQL_ID        PLAN_HASH
------------- ---------
0gx3b09qrx9f5 1

1 row selected.
query method 1:
SCOTT@orcl_12.1.0.2.0> COLUMN plan_hash_full  FORMAT A14
SCOTT@orcl_12.1.0.2.0> COLUMN plan_hash       FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN plan_hash_2     FORMAT A11
SCOTT@orcl_12.1.0.2.0> COLUMN db_version      FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN sql_profile     FORMAT A11
SCOTT@orcl_12.1.0.2.0> COLUMN sql_patch       FORMAT A10
SCOTT@orcl_12.1.0.2.0> SELECT extractvalue(xmlval, '/*/info[@type = "plan_hash_full"]') plan_hash_full,
  2  	    extractvalue(xmlval, '/*/info[@type = "plan_hash"]')      plan_hash,
  3  	    extractvalue(xmlval, '/*/info[@type = "plan_hash_2"]')    plan_hash_2,
  4  	    extractvalue(xmlval, '/*/info[@type = "db_version"]')     db_version,
  5  	    extractvalue(xmlval, '/*/info[@type = "sql_profile"]')    sql_profile,
  6  	    extractvalue(xmlval, '/*/info[@type = "sql_patch"]')      sql_patch
  7  from   (select xmltype (other_xml) xmlval from
  8  	    (select other_xml
  9  	     from   dba_hist_sql_plan
 10  	     where  sql_id = '&&sql_id'
 11  	  -- and    plan_hash_value = nvl('&&plan_hash',0)
 12  	     and    other_xml is not null
 13  	     and    not exists
 14  		      (select 1
 15  		       from   gv$sql_plan
 16  		       where  sql_id = '&&sql_id'
 17  		    -- and    plan_hash_value = nvl('&&plan_hash',0)
 18  		       and    other_xml is not null)
 19  	      union all
 20  	      select other_xml
 21  	      from   gv$sql_plan
 22  	      where  sql_id = '&&sql_id'
 23  	   -- and    plan_hash_value = nvl('&&plan_hash',0)
 24  	      and    other_xml is not null
 25  	      and    (inst_id, child_number) in
 26  		     (select inst_id, child_number
 27  		      from   gv$sql_plan
 28  		      where  sql_id = '&&sql_id'
 29  		    --and    plan_hash_value = nvl('&&plan_hash', 0)
 30  		     )))
 31  /

PLAN_HASH_FULL PLAN_HASH  PLAN_HASH_2 DB_VERSION SQL_PROFILE SQL_PATCH
-------------- ---------- ----------- ---------- ----------- ----------
1254914732     1964104430 1254914732  12.1.0.2
1254914732     1964104430 1254914732  12.1.0.2

2 rows selected.
query method 2:
SCOTT@orcl_12.1.0.2.0> select x.*
  2  from   (select other_xml
  3  	     from   dba_hist_sql_plan
  4  	     where  sql_id = '&&sql_id'
  5  	  -- and    plan_hash_value = nvl('&&plan_hash',0)
  6  	     and    other_xml is not null
  7  	     and    not exists
  8  		      (select 1
  9  		       from   gv$sql_plan
 10  		       where  sql_id = '&&sql_id'
 11  		    -- and    plan_hash_value = nvl('&&plan_hash',0)
 12  		       and    other_xml is not null)
 13  	      union all
 14  	      select other_xml
 15  	      from   gv$sql_plan
 16  	      where  sql_id = '&&sql_id'
 17  	   -- and    plan_hash_value = nvl('&&plan_hash',0)
 18  	      and    other_xml is not null
 19  	      and    (inst_id, child_number) in
 20  		     (select inst_id, child_number
 21  		      from   gv$sql_plan
 22  		      where  sql_id = '&&sql_id'
 23  		    --and    plan_hash_value = nvl('&&plan_hash', 0)
 24  		     )) t,
 25  	    xmltable
 26  	      ('/other_xml'
 27  	       passing xmltype (t.other_xml)
 28  	       columns
 29  		 plan_hash_full  varchar2(14)  path '/*/info[@type = "plan_hash_full"]',
 30  		 plan_hash	 varchar2( 9)  path '/*/info[@type = "plan_hash"]',
 31  		 plan_hash_2	 varchar2(14)  path '/*/info[@type = "plan_hash_2"]',
 32  		 db_version	 varchar2(10)  path '/*/info[@type = "db_version"]',
 33  		 sql_profile	 varchar2(11)  path '/*/info[@type = "sql_profile"]',
 34  		 sql_patch	 varchar2(10)  path '/*/info[@type = "plan_hash_2"]') x
 35  /

PLAN_HASH_FULL PLAN_HASH  PLAN_HASH_2 DB_VERSION SQL_PROFILE SQL_PATCH
-------------- ---------- ----------- ---------- ----------- ----------
1254914732     196410443  1254914732  12.1.0.2               1254914732
1254914732     196410443  1254914732  12.1.0.2               1254914732

2 rows selected.
Re: convert xml column value into columns [message #687500 is a reply to message #687496] Tue, 21 March 2023 09:20 Go to previous message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
Thank you so much it is really helpfull
Previous Topic: Refreshing Materialized View
Next Topic: xml converting task
Goto Forum:
  


Current Time: Sun Mar 03 10:46:17 CST 2024