Oracle | XMLTABLE

Oracle XMLTable Version 20c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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;