Satyaki De - SQL Exploration

Hi friends, today we will see one of few useful case where we'll prepare XML from Oracle. From 9i, oracle has incorporated all major functionality which helped us to generate/parse XMLs. For details -> you can refer documents which describe these Oracle XML Functions .

You can also visit the official site which contains all the essential ingredients to learn the basics.

1 - Using XQuery

2 - Generating XML

Let us concentrate on our small test case, now.

We need to produce one XML and initial input will be two dates as date range and also the department number. Date should look in this format -> YYYY-MM-DD

Date should enclose within tag and employee details should enclosed within tag. Each record inside this would be enclosed as tag.

So, the final XML look like this ->

<?xml version="1.0" encoding="UTF-8"?>
<period>
  <START_DT>format of YYYY-MM-DD</START_DT>
  <END_DT>format of YYYY-MM-DD</END_DT>
</period>
<EmployeeList>
  <Emp>
    .... Employee Details ....
  </Emp>
</EmployeeList>

Step - 1

Save the following script in a file called emp_oracle.sql

set feedback off
set verify off
set trimspool on
set long 100000
set heading off
set serveroutput on
set timi off

spool d:\emp_oracle.xml
SELECT XMLROOT (
                   XMLELEMENT("period", XMLFOREST(e.start_dt, e.end_dt))
               ) AS "XMLROOT"
FROM (
          select to_date('&&1','DD-MON-YYYY') start_dt,
                 to_date('&&2','DD-MON-YYYY') end_dt
          from dual
     ) e
union all
SELECT XMLELEMENT(
                    "EmployeeList",
                    XMLAGG(
                            XMLELEMENT(
                                        "Emp",
                                        XMLFOREST(
                                                    e.employee_id as "Employee_ID", 
                                                    e.last_name as "First", 
                                                    e.salary as "Sal", 
                                                    e.hire_date as "HireDate"
                                                 )
                                    )
                          )
                  )
FROM employees e
WHERE department_id = &&3;

spool off

set feedback on
set verify on
set trimspool off
set heading on
set timi on

Step - 2

Execute the above query.

hr@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.00
hr@ORCL>
hr@ORCL>
hr@ORCL>@D:\emp_oracle.sql
Enter value for 1: 01-JAN-1979
Enter value for 2: 05-JAN-2010
Enter value for 3: 10

Step -3

Check the output and it will look like -

<?xml version="1.0" encoding="UTF-8"?>
<period>
  <START_DT>1979-01-01</START_DT>
  <END_DT>2010-01-05</END_DT>
</period>
<EmployeeList>
  <Emp>
   <Employee_ID>200</Employee_ID>
   <First>Whalen</First>
   <Sal>4400</Sal>
   <HireDate>1987-09-17</HireDate>
  </Emp>
</EmployeeList>

Hope this basic & simple simulations will give you initial idea on this topic.

Regards.