DBMS_XMLGEN

Prepare data

The examples in the following sections use the employees table. Run the following SQL statements in your cluster to create and populate the table.

-- Create the employees table
CREATE TABLE employees (
    "EMP_id" NUMBER,
    name VARCHAR2(50),
    dept VARCHAR2(30),
    "SALARY" NUMBER
);

-- Insert test data
INSERT INTO employees VALUES (1, 'Zhang San', 'Technical Department', 15000);
INSERT INTO employees VALUES (2, 'Li Si', 'Finance Department', 12000);
INSERT INTO employees VALUES (3, 'Wang Wu', 'Technical Department', 18000);

Generate XML directly from an SQL query

Use getXML with a query string for simple, one-off conversions.

Syntax

DBMS_XMLGEN.GETXML (
    sqlQuery     IN VARCHAR2,
    dtdOrSchema  IN number := NONE)
RETURN CLOB;

Example

SELECT DBMS_XMLGEN.getXML('SELECT * FROM employees') FROM dual;

Output:

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMP_id>1</EMP_id>
  <name>Zhang San</name>
  <dept>Technical Department</dept>
  <SALARY>15000</SALARY>
 </ROW>
 <ROW>
  <EMP_id>2</EMP_id>
  <name>Li Si</name>
  <dept>Finance Department</dept>
  <SALARY>12000</SALARY>
 </ROW>
 <ROW>
  <EMP_id>3</EMP_id>
  <name>Wang Wu</name>
  <dept>Technical Department</dept>
  <SALARY>18000</SALARY>
 </ROW>
</ROWSET>

Generate XML using a context handle

Use the context-based pattern when you need finer control over the lifecycle of resources or want to configure output options.

1. Call newContext with an SQL query to create a context handle.

2. Call getXML with the handle to retrieve XML.

3. Call closeContext to release the handle and free resources.

Syntax

-- Create a context handle
DBMS_XMLGEN.NEWCONTEXT (
    query     IN VARCHAR2)
RETURN ctxHandle;

-- Generate XML from the handle (returns a new CLOB)
DBMS_XMLGEN.GETXML (
    ctx          IN ctxHandle,
    dtdOrSchema  IN number := NONE)
RETURN CLOB;

-- Generate XML from the handle (writes into an existing CLOB variable)
DBMS_XMLGEN.GETXML (
    ctx          IN ctxHandle,
    tmpclob      IN OUT NCOPY CLOB,
    dtdOrSchema  IN number := NONE);

-- Close the context
DBMS_XMLGEN.CLOSECONTEXT (
    ctx  IN ctxHandle);

Example: Return XML as a new CLOB

DECLARE
    xml_result CLOB;
    handle DBMS_XMLGEN.ctxHandle;
BEGIN
    handle := DBMS_XMLGEN.NEWCONTEXT('SELECT * FROM employees');
    DBMS_OUTPUT.PUT_LINE(handle);
    xml_result := DBMS_XMLGEN.GETXML(handle);
    DBMS_OUTPUT.PUT_LINE(xml_result);
END;
/

Example: Write XML into an existing CLOB variable

DECLARE
    xml_result CLOB := '<dummy></dummy>';
    handle DBMS_XMLGEN.ctxHandle;
BEGIN
    handle := DBMS_XMLGEN.NEWCONTEXT('SELECT * FROM employees');
    DBMS_OUTPUT.PUT_LINE(handle);
    DBMS_XMLGEN.GETXML(handle, xml_result);
    DBMS_OUTPUT.PUT_LINE(xml_result);
END;
/

Encode and decode XML special characters

Use convert to escape or unescape special characters in XML strings.

Syntax

-- Input: VARCHAR2
DBMS_XMLGEN.CONVERT (
    xmlData IN VARCHAR2,
    flag    IN NUMBER := ENTITY_ENCODE)
RETURN VARCHAR2;

-- Input: CLOB
DBMS_XMLGEN.CONVERT (
    xmlData IN CLOB,
    flag    IN NUMBER := ENTITY_ENCODE)
RETURN CLOB;

Example

-- Encode special characters
SELECT DBMS_XMLGEN.CONVERT('<>&"''') FROM dual;
-- Returns: &lt;&gt;&amp;&quot;&apos;

-- Decode back to original characters
SELECT DBMS_XMLGEN.CONVERT('&lt;&gt;&amp;&quot;&apos;', 1) FROM dual;
-- Returns: <>&"'

Column name case

In PolarDB, unquoted column names are lowercase by default. In Oracle, they are uppercase by default. This affects the XML tag names generated by getXML.

• If the downstream system accepts lowercase tags, no action is needed.

• If the downstream system requires uppercase tags (for example, /ROWSET/ROW/COLUMN_NAME), set the polar_dbms_xmlgen_upper_column_type_name parameter to ON in the console.

Column aliases for function calls and constants

When a query contains a function call or a constant — for example:

SELECT DBMS_XMLGEN.getXML('SELECT my_function(1) FROM dual') FROM dual;

The column name in the returned XML differs between Oracle and PolarDB:

• Oracle: MY_FUNCTION_x0028_1_x0029_ (Unicode-formatted string)

• PolarDB: my_function

To get consistent behavior, add an explicit alias to the function call:

SELECT DBMS_XMLGEN.getXML('SELECT my_function(1) AS column_name FROM dual') FROM dual;

With an explicit alias, PolarDB and Oracle produce the same column name in the XML output.

Functions that return no rows

If an SQL statement returns 0 rows, getXML returns NULL, which is compatible with Oracle. However, if you wrap a function that raises no_data_found inside a getXML call — for example:

SELECT DBMS_XMLGEN.getXML('SELECT func_return_null FROM dual') FROM dual;

Where func_return_null is defined as:

CREATE OR REPLACE FUNCTION func_return_null RETURN int AS
    result int := 1;
BEGIN
    -- Always returns 0 rows, triggers the no_data_found exception, and directly returns NULL
    SELECT 1 INTO result FROM dual WHERE 1 = 0;
    RETURN result;
END;
/

The result may be incompatible with Oracle. Avoid this pattern.