Years ago I wrote an article (in Dutch) on the XML functions in Oracle SQL. It can be found here.
It describes how to create an xml document as an XMLType with an Oracle SQL Query.

The query that is described is based on a pretty simple table, with no relationships. I'm creating a new course based on a datamodel we created years ago, that contains data. I wanted to abstract some of that data as xml, but then: how about the foreign key relations?

It turns out pretty straightforward, that you can probably figure out yourself. But hey, I'm not a bad guy, so how about sharing it to you?

The query selects employees with their addresses and goes as follows:

select xmlelement("emp:employees"
  , xmlattributes( 'http://xmlnls.darwin-it.nl/doe/xsd/v1/employee' as "xmlns"
                 , 'http://xmlnls.darwin-it.nl/doe/xsd/v1/employee' as "xmlns:emp"
                 , 'http://xmlnls.darwin-it.nl/doe/xsd/v1/address' as "xmlns:ads")
  , xmlagg
  ( xmlelement
    ( "emp:employee"
    , xmlforest
      ( emp.title as "emp:title"
      , emp.firstname as "emp:firstName"
      , emp.last_name as "emp:lastName"
      , emp.gender as "emp:gender"
      , emp.birth_date as "emp:birthDate"
      )
    , ( select xmlelement( "emp:addresses"
        , xmlagg(
             xmlelement("ads:address"
             , xmlattributes( ate.code as "type"
                            , ate.description as "description")
             , xmlforest
               ( ads.adress_line1 as "ads:addressLine1"
               , ads.adress_line2 as "ads:addressLine2"
               , ads.adress_line3 as "ads:addressLine3"
               , ads.postal_code as "ads:postalCode"
               , ads.city as "ads:city"
               , ads.country as "ads:country"
               )
             )
          )
        )
        from doe_party_addresses pae
        join doe_addresses ads on ads.id = pae.ads_id
        join doe_address_types ate on ate.id= pae.ate_id
        where pae.emp_id = emp.id
        )
      )
    )
  )  xml
from doe_employees emp;

The doe_addresses table is joined with the doe_employees table via a couple-table named doe_party_addresses. This is because an employee can have multiple addresses, but with different types. An employee can have a Business address and a Home address. Like a customer can have a shipping, billing and visiting addresses. Also an address can be used by multiple parties. You see here that the addresses are selected as a subselect. The output of the sub-select is an XMLType that can be embedded in the xmlelement (and other xml-) functions. What you also see is that I added namespace declarations as xmlattributes on the top-level element. The element and attribute names are prefixed with the corresponding 'emp:' or 'ads:' namespace-prefixes. Oh, and the output of the query is something like:

<?xml version="1.0" encoding="UTF-8" ?>
<emp:employees xmlns="http://xmlnls.darwin-it.nl/doe/xsd/v1/employee"
               xmlns:emp="http://xmlnls.darwin-it.nl/doe/xsd/v1/employee"
               xmlns:ads="http://xmlnls.darwin-it.nl/doe/xsd/v1/address">
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>Ed</emp:firstName>
    <emp:lastName>Bushes</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1968-01-20</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Maasstraat 19</ads:addressLine1>
        <ads:postalCode>3812HS</ads:postalCode>
        <ads:city>Amersfoort</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>M.</emp:firstName>
    <emp:lastName>Outback</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1961-10-14</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Rocky Road 2</ads:addressLine1>
        <ads:postalCode>20001</ads:postalCode>
        <ads:city>StoneHench</ads:city>
        <ads:country>UNITED KINGDOM</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>M.</emp:firstName>
    <emp:lastName>Outback</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1961-10-14</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Hofvijver 12</ads:addressLine1>
        <ads:postalCode>2000XX</ads:postalCode>
        <ads:city>Den Haag</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Ms.</emp:title>
    <emp:firstName>Pat</emp:firstName>
    <emp:lastName>Darwin</emp:lastName>
    <emp:gender>F</emp:gender>
    <emp:birthDate>1980-03-14</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Rijnzathe 6</ads:addressLine1>
        <ads:postalCode>3140ZP</ads:postalCode>
        <ads:city>De Meern</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>T.</emp:firstName>
    <emp:lastName>Barakus</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1970-02-11</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Erasmusstraat 312</ads:addressLine1>
        <ads:postalCode>1234GK</ads:postalCode>
        <ads:city>Rotterdam</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Ms.</emp:title>
    <emp:firstName>Debby</emp:firstName>
    <emp:lastName>Waters</emp:lastName>
    <emp:gender>F</emp:gender>
    <emp:birthDate>1982-01-20</emp:birthDate>
    <emp:addresses>
      <ads:address type="WORK" description="Work address">
        <ads:addressLine1>Darwinplein 11</ads:addressLine1>
        <ads:postalCode>4321PS</ads:postalCode>
        <ads:city>Amsterdam</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Newtonweg 41</ads:addressLine1>
        <ads:postalCode>6543AB</ads:postalCode>
        <ads:city>Maasland</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>Wally</emp:firstName>
    <emp:lastName>Waters</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1963-06-13</emp:birthDate>
    <emp:addresses></emp:addresses>
  </emp:employee>
</emp:employees>

2014-04-25, update: I changed the namespaces and I found a curiosity in my database content: all birthdates were in the future...