I started looking into the
DBMS_XMLGEN package when we had a need to do character escaping of some XML
text. Basically we needed to convert all the < and > characters to <
and > in PL/SQL.
Here is a quick script to demonstrate using DBMS_XMLGEN.CONVERT
to do this.
To convert to a simple string ‘<>CAT<>’
SELECT DBMS_XMLGEN.CONVERT(xmldata=>'<>CAT<>',
flag=>0) FROM dual
results in the string
<>CAT<>
Then to convert back the other way.
SELECT DBMS_XMLGEN.CONVERT(xmldata=>'<>CAT<>',
flag=>1) FROM dual
results in the original string
<>CAT<>
With the flags instead of using 0 or 1 you should be able to
use DBMS_XMLGEN.ENCODE and DBMS_XMLGEN.DECODE but I have not been able to get
these to be recognised which might be an environmental issue in this database.
Looking further into the DBMS_XMLGEN package I found that
is can do a great deal more with XML processing. Here is a basic example of
running a query and having it formatted as XML then using dbms_output.put_line
to display the CLOB.
DECLARE
v_ctxhandle DBMS_XMLGEN.ctxHandle;
v_clob CLOB;
v_offset NUMBER := 1;
BEGIN
v_ctxhandle
:= DBMS_XMLGEN.NEWCONTEXT (
'SELECT
employee_id ID,
last_name,
to_char(hire_date,
''DD/MM/YYYY'') Hire
FROM
employees
WHERE
employee_id < 105');
v_clob :=
DBMS_XMLGEN.getXML(v_ctxhandle);
loop exit
when v_offset > dbms_lob.getlength(v_clob);
dbms_output.put_line(dbms_lob.substr(
v_clob, 32767, v_offset));
v_offset := v_offset +
32767;
end loop;
DBMS_LOB.FREETEMPORARY(v_clob);
DBMS_XMLGEN.CLOSECONTEXT(v_ctxhandle);
END;
This results in this XML
<?xml version="1.0"?>
<ROWSET>
<ROW>
<ID>100</ID>
<LAST_NAME>King</LAST_NAME>
<HIRE>17/06/1987</HIRE>
</ROW>
<ROW>
<ID>101</ID>
<LAST_NAME>Kochhar</LAST_NAME>
<HIRE>21/09/1989</HIRE>
</ROW>
<ROW>
<ID>102</ID>
<LAST_NAME>De Haan</LAST_NAME>
<HIRE>13/01/1993</HIRE>
</ROW>
<ROW>
<ID>103</ID>
<LAST_NAME>Hunold</LAST_NAME>
<HIRE>03/01/1990</HIRE>
</ROW>
<ROW>
<ID>104</ID>
<LAST_NAME>Ernst</LAST_NAME>
<HIRE>21/05/1991</HIRE>
</ROW>
</ROWSET>
Now to further expand the example we can include some
options to change the way the XML is handled.
DECLARE
v_ctxhandle
DBMS_XMLGEN.ctxHandle;
v_clob CLOB;
v_offset NUMBER := 1;
BEGIN
v_ctxhandle
:= DBMS_XMLGEN.NEWCONTEXT (
'SELECT
employee_id ID,
last_name,
to_char(hire_date, ''DD/MM/YYYY'') Hire
FROM employees
WHERE
employee_id < 105');
-- Pretty print will do the nice indentation.
DBMS_XMLGEN.setPrettyPrinting(v_ctxhandle, TRUE);
-- The indentation width can be set.
DBMS_XMLGEN.setIndentationWidth(v_ctxhandle, 2);
-- Set the tag for the rowset
DBMS_XMLGEN.setrowsettag(v_ctxhandle, 'PEOPLE');
-- Set the tag for the row.
DBMS_XMLGEN.setrowtag(v_ctxhandle, 'PERSON');
-- when we retrieve row from this query only get two at a
time, this can let
-- you do things like control the amount of data you show on one page.
DBMS_XMLGEN.setmaxrows(v_ctxhandle,
2);
-- Now do the query as before
v_clob :=
DBMS_XMLGEN.getXML(v_ctxhandle);
loop exit
when v_offset > dbms_lob.getlength(v_clob);
dbms_output.put_line(dbms_lob.substr( v_clob, 32767, v_offset));
v_offset := v_offset + 32767;
end loop;
dbms_output.put_line('Can do some new page processing here');
DBMS_LOB.FREETEMPORARY(v_clob);
v_offset :=
1;
v_clob :=
DBMS_XMLGEN.getXML(v_ctxhandle);
loop exit
when v_offset > dbms_lob.getlength(v_clob);
dbms_output.put_line(dbms_lob.substr( v_clob, 32767, v_offset));
v_offset := v_offset + 32767;
end loop;
DBMS_LOB.FREETEMPORARY(v_clob);
DBMS_XMLGEN.CLOSECONTEXT(v_ctxhandle);
END;
<?xml version="1.0"?>
<PEOPLE>
<PERSON>
<ID>100</ID>
<LAST_NAME>King</LAST_NAME>
<HIRE>17/06/1987</HIRE>
</PERSON>
<PERSON>
<ID>101</ID>
<LAST_NAME>Kochhar</LAST_NAME>
<HIRE>21/09/1989</HIRE>
</PERSON>
</PEOPLE>
Can do some new page processing here
<?xml version="1.0"?>
<PEOPLE>
<PERSON>
<ID>102</ID>
<LAST_NAME>De Haan</LAST_NAME>
<HIRE>13/01/1993</HIRE>
</PERSON>
<PERSON>
<ID>103</ID>
<LAST_NAME>Hunold</LAST_NAME>
<HIRE>03/01/1990</HIRE>
</PERSON>
</PEOPLE>
There are lots of ways of doing XML processing from PL/SQL
and DBMS_XMLGEN seems to offer many options that can be explored further.
