CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle XMLTable
Version 11.1
 
General
Note: XMLQuery returns query results as XML. XMLTable returns results as relation data.
 
Demo Tables CREATE TABLE person_data (
person_id   NUMBER(3),
person_data XMLTYPE);

desc person_data

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>Jack Cline</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;
 
SELECT

Simple 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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [70 users online]    © 2010 psoug.org