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.