Change the XML Prolog using XMLROOT

About a month ago I wrote about using XMLType and RefCursor to rename the ROW and ROWSET tags. You can find that article by clicking here.
For the same project described in that article the XML prolog needed to include the encoding as well. Just in case you are unfamiliar with the XML Prolog, this is it:

<?xml version="1.0"?>

In Oracle 10g there is an XMLROOT function which creates the XML Prolog for you, but it doesn't allow you to add encoding to it. At least not by specifying parameters, but there is a way to do this, read on..

With the XMLRoot function you can create the XML Prolog. Besides the XML value, you need to specify the Version as well. This can be any value that you want.

SQL> select xmlroot (xmltype ('that')
  2                 ,version '24.5'
  3                 ) "XMLRoot"
  4    from dual
  5  /

XMLRoot
---------------------------------------------------------
<?xml version="24.5"?>
<this>that</this>

Quite strangly, you can also specify that you want NO VALUE for the Version. Why strange? Look at this

SQL> select xmlroot (xmltype ('that')
  2                 ,version no value
  3                 ) "XMLRoot"
  4    from dual
  5  /

XMLRoot
----------------------------------------------------
<?xml version="1.0"?>
<this>that</this>

It seems a bit odd to me, specifying NO VALUE but you get a value anyway. This behavior is documented to do so, but why not call it DEFAULT?
The last argument is optional and allows you to create a STANDALONE clause. The exact meaning of it is not clear to me, one day I will find out what it means.

SQL> select xmlroot (xmltype ('that')
  2                 ,version '1.0'
  3                 ,standalone YES
  4                 ) "XMLRoot"
  5    from dual
  6  /

XMLRoot
--------------------------------------------------
<?xml version="1.0" standalone="yes"?>
<this>that</this>

As you can see there is no argument where you can specify the encoding. After searching for a long-long time, I gave my search and decided to concatenate the XML Prolog to the XML (after having extracting it as a CLOB). Not a nice solution, but it works.
And than suddenly last week on the Oracle Technology Network SQL and PL/SQL forum someone asked how to specify the encoding in the XML Prolog... Now why didn't I think of that? I spend way too much time on that forum, and I never thought of it of posting a question there. Anyway, because you can put whatever you want in the Version argument,

SQL> select xmlroot (xmltype ('that')
  2                 ,version 'whatever'
  3                 ) "XMLRoot"
  4    from dual
  5  /

XMLRoot
-----------------------------------------------------
<?xml version="whatever"?>
<this>that</this>

you can also place the encoding that you want in there.

SQL> select xmlroot (xmltype ('that')
  2                 , version '1.0" encoding="utf-8'
  3                 ) "XMLRoot"
  4    from dual
  5  /

XMLRoot
----------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<this>that</this>

Just mind the quotes and double quotes in the Version argument.

Links

OTN SQL and PL/SQL Discussion forum thread
Oracle Documentation on XMLRoot