Re: Creating valid and well-formed XML-output

From: willy <w.tadema_at_planet.nl>
Date: 23 Aug 2006 05:26:58 -0700
Message-ID: <1156336018.500429.205450@i3g2000cwc.googlegroups.com>

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_at_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_at_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 Received on Wed Aug 23 2006 - 07:26:58 CDT