Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF



Description of XMLElement.gif follows
Description of the illustration XMLElement.gif

Description of XML_attributes_clause.gif follows
Description of the illustration XML_attributes_clause.gif


XMLElement takes an element name for identifier, an optional collection of attributes for the element, and arguments that make up the content of the element. It returns an instance of type XMLType. XMLElement is similar to SYS_XMLGen except that XMLElement can include attributes in the XML returned, but it does not accept formatting using the XMLFormat object.

The XMLElement function is typically nested to produce an XML document with a nested structure, as in the example in the following section.

You must specify a value for identifier, which Oracle Database uses as the enclosing tag. The identifier does not have to be a column name or column reference. It cannot be an expression or null.

In the XML_attributes_clause, if the value_expr is null, then no attribute is created for that value expression. The type of value_expr cannot be an object type or collection.

The objects that make up the element content follow the XMLATTRIBUTES keyword.


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

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

  <Name>MK_MAN Hartstein</Name>

  <Name>MK_REP Fay</Name>

  <Name>HR_REP Mavris</Name>

  <Name>PR_REP Baer</Name>

  <Name>AC_MGR Higgins</Name>

  <Name>AC_ACCOUNT Gietz</Name>

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:

      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;

Emp Element
<Emp ID="206" LAST_NAME="Gietz">

Notice that the AS identifier clause was not specified for the last_name column. As a result, the XML returned uses the column name last_name as the default.

Finally, the next example uses a subquery within the XML_attributes_clause to retrieve information from another table into the attributes of an element:

SELECT XMLELEMENT("Emp", XMLATTRIBUTES(e.employee_id, e.last_name),
   XMLELEMENT("Dept", XMLATTRIBUTES(e.department_id,
   (SELECT d.department_name FROM departments d
   WHERE d.department_id = e.department_id) as "Dept_name")),
   XMLELEMENT("salary", e.salary),
   XMLELEMENT("Hiredate", e.hire_date)) AS "Emp Element"
   FROM employees e
   WHERE employee_id = 205;

Emp Element
<Emp EMPLOYEE_ID="205" LAST_NAME="Higgins">
  <Dept DEPARTMENT_ID="110" Dept_name="Accounting"/>