I'd like to create a script to generate a valid XML-file based upon the
contents of a table containing employee information.
So far, I've come up with the following:
SELECT SYS_XMLGEN(XMLELEMENT("Medewerker",
XMLFOREST(Registratienr,
Voornaam,
Achternaam,
Tussenvoegsel,
Voorletters,
Geslacht,
Datum_uit_dienst,
Gebruikersnaam,
Email
)))
FROM medewerkers
/

This results into the following XML-file:

<?xml version="1.0"?>
<Medewerkerslijst>
<ROW>
<REGISTRATIENR>123456789</REGISTRATIENR>
<VOORNAAM>John</VOORNAAM>
<ACHTERNAAM>Jansen</ACHTERNAAM>
<VOORLETTERS>J</VOORLETTERS>
<GESLACHT>M</GESLACHT>
<GEBRUIKERSNAAM>xxx222</GEBRUIKERSNAAM>
<EMAIL>john@ourcompany.nl</EMAIL>
</ROW>
<ROW>
<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>
</ROW>
</Medewerkerslijst>

This is a valid XML-file, but in order for the file to be wellformed as
well as valid I'll have to change the element <ROW> into <MEDEWERKER>.
In addition, I'd like to add elements to point to a style sheet, e.g.
<?xml-stylesheet href="test.xsl" type="text/xsl" ?>, and a reference to
an XML-schema in the root-element <Medewerkerslijst>.
I think I ought to achieve this by using the XMLFORMAT.createformat
statement, but I can't seem to figure it out.
Any help would be appreciated.

Willy Tadema

willy wrote:
> I'd like to create a script to generate a valid XML-file based upon the
> contents of a table containing employee information.
> So far, I've come up with the following:
> SELECT SYS_XMLGEN(XMLELEMENT("Medewerker",
> XMLFOREST(Registratienr,
> Voornaam,
> Achternaam,
> Tussenvoegsel,
> Voorletters,
> Geslacht,
> Datum_uit_dienst,
> Gebruikersnaam,
> Email
> )))
> FROM medewerkers
> /
>
> This results into the following XML-file:
>
> <?xml version="1.0"?>
> <Medewerkerslijst>
> <ROW>
> <REGISTRATIENR>123456789</REGISTRATIENR>
> <VOORNAAM>John</VOORNAAM>
> <ACHTERNAAM>Jansen</ACHTERNAAM>
> <VOORLETTERS>J</VOORLETTERS>
> <GESLACHT>M</GESLACHT>
> <GEBRUIKERSNAAM>xxx222</GEBRUIKERSNAAM>
> <EMAIL>john@ourcompany.nl</EMAIL>
> </ROW>
> <ROW>
> <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>
> </ROW>
> </Medewerkerslijst>
>
> This is a valid XML-file, but in order for the file to be wellformed as
> well as valid I'll have to change the element <ROW> into <MEDEWERKER>.
> In addition, I'd like to add elements to point to a style sheet, e.g.
> <?xml-stylesheet href="test.xsl" type="text/xsl" ?>, and a reference to
> an XML-schema in the root-element <Medewerkerslijst>.
> I think I ought to achieve this by using the XMLFORMAT.createformat
> statement, but I can't seem to figure it out.
> Any help would be appreciated.
>
> Willy Tadema

Look at dbms_xmlgen.setXSLT.

Last time I looked it was undocumented but it has has three overloads
and you might find one that works for you.

dbms_xmlgen.setXSLT(ctx IN ctxType, stylesheet IN CLOB);
dbms_xmlgen.setXSLT(ctx IN ctxType, stylesheet IN XMLType);
dbms_xmlgen.setXSLT(ctx IN ctxType, uri IN VARCHAR2);

HTH
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thanks Daniel, I will look in to it!
Do you know of a way to add attributes to the root element?
I'd lik to add a date and timestamp, target namespace and xml schema
reference.

Kind regards,

Willy Tadema

