Oracle | XMLTABLE

Oracle XMLTable Version 20c

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose XMLQuery returns query results as XML. XMLTable returns results as relational data.

Create Schema

Create Demo Table conn uwclass/uwclass@pdbdev CREATE TABLE person_data ( person_id NUMBER(3), person_data sys.XMLTYPE); desc person_data

Load Demo Data INSERT INTO person_data (person_id, person_data) VALUES (1, XMLTYPE(' <PDRecord> <PDName>Daniel Morgan</PDName> <PDDOB>12/1/1951</PDDOB> <PDEmail>damorgan@u.washington.edu</PDEmail> </PDRecord>') ); INSERT INTO person_data (person_id, person_data) VALUES (2, XMLTYPE(' <PDRecord> <PDName>Julian Dyke</PDName> <PDDOB>5/17/1949</PDDOB> <PDEmail>damorgan@u.washington.edu</PDEmail> </PDRecord>') ); INSERT INTO person_data (person_id, person_data) VALUES (3, XMLTYPE(' <PDRecord> <PDName>Caleb Small</PDName> <PDDOB>1/1/1960</PDDOB> <PDEmail>damorgan@u.washington.edu</PDEmail> </PDRecord>') ); COMMIT; set long 100000 col person_data format a60 SELECT * FROM person_data;

Simple XMLTABLE Query (with equals) SELECT <column_list>, XMLQuery ( 'for $i IN <record_end_tag> where $i<item_end_tag> = <value> order by $i<item_end_tag> return $i<item_end_tag> PASSING BY VALUE <xml_record_column> RETURNING CONTENTS) <returning_column_alias> FROM <table_name>;

Note: What is within the parentheses is case sensitive and you can not use Upper Case or InitCap for commands.

col column_value format a50

      

      SELECT t.column_value

      FROM person_data a,

        XMLTABLE (

          'for $root in $date

           where $root/PDRecord/PDName = "Daniel Morgan"

           return $root/PDRecord/PDDOB/text()'

        passing a.person_data AS "date") t;