Oracle XMLQuery

Oracle XMLQuery Version 11.2.0.3

General Information

Note: XMLQuery returns query results as XML. XMLTable returns results as relation data.

Demo Setup

Table DDL

CREATE TABLE person_data (
person_id NUMBER(3),
person_data XMLTYPE);

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>Taner Poder</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;

col person_data format a50

SELECT * FROM person_data;

set long 100000

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.

SELECT person_id, XMLQuery('for $i in /PDRecord
where $i /PDName = "Daniel Morgan"
order by $i/PDName
return $i/PDName'
passing by value person_data
RETURNING CONTENT) XMLData
FROM person_data;

SELECT person_id, XMLQuery (
'for $i in /PDRecord
where $i /PDName eq "Daniel Morgan"
order by $i/PDName
return $i/PDName'
passing by value person_data
RETURNING CONTENT) XMLData
FROM person_data;

Greater Than

SELECT person_id, XMLQuery (
'for $i in /PDRecord
where $i /PDName > "Daniel Morgan"
order by $i/PDName
return $i/PDName'
passing by value person_data
RETURNING CONTENT) XMLData
FROM person_data;

SELECT person_id, XMLQuery (
'for $i in /PDRecord
where $i /PDName gt "Daniel Morgan"
order by $i/PDName
return $i/PDName'
passing by value person_data
RETURNING CONTENT) XMLData
FROM person_data;

Greater Than Or Equal To

SELECT person_id, XMLQuery (
'for $i in /PDRecord
where $i /PDName >= "Daniel Morgan"
order by $i/PDName
return $i/PDName'
passing by value person_data
RETURNING CONTENT) XMLData
FROM person_data;

SELECT person_id, XMLQuery (
'for $i in /PDRecord
where $i /PDName ge "Daniel Morgan"
order by $i/PDName
return $i/PDName'
passing by value person_data
RETURNING CONTENT) XMLData
FROM person_data;

Less Than

SELECT person_id, XMLQuery (
'for $i in /PDRecord
where $i /PDName < "Daniel Morgan"
order by $i/PDName
return $i/PDName'
passing by value person_data
RETURNING CONTENT) XMLData
FROM person_data;

SELECT person_id, XMLQuery (
'for $i in /PDRecord
where $i /PDName lt "Daniel Morgan"
order by $i/PDName
return $i/PDName'
passing by value person_data
RETURNING CONTENT) XMLData
FROM person_data;

Less Than Or Equal To

SELECT person_id, XMLQuery (
'for $i in /PDRecord
where $i /PDName >= "Daniel Morgan"
order by $i/PDName
return $i/PDName'
passing by value person_data
RETURNING CONTENT) XMLData
FROM person_data;

SELECT person_id, XMLQuery (
'for $i in /PDRecord
where $i /PDName le "Daniel Morgan"
order by $i/PDName
return $i/PDName'
passing by value person_data
RETURNING CONTENT) XMLData
FROM person_data;

Not Equals

SELECT person_id, XMLQuery (
'for $i in /PDRecord
where $i /PDName != "Daniel Morgan"
order by $i/PDName
return $i/PDName'
passing by value person_data
RETURNING CONTENT) XMLData
FROM person_data;

SELECT person_id, XMLQuery (
'for $i in /PDRecord
where $i /PDName ne "Daniel Morgan"
order by $i/PDName
return $i/PDName'
passing by value person_data
RETURNING CONTENT) XMLData
FROM person_data;

Additional Syntax Elements

[	//	div	union	<CastAs>
-	>>	*	intersect	<CastableAs>
+	and	idiv		<EOF>
\|	or	mod		<InstanceOf>
except	is	to		<TreatAs>