8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 10g » Here

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...

Back to the Top.