Dear Maxim,
I seem to have solved my problem about adding an empty element for
those employees that are hired from another company: I've added an
DECODE statement.
SELECT XMLROOT(XMLCONCAT(XMLPI(NAME "xml-stylesheet",'href="test.xsl"
type="text/xsl"'),
XMLELEMENT("MEDEWERKERSLIJST",
XMLATTRIBUTES('http://www.my.schema' AS "xmlns",
to_char(sysdate,
'DD-MM-YYYY HH24:MI:SS') AS "gegenereerd_op"),
XMLAGG(XMLELEMENT("MEDEWERKER", XMLFOREST(m.Registratienr,
m.Voornaam,
m.Achternaam,
m.Tussenvoegsel,
m.Voorletters,
m.Geslacht,
m.Datum_Uit_Dienst,
m.Gebruikersnaam,
m.Email),
DECODE(m.EXTERN, 1,
XMLELEMENT("EXTERN"), NULL)
)))),
Version '1.0',
Standalone NO VALUE) as xml
FROM Medewerkers m
As I am an Oracle newbie, do you think this is the way to go?
I realized I still have one problem to tackle: the employee table
should be joined with the department table so the XML output contains
one or more departments for every employee, e.g.:
<?xml version="1.0"?>
<?xml-stylesheet href="test.xsl" type="text/xsl"?>
<Medewerkerslijst xmlns="http://www.my.schema">
<Medewerker>
<REGISTRATIENR>123456789</REGISTRATIENR>
<VOORNAAM>John</VOORNAAM>
<ACHTERNAAM>Jansen</ACHTERNAAM>
<VOORLETTERS>J</VOORLETTERS>
<GESLACHT>M</GESLACHT>
<GEBRUIKERSNAAM>xxx222</GEBRUIKERSNAAM>
<EMAIL>john@ourcompany.nl</EMAIL>
<DEPARTMENT> Finance and Control</DEPARTMENT>
</Medewerker>
<Medewerker>
<REGISTRATIENR>987654321</REGISTRATIENR>
<VOORNAAM>Pietje</VOORNAAM>
<ACHTERNAAM>Boer</ACHTERNAAM>
<TUSSENVOEGSEL>de</TUSSENVOEGSEL>
<VOORLETTERS>P.</VOORLETTERS>
<GESLACHT>M</GESLACHT>
<GEBRUIKERSNAAM>xxx111</GEBRUIKERSNAAM>
<EMAIL>pietje@ourcompany.nl</EMAIL>
<DEPARTMENT> Finance and Control</DEPARTMENT>
<DEPARTMENT> FZ</DEPARTMENT>
</Medewerker>
</Medewerkerslijst>
So, an employee can work for more than one department.
So far, I've come up with the following:
SELECT XMLROOT(XMLCONCAT(XMLPI(NAME "xml-stylesheet",'href="test.xsl"
type="text/xsl"'),
XMLELEMENT("MEDEWERKERSLIJST",
XMLATTRIBUTES('http://www.provinciegroningen.nl/cmr' AS "xmlns",
to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS') AS "gegenereerd_op"),
XMLAGG(XMLELEMENT("MEDEWERKER", XMLFOREST(m.Registratienr,
m.Voornaam,
m.Achternaam,
m.Tussenvoegsel,
m.Voorletters,
m.Geslacht,
m.Datum_Uit_Dienst,
m.Gebruikersnaam,
m.Email),
DECODE(m.EXTERN, 1,
XMLELEMENT("EXTERN"), NULL),
(SELECT XMLELEMENT(Department) FROM
Registratienr_Departments r
WHERE m.registratienr = r.registratienr )
)))),
Version '1.0',
Standalone NO VALUE) as xml
FROM Medewerkers m
However, when I run this query I get the following error:
ORA-01427: single-row subquery returns more than one row.
Apparantly I should convert the result of the subquery into a single
row.
Any suggestions on how to do this?
Willy Tadema