Encoding strings in XML from Oracle query

Question

I'm producing XML right from PL/SQL in Oracle.

What is the preferred way of ensuring that outputted strings are XML-conformant, with regards to special characters and character encoding ?

Most of the XML file is static, we only need to output data for a few fields.

Example of what I consider bad practice:

DECLARE @s AS NVARCHAR(100)
SELECT  @s = 'Test chars = (<>, æøåÆØÅ)'

SELECT  '<?xml version="1.0" encoding="UTF-8"?>'
      + '<root><foo>'
      + @s
      + '</foo></root>' AS XML

Answer 1

There are two good ways to generate XML that I've found. One is the SYS.XMLDOM package which is essentially a wrapper around the Java DOM API. It's somewhat clunky because pl/sql doesn't have the polymorphic capabilities of Java, so you constantly have to explicitly "cast" elements to nodes and vice versa to use the methods in the package.

The coolest, IMO, technique is to use the XMLElement, etc, SQL functions like this:

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE

    v_xml XMLTYPE;

BEGIN

    SELECT
        XMLElement( "dual",
            XMLAttributes( dual.dummy AS "dummy" )
        )
    INTO
        v_xml
    FROM
        dual;

    dbms_output.put_line( v_xml.getStringVal() );

END;
/

If your XML structure is not very complex and maps easily to your table structure then this can be very handy.

Answer 2

Using XmlElement, XmlAttribute, ... is the best way to generate xml.
The following link gives a good intro in all functions there are:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#ADXDB1620

If you look for some more ways, look at here
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#sthref1486

Whatchout for the encoding. If you run a pl/sql programm or a select generating xml in a client session. The xml is encoded with client codepage. If you run it in background job (using dbms_job or dbms_scheduler) it is encoded with database codepage.