# XMLTYPE

## Introduced

MariaDB 12.3

## Syntax

```
XMLTYPE
```

* No parameters accepted
* Length specification for `XMLTYPE` (e.g., `XMLTYPE(100)`) results in an error

## Description

`XMLTYPE` is a data type introduced in MariaDB 12.3 for storing XML data. It is designed to:

* Provide convenient storage of XML data
* Ensure compatibility with Oracle databases
* Support future XML validation and processing capabilities

In its initial implementation*,* `XMLTYPE` provides basic XML storage capabilities only*,* without validation or specialized XML-specific functionality*.* When using string functions, the data type is effectively converted to strings and is maintained in temporary tables.

## Characteristics

* Maximum storage capacity: 4GB (same as `LONGBLOB`)
* Compatibility**:** Designed to be compatible with Oracle’s `XMLTYPE`
* Validation**:** XML validation or schema enforcement is not supported
* Length restriction**:** Length cannot be specified.

**Example (invalid usage):**

```
CREATE TABLE t1 (a XMLTYPE(6));
```

`XMLTYPE` does not accept length parameters, unlike data types such as `VARCHAR(255)` or `DECIMAL(10,2)`*.*

## Related Functions

With MariaDB 12.3, the following functions return the `XMLTYPE` data type:

* `UPDATEXML`
* `CAST`

## **Examples**

### Basic Tables Creation

```
CREATE TABLE t1(id INT, x xmltype);
SHOW CREATE TABLE t1;
```

**Output**

```
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `x` xmltype DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### Character Set Specification

```
CREATE TABLE t1(id INT, x xmltype  CHARACTER SET utf8mb3) CHARACTER SET utf8mb4;
SHOW CREATE TABLE t1;
```

**Output**

```
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `x` xmltype CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### Binary Attribute

```
CREATE TABLE t1 (a xmltype binary) CHARACTER SET utf8mb4;
SHOW CREATE TABLE t1;
```

**Output**

```
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` xmltype CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### Basic Data Insertion and Selection

```
CREATE TABLE t1(id INT, x xmltype);
INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
SELECT * FROM t1;
```

**Output**

```
id	x
1	 one
2	 two
3	 three
```

### Restrictions and Error Cases

#### Length Specification Not Permitted

If a length parameter is provided for `XMLTYPE`*,* the system returns an error:

```
CREATE TABLE t1 (a XMLTYPE(6));
```

**Output**

```
ERROR HY000: Data type 'XMLTYPE' doesn't support LENGTH attribute.
```

#### REF\_SYSTEM\_ID Attribute Not Supported

```
CREATE TABLE t1 (a XMLTYPE REF_SYSTEM_ID=4);
```

**Output**

```
ERROR HY000: Data type 'XMLTYPE' doesn't support REF_SYSTEM_ID attribute.

```

#### Length Parameters in Complex Column Definitions

```
CREATE TABLE t1(id INT, x xmltype(10, 2));
```

**Output**

```
ERROR HY000: Data type 'xmltype' doesn't support LENGTH attribute.
```

## See Also

* [CAST](https://mariadb.com/docs/server/reference/sql-functions/string-functions/cast)
* [Data Types](https://mariadb.com/docs/server/reference/data-types)
* [String Functions](https://mariadb.com/docs/server/server-usage/basics/mariadb-string-functions-guide-1)
* [LONGBLOB](https://mariadb.com/docs/server/reference/data-types/string-data-types/longblob)
* [Data Type Storage Requirements](https://mariadb.com/docs/server/reference/data-types/data-type-storage-requirements)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/data-types/string-data-types/xmltype.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.