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...
No comments:
Post a Comment