8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
XMLSERIALIZE
The XMLSERIALIZE
function was introduced in Oracle 10.1 to create a string or LOB based on the XML passed to it.
Related articles
Setup
Create a test table to hold XMLTYPE
data.
-- drop table xmldocs purge; create table xmldocs ( id number, xml_data xmltype, constraint xmldocs_pk primary key (id) );
Populate the table with a full XML document, and three XML fragments.
begin -- Full document. insert into xmldocs (id, xml_data) values (1, xmltype( '<employees> <employee><works_number>7782</works_number><name>CLARK</name></employee> <employee><works_number>7839</works_number><name>KING</name></employee> <employee><works_number>7934</works_number><name>MILLER</name></employee> </employees>')); -- Three fragments. insert into xmldocs (id, xml_data) values (2, xmltype('<employee><works_number>7782</works_number><name>CLARK</name></employee>')); insert into xmldocs (id, xml_data) values (3, xmltype('<employee><works_number>7839</works_number><name>KING</name></employee>')); insert into xmldocs (id, xml_data) values (4, xmltype('<employee><works_number>7934</works_number><name>MILLER</name></employee>')); commit; end; /
We need to set the following SQLcl/SQL*Plus setting to display the output of the following queries properly.
set long 1000000
Usage
The XMLSERIALIZE
function was introduced in Oracle 10.1, but has been extended in subsequent versions. It currently has the following usage.
XMLSERIALIZE ( { DOCUMENT | CONTENT } value_expr [ AS datatype ] [ ENCODING xml_encoding_spec ] [ VERSION string_literal ] [ NO INDENT | { INDENT [SIZE = number] } ] [ { HIDE | SHOW } DEFAULTS ] )
The XMLSERIALIZE
function creates a string or LOB based on the XML passed as the value_expr
.
Document
The DOCUMENT
keyword can only be used if the XML is a valid XML document. The following example creates a CLOB from the XML document.
select xmlserialize(document xml_data as clob) from xmldocs where id = 1; XMLSERIALIZE(DOCUMENTXML_DATAASCLOB) -------------------------------------------------------------------------------- <employees> <employee> <works_number>7782</works_number> <name>CLARK</name> </employee> <employee> <works_number>7839</works_number> <name>KING</name> </employee> <employee> <works_number>7934</works_number> <name>MILLER</name> </employee> </employees> SQL>
Adding NO INDENT
removes additional whitespace.
select xmlserialize(document xml_data as clob no indent) from xmldocs where id = 1; XMLSERIALIZE(DOCUMENTXML_DATAASCLOBNOINDENT) -------------------------------------------------------------------------------- <employees><employee><works_number>7782</works_number><name>CLARK</name></employ ee><employee><works_number>7839</works_number><name>KING</name></employee><emplo yee><works_number>7934</works_number><name>MILLER</name></employee></employees> SQL>
Using INDENT SIZE=n
allows us to specify the depth of the indent.
select xmlserialize(document xml_data as clob indent size=4) from xmldocs where id = 1; XMLSERIALIZE(DOCUMENTXML_DATAASCLOBINDENTSIZE=4) -------------------------------------------------------------------------------- <employees> <employee> <works_number>7782</works_number> <name>CLARK</name> </employee> <employee> <works_number>7839</works_number> <name>KING</name> </employee> <employee> <works_number>7934</works_number> <name>MILLER</name> </employee> </employees> SQL>
Content
The CONTENT
keyword can be used for collections of XML fragments that don't represent a valid XML document. The following query returns three XML fragments as a single document, but it is not a valid XML document.
select xmlagg(xml_data) from xmldocs where id in (2,3,4); XMLAGG(XML_DATA) -------------------------------------------------------------------------------- <employee> <works_number>7782</works_number> <name>CLARK</name> </employee><employee> <works_number>7839</works_number> <name>KING</name> </employee><employee> <works_number>7934</works_number> <name>MILLER</name> </employee> SQL>
If we try to use the DOCUMENT
keyword this will fail, because this is not a valid XML document.
select xmlserialize(document xmlagg(xml_data) as clob) from xmldocs where id in (2,3,4); Error report - ORA-19012: Cannot convert XML fragment to the required datatype SQL>
Instead we use the CONTENT
keyword.
select xmlserialize(content xmlagg(xml_data) as clob) from xmldocs where id in (2,3,4); XMLSERIALIZE(CONTENTXMLAGG(XML_DATA)ASCLOB) -------------------------------------------------------------------------------- <employee> <works_number>7782</works_number> <name>CLARK</name> </employee><employee> <works_number>7839</works_number> <name>KING</name> </employee><employee> <works_number>7934</works_number> <name>MILLER</name> </employee> SQL>
Adding NO INDENT
removes additional whitespace.
select xmlserialize(content xmlagg(xml_data) as clob no indent) from xmldocs where id in (2,3,4); XMLSERIALIZE(CONTENTXMLAGG(XML_DATA)ASCLOBNOINDENT) -------------------------------------------------------------------------------- <employee><works_number>7782</works_number><name>CLARK</name></employee><employe e><works_number>7839</works_number><name>KING</name></employee><employee><works_ number>7934</works_number><name>MILLER</name></employee> SQL>
Using INDENT SIZE=n
allows us to specify the depth of the indent.
select xmlserialize(content xmlagg(xml_data) as clob indent size=4) from xmldocs where id in (2,3,4); XMLSERIALIZE(CONTENTXMLAGG(XML_DATA)ASCLOBINDENTSIZE=4) -------------------------------------------------------------------------------- <employee> <works_number>7782</works_number> <name>CLARK</name> </employee><employee> <works_number>7839</works_number> <name>KING</name> </employee><employee> <works_number>7934</works_number> <name>MILLER</name> </employee> SQL>
For more information see:
Hope this helps. Regards Tim...