XMLType-PolarDB(PolarDB)-阿里云帮助中心

PolarDB for PostgreSQL (Compatible with Oracle) supports the XMLType composite type for storing and manipulating XML data.

Prerequisites

XMLType requires Oracle syntax compatibility 2.0 (minor engine version 2.0.14.17.32.0 or later).

View the minor engine version in the console or by running:

SHOW polardb_version;

If the minor engine version does not meet the requirement, upgrade the minor engine version.

Supported functions

Function	Type	Description
`createXML`	Static function	Creates an `XMLType` object from a CLOB or VARCHAR2 string
`XMLType()`	Constructor	Creates an `XMLType` object from a CLOB or VARCHAR2 string
`getStringVal()`	Member function	Converts an `XMLType` object to a VARCHAR2 string
`getClobVal()`	Member function	Converts an `XMLType` object to a CLOB string
`getNumberVal()`	Member function	Converts an `XMLType` object to a NUMBER value
`extract()`	Member function	Extracts an XML fragment using an XPath expression and returns it as an `XMLType` object

Create an XMLType object

Use either the createXML static function or the XMLType constructor to create an XMLType object from a string.

Function signatures

`createXML` static function:

STATIC FUNCTION createXML(xmlData IN CLOB) RETURN XMLType
STATIC FUNCTION createXML(xmlData IN VARCHAR2) RETURN XMLType
STATIC FUNCTION createXML(xmlData IN CLOB, schema IN VARCHAR2, validated IN NUMBER := 0, wellFormed IN NUMBER := 0) RETURN XMLType
STATIC FUNCTION createXML(xmlData IN VARCHAR2, schema IN VARCHAR2, validated IN NUMBER := 0, wellFormed IN NUMBER := 0) RETURN XMLType

`XMLType` constructor:

CONSTRUCTOR FUNCTION XMLType(xmlData IN CLOB, schema IN VARCHAR2 := NULL, validated IN NUMBER := 0, wellFormed IN NUMBER := 0) RETURN SELF AS RESULT
CONSTRUCTOR FUNCTION XMLType(xmlData IN VARCHAR2, schema IN VARCHAR2 := NULL, validated IN NUMBER := 0, wellFormed IN NUMBER := 0) RETURN SELF AS RESULT

Parameters

Parameter	Description
`xmlData`	The input XML string (CLOB or VARCHAR2).
`schema`	Not supported. Must be NULL.
`validated`	Not supported. Must be 0.
`wellFormed`	Controls the XML format check. If 0 or NULL, `xmlData` must be a valid XML document. Set to any other value to skip the format check.

Examples

Using `createXML`:

DECLARE
    xml_doc XMLType;
BEGIN
    xml_doc := XMLType.createXML('<root><name>Alice</name><age>30</age></root>');
    DBMS_OUTPUT.PUT_LINE(xml_doc.getStringVal());
END;
/

Output:

<root><name>Alice</name><age>30</age></root>

Using the `XMLType` constructor:

DECLARE
    xml_doc XMLType;
BEGIN
    xml_doc := XMLType('<root><name>Alice</name><age>30</age></root>');
    DBMS_OUTPUT.PUT_LINE(xml_doc.getStringVal());
END;
/

Output:

<root><name>Alice</name><age>30</age></root>

Convert XMLType data

Use member functions to convert an XMLType object to a standard Oracle data type.

Function signatures

MEMBER FUNCTION getStringVal() RETURN VARCHAR2
MEMBER FUNCTION getClobVal() RETURN CLOB
MEMBER FUNCTION getNumberVal() RETURN NUMBER

Example

DECLARE
    xml_doc XMLType;
    xml_clob CLOB;
BEGIN
    xml_doc := XMLType('<root><name>Alice</name><age>30</age></root>');
    xml_clob := xml_doc.getClobVal();
    DBMS_OUTPUT.PUT_LINE(xml_clob);
END;
/

Output:

<root><name>Alice</name><age>30</age></root>

Extract XML fragments

The extract member function applies an XPath expression to an XMLType object and returns the matching portion as a new XMLType object.

Function signature

MEMBER FUNCTION extract(xpath IN VARCHAR2) RETURN XMLType

Example

DECLARE
    xml_obj XMLType := XMLType.createXML('<root><name>Alice</name><age>30</age></root>');
    result XMLType;
BEGIN
    result := xml_obj.extract('/root/name');
    DBMS_OUTPUT.PUT_LINE(result.getStringVal()); -- Output: <name>Alice</name>
END;
/

Output:

<name>Alice</name>