Re: Creating valid and well-formed XML-output

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_at_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_at_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_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org