select d.dname, 
          xtab.actual, 
          xtab.estimate, 
          xtab.year, 
          xtab.actual - xtab.estimate overage
from WWV_DEMO_DEPT d, XMLTable ('/Budget/History/Budget' passing BUDGET
 COLUMNS
  YEAR VARCHAR2(4) PATH 'Year',
  ESTIMATE NUMBER(20) PATH 'Estimate',
  ACTUAL NUMBER(20) PATH 'Actual') xtab
where xtab.ACTUAL > xtab.ESTIMATE 
order by xtab.year desc, d.dname

This is an example of the data contained within an XML database column.

XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
<Budget>
    <Year>FY2010</Year>
    <Estimate currency="USD">570000</Estimate>
    <YearToDate currency="USD">56000</YearToDate>
    <History>
         <Budget>
        <Year>2009</Year>
        <Estimate currency="USD">540000</Estimate>
        <Actual currency="USD">550000</Actual>
      </Budget>
         <Budget>
        <Year>2008</Year>
        <Estimate currency="USD">520000</Estimate>
        <Actual currency="USD">510000</Actual>
      </Budget>
         <Budget>
        <Year>2007</Year>
        <Estimate currency="USD">510000</Estimate>
        <Actual currency="USD">515000</Actual>
      </Budget>
    </History>
</Budget>')

This SQL query is used to generate the XML displayed after the clicking the View XML button.

declare
    l_XML varchar2(32767); 
begin 
    -- force Oracle APEX to not show any HTML
    sys.htp.init;
    wwv_flow.g_page_text_generated := true;
    wwv_flow.g_unrecoverable_error := true;

    -- select XML
     select xmlElement  
      (  
        "Department",  
        xmlAttributes( d.DEPTNO as "DepartmentNumber"),  
        xmlElement("Name", d.DNAME),  
        xmlElement("Location", d.LOC), 
        xmlElement  
        (  
          "EmployeeList",  
          (  
            select xmlAgg  
                   (  
                     xmlElement  
                     (  
                       "Employee",  
                       xmlAttributes ( e.EMPNO as "employeeNumber" ),  
                       xmlForest  
                       (  
                         e.ENAME as "EmpolyeeName", e.JOB as "JobTitle", e.SAL as "EmployeeSalary"  
                       ),  
                       xmlElement ( "Commission", e.COMM )  
                     )  
                   )  
                   from WWV_DEMO_EMP e  
                   where e.DEPTNO = d.DEPTNO  
                 )  
               )  
             ).getClobVal()  
    into l_XML
    from WWV_DEMO_DEPT d 
   where d.DEPTNO = 20; 
 
  -- Display HTML document
  sys.owa_util.mime_header('text/xml',FALSE); 
  sys.htp.p('Content-Length: ' || length(l_XML)); 
  sys.owa_util.http_header_close; 
  sys.htp.print(l_XML); 
end;