Oracle XMLTABLE Syntax and Demos

Oracle XMLTable Version 11.2.0.3

General Information
Note: XMLQuery returns query results as XML. XMLTable returns results as relational data.

Create Schema
Create Demo Table	conn uwclass/uwclass 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; SELECT * FROM person_data; 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;