Oracle XMLTable
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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;

Related Topics
DBMS_XMLGEN
XML Functions
XMLQuery
XML Tables
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved