Oracle XMLSERIALIZE Function

XML moves between two forms in Oracle: XMLType values that SQL can query, and text that files, services, and people read. XMLSERIALIZE turns XML into text, with control over the data type and indentation. Its partner XMLPARSE turns text into XMLType, checking that it is well formed.

Code for This Guide

The main examples are in the examples/xml folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

xmlserialize({document | content} xml_expr [as type] [indent [size = n] | no indent])
xmlparse({document | content} string [wellformed])

Serialize Generated XML

Every generating example ends with XMLSERIALIZE, as this one does, with INDENT SIZE = 2 for readability.

Example:

select xmlserialize(content
         xmlelement("airport",
           xmlattributes(airport_code as "code", is_hub as "hub"),
           xmlelement("city", city),
           xmlforest(time_zone as "timeZone", elevation_ft as "elevation"))
         indent size = 2) as airport_xml
from   airports
where  airport_code = 'DXB';

Output:

AIRPORT_XML
____________________________________
<airport code="DXB" hub="TRUE">
  <city>Dubai</city>
  <timeZone>Asia/Dubai</timeZone>
  <elevation>62</elevation>
</airport>

Indentation and Type

Example:

select xmlserialize(content xmltype('<a><b>1</b></a>') no indent)          as no_indent,
       xmlserialize(content xmltype('<a><b>1</b></a>') as varchar2(30))   as as_varchar2
from   dual;

select xmlserialize(content xmltype('<a><b>1</b><c>2</c></a>') indent size = 4) as indent_4
from   dual;

Output:

NO_INDENT          AS_VARCHAR2
__________________ __________________
<a><b>1</b></a>    <a><b>1</b></a>

INDENT_4
_______________
<a>
    <b>1</b>
    <c>2</c>
</a>

NO INDENT gives compact text for transmission, AS VARCHAR2 a string you can compare or concatenate, and INDENT SIZE = 4 a layout for people.

Parse Text with XMLPARSE

XMLPARSE DOCUMENT requires a single root element; a fragment with two roots fails:

Example:

select xmlparse(document '<a>1</a><b>2</b>') as two_roots from dual;

Output:

Error starting at line : 1
In command -
select xmlparse(document '<a>1</a><b>2</b>') as two_roots from dual
Error at Command Line : 1 Column : 64
Error report -
SQL Error: ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00245: extra data after end of document

XMLPARSE CONTENT accepts the same text as a fragment:

Example:

select xmlserialize(content xmlparse(content '<a>1</a><b>2</b>')) as fragment from dual;

Output:

FRAGMENT
___________________
<a>1</a><b>2</b>

WELLFORMED skips the check when you know the text is valid, which saves time on large trusted documents.

Related Guides

• Oracle XMLELEMENT Function
• Oracle XMLCAST Function
• Oracle JSON_SERIALIZE Function

Conclusion

XMLSERIALIZE converts XML into text of the type you choose, compact or indented, and XMLPARSE converts text into XMLType, as a single-rooted DOCUMENT or a CONTENT fragment. Together they move XML between the forms SQL queries and the forms other systems exchange.