table results to xml in oracle

Question

Experts, I want to send some results via email in the form of table, for which i need to convert the results into xml format. I want to store the results in a variable and attach to the xml body. The xml result gives me in the form of rows and i am unable to capture the xml results in a single row and convert that into varchar2 so that i can attach the variable. Need help... I am using Oracle Sql Developer 11g.

Below is the script i am using

select (XMLElement("tr"
,XMLFOREST(d.tablespace_name as "td", 
 d.file_name as "td",   d.bytes/1024/1024 as "td", 
 h.CREATION_TIME as "td",
 d.blocks as "td")))
from dba_data_files d
inner join v$datafile_header h
on d.TABLESPACE_NAME = h.TABLESPACE_NAME
order by d.TABLESPACE_NAME;

Does what you want is just to aggregate all the row results to one big XML with one root element, instead of multiple rows with one element in each? — evenro
– evenro, Commented Apr 7, 2014 at 16:50

evenro · Accepted Answer · 2014-04-07 16:58:33Z

1

If I understood the question correctly - please have a look at XMLAGG: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions215.htm

This function allows creating one xml from multiple records.

answered Apr 7, 2014 at 16:58

evenro

2,66422 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3311382 Over a year ago

Evenro, Thanks for the link. I even tried using xmlagg but when i execute, the result just shows XMLTYPE nothing else. I am executing in sql developer. I want the result like <tr><td>SYSTEM</td><td>C:\APP****\ORADATA\ORCL\SYSTEM01.DBF</td><td>740</td><td>2010-04-02</td><td>94720</td> in text datatype so that i can save it in a variable and attach the variable.

evenro Over a year ago

double click the "XMLTYPE" to see the content in this interface... the "XMLTYPE" is a marker that there is a lob in the background that hold your content... :)

evenro Over a year ago

(Remember that XMLType is not a string, therefore - viewing it would require a different viewer)

Collectives™ on Stack Overflow

table results to xml in oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related