Home

Description

Oracle Database 23ai deprecated DBMS_XMLGEN, which converts the results of SQL queries to XML with procedures such as getXML and getXMLType. Oracle advises generating XML with standard SQL/XML constructs, e.g. XMLELEMENT, XMLAGG, and XMLSERIALIZE, to ensure portability and continued support.

Key Benefits

• Upgrade Safety: Code compiles unchanged on future Oracle versions where the package may be removed.
• Optimizer Visibility: Pure SQL/XML enables cost-based optimisation and index usage.
• Maintainability: Removes bespoke context objects (newContext, closeContext) in favour of straightforward SQL.

Non-compliant Code Example

DECLARE
  ctx  DBMS_XMLGEN.ctxType; --Non compliant code
  xml  CLOB;
BEGIN
  ctx := DBMS_XMLGEN.newContext('SELECT empno, ename FROM emp'); --Non compliant code
  xml := DBMS_XMLGEN.getXML(ctx);   --Non compliant code
  DBMS_XMLGEN.closeContext(ctx);    --Non compliant code
  DBMS_OUTPUT.put_line(xml);
END;
/



DECLARE
  ctx     DBMS_XMLGEN.ctxType;       
  rowsnum NUMBER;
  xmlDoc  CLOB :=
    '7788SCOTT1000';
BEGIN
  ctx     := DBMS_XMLGEN.newContext('EMP');       --Non compliant code
  rowsnum := DBMS_XMLGEN.insertXML(ctx, xmlDoc);       --Non compliant code
  DBMS_XMLGEN.closeContext(ctx);       --Non compliant code
END;

Compliant Code Example

SELECT XMLSERIALIZE(
         DOCUMENT
           XMLROOT(
             XMLELEMENT("ROWSET",
               XMLAGG(
                 XMLELEMENT("ROW",
                   XMLELEMENT("EMPNO", empno),
                   XMLELEMENT("ENAME", ename))))
             VERSION '1.0') AS CLOB)
  AS emp_xml
FROM   emp;     --Compliant code