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