How to Use XMLTYPE Methods in PL/SQL

XMLTYPE is Oracle's data type for XML, and in PL/SQL it is an object with methods. A variable of type XMLTYPE can report its root element, extract nodes with XPath, test whether a node exists, and transform the document with an XSLT stylesheet, all without leaving PL/SQL.

Code for This Guide

The main examples are in the examples/pkg-xml folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use 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

v_doc := xmltype('xml text');               -- or from a CLOB or a query
v_doc.getrootelement
v_doc.extract(xpath).getstringval           -- or getclobval, getnumberval
v_doc.existsnode(xpath)                     -- 1 or 0
v_doc.transform(xsl_xmltype)

Read and Transform a Document

Example:

declare
  v_doc xmltype := xmltype('<flight no="NM150"><from>DXB</from><to>LHR</to>'
                           || '<seats><seat>12A</seat><seat>12B</seat></seats></flight>');
  v_seat varchar2(10);
begin
  dbms_output.put_line('root:   ' || v_doc.getrootelement);
  dbms_output.put_line('number: ' || v_doc.extract('/flight/@no').getstringval);
  dbms_output.put_line('seats:  ' || v_doc.extract('//seat').getstringval);
  dbms_output.put_line('has to? ' || v_doc.existsnode('/flight/to'));
  select xmlcast(xmlquery('/flight/seats/seat[2]' passing v_doc returning content)
                 as varchar2(10))
  into   v_seat from dual;                           -- XMLQUERY is SQL, not PL/SQL
  dbms_output.put_line('second: ' || v_seat);
  dbms_output.put_line(v_doc.transform(xmltype(
    '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
       <xsl:output method="text"/>
       <xsl:template match="/flight">
         <xsl:value-of select="@no"/>: <xsl:value-of select="from"/>
         <xsl:text> to </xsl:text><xsl:value-of select="to"/>
       </xsl:template>
     </xsl:stylesheet>')).getstringval);
end;
/

Output:

root:   flight
number: NM150
seats:  <seat>12A</seat>
<seat>12B</seat>

has to? 1
second: 12B
NM150: DXB to LHR

PL/SQL procedure successfully completed.

• GETROOTELEMENT returns flight, and EXTRACT with /flight/@no reads the attribute.
• EXTRACT with //seat returns both seat elements as an XML fragment.
• EXISTSNODE returns 1, since the document has a to element.
• XMLQUERY is SQL, so it runs in a SELECT INTO; XMLCAST turns the second seat into a VARCHAR2.
• TRANSFORM applies an XSLT stylesheet that writes one line of text.

Things to Know

• EXTRACT and EXISTSNODE are older methods; in SQL, XMLQUERY, XMLTABLE, and XMLEXISTS are the standard ways.
• GETSTRINGVAL returns VARCHAR2 and fails for long results; use GETCLOBVAL for large documents.
• XPath expressions are case-sensitive, like XML itself.

Related Guides

• Oracle XMLQUERY Function
• Oracle XMLTABLE Function

Conclusion

XMLTYPE methods read, test, and transform XML documents inside PL/SQL. Use EXTRACT, EXISTSNODE, and TRANSFORM on variables, and the SQL functions XMLQUERY, XMLTABLE, and XMLCAST when the work belongs in a query.