XMLType - PolarDB - Alibaba Cloud Documentation Center

PolarDB for PostgreSQL (Compatible with Oracle) supports the XMLType composite type for storing and manipulating XML data. This topic describes its definition, features, and common operations.

Prerequisites

XMLType is supported in the following versions of PolarDB for PostgreSQL (Compatible with Oracle):

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

Note

You can view the minor engine version in the console or by running the SHOW polardb_version; statement. If the minor engine version does not meet the requirements, upgrade the minor engine version.

Static functions and constructors

Static function createXML: This function creates an XMLType object from a string. The function prototypes are as follows:

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

Constructor XMLType: Similar to the createXML static function, this constructor creates an XMLType object from a string. The function prototypes are as follows:

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

Note

The following describes the parameters in the preceding function prototypes:

The xmlData parameter is the input XML string.
The schema parameter is not supported and must be NULL.
The validated parameter is not supported and must be 0.
The wellFormed parameter controls the XML format check. If wellFormed is 0 or NULL, xmlData must be a valid XML document. If wellFormed is set to any other value, the XML format check is skipped.

Examples

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

The following result is returned:

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

Constructor XMLType

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

The following result is returned:

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

Type conversion functions

The XMLType composite type provides several functions to convert XMLType data to other data types. The return value is of the target data type. The functions are as follows:

getStringVal: Converts XMLType data to a VARCHAR2 string.
```
MEMBER FUNCTION getStringVal() RETURN VARCHAR2
```
getClobVal: Converts XMLType data to a CLOB string.
```
MEMBER FUNCTION getClobVal() RETURN CLOB
```
getNumberVal: Converts XMLType data to a NUMBER value.
```
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;
/

The following result is returned:

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

The extract function

The extract function extracts a portion of data from an XML document and returns it as an XMLType object. The function prototype is as follows:

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()); -- Outputs: <name>Alice</name>
END;
/

The following result is returned:

<name>Alice</name>