Saturday, May 13

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.

    1 comment:

    Emma said...

    Nice post. Yes I do agree that Oracle has come up with so many new features that makes it the most popular databases of all. I have not used these functions so far, Thanks for sharing all the basic functions and quoting an example to show the usage.
    sap project systems