Using XML in Oracle

Oracle has a extremly nice built in XML support, in 10gR2 you even have native xquery support. 9iR2 and later versions still have excellent XML support in the shape of XML DB.

First step in getting to know the XML functions in Oracle is to extract data to XML format, probably a quite common task and from what I've seen this is usually done using some highly customized java app that needs to be rewritten, recompiled, tested etc. every time there is a change. So why not use the functions available and let Oracle do the job.

The most basic XML functions to use are.

xmlelement() - simply creates an xmlelement. An element can have child elements and attributes.

xmlattributes() - adds an attribute to an element.

xmlagg() - aggregates a number of elements under another element, sort of like group by in a normal query.

Example query to generate employee records from scott.emp.

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 
/

And the result will look like this (and no, the basic XML function are not formatting aware and will not indent the XML structure).

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

Check here for the offical (10gR2) XML docs.

Stay tuned, more XML to come.