Re: How to add encoding attribute to prolog in XML-file

From: willy <w.tadema_at_planet.nl>
Date: 8 Sep 2006 06:05:21 -0700
Message-ID: <1157720721.588713.169110@p79g2000cwp.googlegroups.com>

Dear Maxim,
Thanks for your suggestion. I modified the script and the prolog including the attribute was added. However, I want to create an XML file based upon the query using UTL_FILE. I added code to the script to achieve this and to my surprise the value in the encoding attribute is modified automatically into US-ASCII When I run only the select statement, the output does contain the correct value for the encoding attribute! When I remove the encoding attribute from the xml-prolog in the select statement, the output file also contains an encoding attribute: again the value is US-ASCII.

Kind regards,

Willy Tadema

DECLARE
-- Data Variables

   v_xml              XMLTYPE;
   v_blob             BLOB;
   v_data_length      NUMBER;
   -- Loop Control Variables
   v_offset           NUMBER             DEFAULT 1;
   v_chunk   CONSTANT NUMBER             DEFAULT 4000;
   -- UTL_FILE variables
   fh                 UTL_FILE.file_type;

BEGIN
 select xmltype

           (
             '<?xml version="1.0" encoding="ISO-8955-1"?>' ||
             XMLELEMENT
             (
                "MEDEWERKERSLIJST", XMLATTRIBUTES(to_char(sysdate,

'DD-MM-YYYY HH24:MI:SS') AS "DAT_GEGENEREERD", (SELECT COUNT(*) FROM
MEDEWERKERS) AS "AANTAL_RECORDS"),

                ( select 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
                        )))
                   FROM Medewerkers m
                )
             ).getClobVal()
          )
into v_xml     from dual;

   v_blob := v_xml.getblobval (1);
   v_data_length := DBMS_LOB.getlength (v_blob);

   fh := UTL_FILE.fopen ('D:\CMR_OUTPUT', 'medewerkers.xml', 'wb', v_chunk);

   LOOP

      EXIT WHEN v_offset > v_data_length;
      UTL_FILE.put_raw (fh, DBMS_LOB.SUBSTR (v_blob, v_chunk,
v_offset),
                        TRUE);
      v_offset := v_offset + v_chunk;

   END LOOP;
   UTL_FILE.fclose (fh);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
END;
/ Received on Fri Sep 08 2006 - 08:05:21 CDT