Saturday, May 20

Oracle XML output (native) formatting

Got a few question on how to actually get properly formated XML straight from Oracle.
I'm not aware of any suitable DBMS function if there are any.
One way I to simply insert and select the query result in an Oracle xmltype data column in a temporary table. A temporary table is per session and is per default truncated when you commit.

set pages 0 long 900000 lines 100
-- create a temporary table with one xmltype column
create global temporary table xmlformat (data xmltype);

-- do the select but with an prefixed insert
insert into xmlformat select
xmlelement("emp",
xmlagg(xmlelement("employee",
xmlattributes(empno as "id"),
xmlelement("name",ename),
xmlelement("job", job),
xmlelement("hiredate", to_char(hiredate, 'YYYY/MM/DD')),
xmlelement("deptno", deptno)
)
)
)
from
emp
where
empno<=7566
/

-- Ok, what do we got here, the output here is exactly
-- as displayed by Oracle. No cheating. Do remember that this is
-- a temporary table so the data is just for your current session
-- and it will be lost when you commit (or end your session)


select data from xmlformat;

<emp>
<employee id="7369">
<name>SMITH</name>
<job>CLERK</job>
<hiredate>1980/12/17</hiredate>
<deptno>20</deptno>
</employee>
<employee id="7499">
<name>ALLEN</name>
<job>SALESMAN</job>
<hiredate>1981/02/20</hiredate>
<deptno>30</deptno>
</employee>
<employee id="7521">
<name>WARD</name>
<job>SALESMAN</job>
<hiredate>1981/02/22</hiredate>
<deptno>30</deptno>
</employee>
<employee id="7566">
<name>JONES</name>
<job>MANAGER</job>
<hiredate>1981/04/02</hiredate>
<deptno>20</deptno>
</employee>
</emp>


Querying XML data is next. :-)

2 comments:

Taylor Steil said...

When I run this on Oracle 11g via sqlplus on Linux I just get 1 huge block of XML- no linebreaks.

Do you know what is happening to my linebreaks?

Taylor Steil said...

When I added the xmlroot() function, the linebreaks were also added.

http://www.oratechinfo.co.uk/sqlxml.html#xmlroot

Problem solved!