Oracle XQuery 过滤XML查询SQL

Oralce 支持SQL XQuery查询

一个简单示例：

SELECT XMLQuery('for $i in /Videogame return $i/Type' 
passing by value X
RETURNING CONTENT) 
FROM (SELECT XMLTYPE('<Videogame><Type>Racing</Type><Name>NFS Most Wanted</Name><Version>2.0</Version><Size>5.5 GB</Size></Videogame>') as X 
      FROM dual) a;

工作实例（忽略）：

select a.workflowcode,b.displayname
from
(select a.workflowcode, XMLQuery(
    'for $i in /Workflow/Activities
     where $i /ApproveActivity/ParticipateMethod = "Serial" and $i/ApproveActivity/ParticipateType="MultiParticipants"
     return $i/ApproveActivity'
    passing by value contentx
    RETURNING CONTENT) XMLData
from(select a.workflowcode,a.content as contentx
     from
     (SELECT a.*,Row_Number() OVER (partition by a.BizObjectSchemacode ORDER BY a.workflowversion desc) versionNumb 
      FROM OT_WorkflowTemplatePublished a)  a
where a.versionNumb=1) a
) a
left outer join Ot_Functionnode b on a.workflowcode=b.Code
where a.XMLData is not null

说明：

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>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;

SELECT

Simple Query 

语法：

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.


示例：
(with 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 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>

 

参考：http://psoug.org/reference/xmlquery.html