Oracle XMLELEMENT Example 1

The following example produces an Emp element for a series of employees, with nested elements that provide the employee's name and hire date:

SELECT XMLELEMENT("Emp", XMLELEMENT("Name",
   e.job_id||' '||e.last_name),
   XMLELEMENT("Hiredate", e.hire_date)) as "Result"
   FROM employees e WHERE employee_id > 200;

Result
-------------------------------------------------------------------

  MK_MAN Hartstein
  17-FEB-96


  MK_REP Fay
  17-AUG-97


  HR_REP Mavris
  07-JUN-94


  PR_REP Baer
  07-JUN-94


  AC_MGR Higgins
  07-JUN-94


  AC_ACCOUNT Gietz
  07-JUN-94

6 rows selected.

The following similar example uses the XMLElement function with the XML_attributes_clause to create nested XML elements with attribute values for the top-level element:

SELECT XMLELEMENT("Emp",
      XMLATTRIBUTES(e.employee_id AS "ID", e.last_name),
      XMLELEMENT("Dept", e.department_id),
      XMLELEMENT("Salary", e.salary)) AS "Emp Element"
   FROM employees e
   WHERE e.employee_id = 206;