Listing 1• XML Attributes

SQL> SELECT
XMLELEMENT("name", XMLATTRIBUTES( expert_id as "id"),
 expert) FROM experts
 WHERE expert_id BETWEEN 4 and 10;

XMLELEMENT("NAME",XMLATTRIBUTES(EXPERT_ID AS "ID"),EXPERT)

<name id="4">Stotler, Raymond E.</name>
<name id="5">Alfred L. Gardner</name>
<name id="6">Steve J. Upton</name>
<name id="8">Wayne Starnes</name>
<name id="10">Lynne Parenti</name>

Listing 2• XMLForest

SQL>SELECT
XMLELEMENT("expert",
XMLFOREST(expert_id as "id",
expert as "name",
exp_comment as "info")) as "result"
FROM experts
WHERE expert_id = 6;

result

<expert>
  <id>6</id>
  <name>Steve J. Upton</name>
  <info>Professor Division of Biology, Ackert Hall Kansas
  State University Manhattan</info>
</expert>

Listing 3• XMLAgg

SQL>SELECT
XMLELEMENT("experts",
XMLAGG( XMLELEMENT("name", expert)))
FROM experts
WHERE expert_id BETWEEN 4 and 10;


XMLELEMENT("EXPERTS",XMLAGG(XMLELEMENT("NAME",EXPERT)))

<experts>
  <name>Stotler, Raymond E.</name>
  <name>Alfred L. Gardner</name>
  <name>Steve J. Upton</name>
  <name>Wayne Starnes</name>
  <name>Lynne Parenti</name>
</experts>

Listing 4• Result of not using XMLAgg

SQL>SELECT
XMLELEMENT("experts",
XMLELEMENT("name", expert))
FROM experts
WHERE expert_id BETWEEN 4 and 10;


<experts>
  <name>Stotler, Raymond E.</name>
</experts>

<experts>
  <name>Alfred L. Gardner</name>
</experts>

Listing 5• Creating hierarchical date

SQL> SELECT
XMLELEMENT("taxon", XMLATTRIBUTES(tsn as "tsn"),
    XMLELEMENT("name", trim(unit_name1)),
	(SELECT XMLELEMENT("children",
	XMLAGG(
XMLELEMENT("taxon",XMLATTRIBUTES(child.tsn as "tsn"),
    XMLELEMENT("name", trim(child.unit_name1)))))
	    FROM taxonomic_units child
		WHERE child.parent_tsn = prnt.tsn))
FROM taxonomic_units prnt
WHERE prnt.tsn < 10000;

.
.
.
<taxon tsn="9993">
  <name>Plectadinium</name>
  <children/>
</taxon>

<taxon tsn="9994">
  <name>Gymnodiniales</name>
  <children>
    <taxon tsn="9996">
      <name>Gymnodiniaceae</name>
    </taxon>
    <taxon tsn="10127">
      <name>Warnowiaceae</name>
    </taxon>
  </children>
</taxon>

Listing 6• First Step: Register an XSD

BEGIN
 dbms_xmlschema.registerSchema('expert_view.xsd',
  '<?xml version="1.0" encoding="UTF-8"?>
  <!--W3C Schema generated by XMLSPY v5 rel. 4 U
   (http://www.xmlspy.com)-->
  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
  elementFormDefault="qualified">
        <xs:element name="expert">
           <xs:complexType>
              <xs:simpleContent>
                 <xs:extension
                 base="xs:string">
                     <xs:attribute
                     name="id" use=
                     "required">
<xs:simpleType>
<xs:restriction base="xs:string">
</xs:restriction>
</xs:simpleType>
	</xs:attribute>
                          </xs:extension>
                        </xs:simpleContent>
                	</xs:complexType>
        		</xs:element>
        	<xs:element name="experts">
                <xs:complexType>
                   <xs:sequence>
                      <xs:element ref="expert"
                      maxOccurs="unbounded"/>
                        </xs:sequence>
                </xs:complexType>
        </xs:element>
</xs:schema>’
);
END;
/