willy schrieb:
> I'd like to create a script to generate a valid XML-file based upon the
> contents of a table containing employee information.
> So far, I've come up with the following:
> SELECT SYS_XMLGEN(XMLELEMENT("Medewerker",
> XMLFOREST(Registratienr,
> Voornaam,
> Achternaam,
> Tussenvoegsel,
> Voorletters,
> Geslacht,
> Datum_uit_dienst,
> Gebruikersnaam,
> Email
> )))
> FROM medewerkers
> /
>
> This results into the following XML-file:
>
> <?xml version="1.0"?>
> <Medewerkerslijst>
> <ROW>
> <REGISTRATIENR>123456789</REGISTRATIENR>
> <VOORNAAM>John</VOORNAAM>
> <ACHTERNAAM>Jansen</ACHTERNAAM>
> <VOORLETTERS>J</VOORLETTERS>
> <GESLACHT>M</GESLACHT>
> <GEBRUIKERSNAAM>xxx222</GEBRUIKERSNAAM>
> <EMAIL>john@ourcompany.nl</EMAIL>
> </ROW>
> <ROW>
> <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>
> </ROW>
> </Medewerkerslijst>
>
> This is a valid XML-file, but in order for the file to be wellformed as
> well as valid I'll have to change the element <ROW> into <MEDEWERKER>.
> In addition, I'd like to add elements to point to a style sheet, e.g.
> <?xml-stylesheet href="test.xsl" type="text/xsl" ?>, and a reference to
> an XML-schema in the root-element <Medewerkerslijst>.
> I think I ought to achieve this by using the XMLFORMAT.createformat
> statement, but I can't seem to figure it out.
> Any help would be appreciated.
>
> Willy Tadema
>

Maybe this works for you.

1 SELECT Xmlroot(Xmlconcat(Xmlpi(NAME
"xml-stylesheet",'href="test.xsl" type="text/xsl"'),
2 Xmlelement("Medewerkerslijst",
3 Xmlattributes('http://www.my.schema' AS "xmlns"),
4 Xmlagg(Xmlelement("Medewerker",
5 Xmlforest(Registratienr,
6 Voornaam,
7 Achternaam,
8 Tussenvoegsel,
9 Voorletters,
10 Geslacht,
11 Datum_Uit_Dienst,
12 Gebruikersnaam,
13 Email))))),
14 Version '1.0',
15 Standalone NO VALUE) as xml
16* FROM Medewerkers

XML
-----------------------------------------------------------------------------------------------------------------------------
<?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>
</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>
</Medewerker>
</Medewerkerslijst>


Best regards

Maxim

Dear Maxim,

Thank you for your posting! It was very helpfull. I didn't respond any
earlier because I didn't read my mail last week because of summer
holidays.

I've got an additional question: say I want to add an empty element
depending on whether the employee is hired (temporarily) from another
company or on the payrole of our own company.
The output should be:
<Medewerkerslijst ....>
<Medewerker>
<REGISTRATIENR>123456789</REGISTRATIENR>
<VOORNAAM>John</VOORNAAM>
<ACHTERNAAM>Jansen</ACHTERNAAM>
<VOORLETTERS>J</VOORLETTERS>
<GESLACHT>M</GESLACHT>
<GEBRUIKERSNAAM>xxx222</GEBRUIKERSNAAM>
<EMAIL>john@ourcompany.nl</EMAIL>
</EXTERN> ====>>> This element is lacking when an employee is
on the payrole of our own company
</Medewerker>
......
</Medewerkerslijst>

Any idea on how to achieve this? Any help is appreciated.

Kind regards,

Willy Tadema

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

willy schrieb:

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

It seems, your relational design is not ok, if relationship between
employees and departments is not n:1, but n:m, so you need an additional
table to resolve this relationship. If you have done this step, then you
could join both tables and get result in tabular form (however you
would get some rows for every employee employed in more than one
department). To get results as you wish, you'll need some kind
aggregation, from here transform into xml is basic thing. If you could
post ddl for your tables, somebody can suggest appropriate query for you.

Best regards

Maxim