René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

XMLElement [Oracle SQL]

XMLElement (element-name)
XMLElement (element-name, value-expr)
XMLElement (element-name, XMLAttributes( (value-expr))
XMLElement (element-name, XMLAttributes( (value-expr), value-expr)

An example

create table xml_element_test (
  some_id    number   primary key,
  some_col_1 varchar2(10),
  some_col_2 date,
  some_col_3 number(5)
);
alter session set nls_date_format='dd-mon-yyyy';

insert into xml_element_test values( 1, 'one'  , '15-jan-1974', 74);
insert into xml_element_test values( 2, 'two'  , '28-mar-1891', 18);
insert into xml_element_test values( 3, 'three', ' 7-sep-1599', 59);
insert into xml_element_test values( 4, 'four' , '10-aug-1616', 16);
insert into xml_element_test values( 5, 'five' , '19-oct-1904', 19);
select XMLElement ("SomeElementName", some_col_1) from xml_element_test;
XMLELEMENT("SOMEELEMENTNAME",SOME_COL_1)
----------------------------------------------------
<SomeElementName>one</SomeElementName>
<SomeElementName>two</SomeElementName>
<SomeElementName>three</SomeElementName>
<SomeElementName>four</SomeElementName>
<SomeElementName>five</SomeElementName>
set long 1000

select XMLElement ("SomeElementName", 
  XMLAttributes(some_id as "SomeAttrName"),
  XMLElement("col-1", some_col_1),
  XMLElement("col-2", some_col_2),
  XMLElement("col-3", some_col_2)
) "Elem"
from 
  xml_element_test
where 
  some_id=4;
As can be seen, XMLElement constructs XML Elements and can be used to construct nested XML elements:
Elem
---------------------------------------------------------------------------------------------------------------------------------
<SomeElementName SomeAttrName="4"><col-1>four</col-1><col-2>10-aug-1616</col-2><col-3>10-aug-1616</col-3></SomeElementName>