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
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.
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.
select dbms_xmlgen.getXML('select * from yourtable');
