XMLElement to construct XML instances from relational data. It takes as arguments an element name, an optional collection of attributes for the element, and zero or more additional arguments that make up the element content. It returns an XMLType instance.
Example 1:
Formatting a Date
This example shows how to produce an XML date with a format different from the XML Schema standard format.
-- With standard XML date format:
SELECT XMLElement("Date", hire_date)
FROM hr.employees
WHERE employee_id = 203;
Example 2:
-- With an alternative date format:
SELECT XMLElement("Date", to_char(hire_date))
FROM hr.employees
WHERE employee_id = 203;
Example 3:
Generating an Element for Each Employee
This example produces an Emp element for each employee, with the employee name as its content:
SELECT e.employee_id,
XMLELEMENT ("Emp", e.first_name ||' '|| e.last_name) AS "RESULT"
FROM hr.employees e
WHERE employee_id > 200;
Example 4:
Generating Nested XML
To produce an Emp element for each employee, with elements that provide the employee name and hire date, do the following:
SELECT XMLElement("Emp",
XMLElement("name", e.first_name ||' '|| e.last_name),
XMLElement("hiredate", e.hire_date)) AS "RESULT"
FROM hr.employees e
WHERE employee_id > 200 ;
Example 5:
Generating Employee Elements with ID and Name Attributes
This example produces an Emp element for each employee, with an id and name attribute:
SELECT XMLElement("Emp", XMLAttributes(
e.employee_id as "ID",
e.first_name ||' ' || e.last_name AS "name"))
AS "RESULT"
FROM hr.employees e
WHERE employee_id > 200;
Example 6:
As mentioned in "Escaping Characters in Generated XML Data", characters in the root-element name and the names of any attributes defined by AS clauses are not escaped. Characters in an identifier name are escaped only if the name is created from an evaluated expression (such as a column reference). The following query shows that the root-element name and the attribute name are not escaped. Invalid XML is produced because greater-than sign (>) and a comma (,) are not allowed in XML element and attribute names.
SELECT XMLElement("Emp->Special",
XMLAttributes(e.last_name || ', ' || e.first_name
AS "Last,First"))
AS "RESULT"
FROM hr.employees e
WHERE employee_id = 201;
Example 7:
Using Namespaces to Create a Schema-Based XML Document
This example illustrates the use of namespaces to create an XML schema-based document. Assuming that an XML schema "
http://www.oracle.com/Employee.xsd" exists and has no target namespace, then the following query creates an XMLType instance conforming to that schema:
SELECT XMLElement("Employee",
XMLAttributes('http://www.w3.org/2001/XMLSchema' AS
"xmlns:xsi",
'http://www.oracle.com/Employee.xsd' AS
"xsi:nonamespaceSchemaLocation"),
XMLForest(employee_id, last_name, salary)) AS "RESULT"
FROM hr.employees
WHERE department_id = 10;
Example 7:
Generating an Element from a User-Defined Datatype Instance
Example shows an XML document with employee information. You can generate a hierarchical XML document with the employee and department information as follows:
CREATE OR REPLACE TYPE emp_t AS OBJECT ("@EMPNO" NUMBER(4),
ENAME VARCHAR2(10));
/
Type created.
CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t;
/
Type created.
CREATE OR REPLACE TYPE dept_t AS OBJECT ("@DEPTNO" NUMBER(2),
DNAME VARCHAR2(14),
EMP_LIST emplist_t);
/
Type created.
SELECT XMLElement("Department",
dept_t(department_id,
department_name,
CAST(MULTISET(SELECT employee_id, last_name
FROM hr.employees e
WHERE e.department_id = d.department_id)
AS emplist_t)))
AS deptxml
FROM hr.departments d
WHERE d.department_id = 10;
