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
xmlattributes(empno as "id"),
xmlelement("job", job),
xmlelement("hiredate", to_char(hiredate, 'YYYY/MM/DD')),
xmlelement("deptno", deptno)

-- 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;

<employee id="7369">
<employee id="7499">
<employee id="7521">
<employee id="7566">

Querying XML data is next. :-)


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.

Problem solved!