How to add encoding attribute to prolog in XML-file

From: willy <w.tadema_at_planet.nl>
Date: 7 Sep 2006 11:38:01 -0700
Message-ID: <1157654281.224337.159500@d34g2000cwd.googlegroups.com>

Hello,

I created the following script to generate an XML-file from the tables in my database:

set serveroutput on
set long 500000
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 160
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF SELECT XMLROOT(XMLCONCAT(
        XMLELEMENT("MEDEWERKERSLIJST", XMLATTRIBUTES(to_char(sysdate, 'DD-MM-YYYY
HH24:MI:SS') AS "DAT_GEGENEREERD", COUNT(*) AS "AANTAL_RECORDS"),         XMLAGG(XMLELEMENT("MEDEWERKER", XMLFOREST(m.Registratienr AS REGISTRATIENR,

                                                  m.Voornaam AS
VOORNAAM,
                                                  m.Achternaam AS
ACHTERNAAM,
                                                  m.Tussenvoegsels AS
TUSSENVOEGSELS,
                                                  m.Voorletters AS
VOORLETTERS,
                                                  m.Geslacht AS
GESLACHT,
                                                  m.Dat_Uit_Dienst AS
DAT_UIT_DIENST,
                                                  m.Gebruikersnaam AS
GEBRUIKERSNAAM,
                                                  m.Email AS EMAIL,
                                                  m.Telefoon AS
TELEFOON,
                                                  m.GSM AS GSM,
                                                  m.Lokatie AS LOKATIE,
                                                  m.Kamernr AS KAMERNR,
                                                  m.DAT_MUTATIE as

DAT_MUTATIE
),

                                          DECODE(m.EXTERN, 1,
XMLELEMENT("EXTERN"), NULL),
                                          DECODE(m.STAGIAIR, 1,
XMLELEMENT("STAGIAIR"), NULL),
               (SELECT XMLAGG(XMLELEMENT("WERKGEVER",

XMLATTRIBUTES(w.werkgevernr AS "WERKGEVERNR"), w.WERKGEVER, (SELECT XMLAGG(XMLELEMENT("AFDELING", XMLATTRIBUTES(a.afdelingnr AS "AFDELINGNR"), a.AFDELING,(SELECT XMLAGG(XMLELEMENT("CLUSTER", XMLATTRIBUTES(c.clusnr AS "CLUSTERNR", DECODE(c.primair, 1, 'primair', 'secundair') AS "NIVEAU"), c.clus,
(SELECT XMLAGG(XMLELEMENT("FUNCTIE", f.functie)) FROM FUNCTIES f

               WHERE c.clusid = f.clusid)
)) FROM CLUSTERS c

               WHERE a.afdelingid = c.afdelingid)
)) FROM AFDELINGEN a

               WHERE w.werkgeverid = a.werkgeverid)
)) FROM WERKGEVERS w

               WHERE m.registratienr = w.registratienr )     )))),
    Version '1.0',
    Standalone NO VALUE) as xml
    FROM Medewerkers m

The script works fine, but I have to alter the prolog into <? xml version="1.0" encoding="iso-8859-1" ?> because the default character set doesn't support characters like é, ü, â, ....
I've looked at the syntax for XLMROOT, but there's no option for including an enconding attribute.
I've also tried dropping the XMLROOT function and concatenating a string containing the prolog
SELECT '<?xml "version=1.0" encoding="iso-8859-1"' | | XMLELEMENT("MEDEWERKERSLIJST ....etc.)
but that doesn't seem to work either... Any help would be appreciated.

Kind regards,

Willy Received on Thu Sep 07 2006 - 13:38:01 CDT