PolarDB:DBMS_XMLGEN

Prepare data

The examples in the following sections are based on the employees table schema and data. To test the examples, 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 the getXML function to directly convert a query to XML.

Function

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

Parameters

• sqlQuery: The specified SQL statement.

• dtdOrSchema: This parameter is not supported.

Example

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

The following result is returned:

<?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 based on a context handle

For more complex logic, you can perform the operation in steps: create a context, generate XML, and then close the context.

1. Create a context: Use the newContext function to create a context handle based on an SQL query.

2. Retrieve XML: Pass the handle to the getXML function or procedure to retrieve the XML result.

3. Close the context: Use the closeContext function to release the handle and related resources.

Function

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

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

-- Obtain XML from the handle (fills 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

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;
/

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;
/

Escape special XML characters

Use the convert function to encode or decode XML entities in a string to handle special characters.

Function

-- The input parameter is of the VARCHAR2 type
DBMS_XMLGEN.CONVERT (
    xmlData IN VARCHAR2,
    flag    IN NUMBER := ENTITY_ENCODE)
RETURN VARCHAR2;

-- The input parameter is of the CLOB type
DBMS_XMLGEN.CONVERT (
    xmlData IN CLOB,
    flag    IN NUMBER := ENTITY_ENCODE)
RETURN CLOB;

Parameters

• xmlData: The string to be processed.

• flag: The conversion pattern.

  • ENTITY_ENCODE or 0 (default): Encodes special characters.

  • ENTITY_DECODE or 1: Decodes special characters.

Example

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

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