Thursday, 27 March 2014

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 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:

No comments:

About Us

Darwin-IT Professionals is a Dutch Consulting Company.

As an intermediary in the Oracle world we focus on the growth of the Oracle professionals market. Our consultants can help you with the realization of your Oracle and Java projects and at the same time work on supporting of your workforce.

Through a combination of practical training and personal and subject-specific coaching our experienced professional support starting ICT-associates during the first phase of their carreer within your company.

This combination of experienced and starting consultants help you to perform your projects successfully and offers you the possibility to organize the acquisition of young, talented ICT-profesionals in a solid and smooth way.