| 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); |
Demo Data |
INSERT INTO person_data
(person_id, person_data)
VALUES
(1, XMLTYPE('
<PDRecord>
<PDName>Daniel Morgan</PDName>
<PDDOB>12/1/1951</PDDOB>
<PDEmail>[email protected]</PDEmail>
</PDRecord>')
);
INSERT INTO person_data
(person_id, person_data)
VALUES
(2, XMLTYPE('
<PDRecord>
<PDName>Jack Cline</PDName>
<PDDOB>5/17/1949</PDDOB>
<PDEmail>[email protected]</PDEmail>
</PDRecord>')
);
INSERT INTO person_data
(person_id, person_data)
VALUES
(3, XMLTYPE('
<PDRecord>
<PDName>Caleb Small</PDName>
<PDDOB>1/1/1960</PDDOB>
<PDEmail>[email protected]</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 CONTENT) <returning_column_alias>
FROM <table_name>;
Note: What is within the parentheses is case sensitive, and you cannot 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> |
|
