Oracle XQuery 过滤XML查询SQL
2024-10-19 12:41:20
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
最新文章
- Linux(一)__入门介绍
- Ubuntu16.04 802.1x 有线连接 输入账号密码，为什么连接不上？
- 零配置简单搭建SpringMVC 项目
- nginx反向代理docker registry报”blob upload unknown"解决办法
- hadoop2.6---常用命令
- VC++ 比较两个字符串是否相等,字母大小写相关。
- C# 链接Sql和Access数据库语句
- c++基础语法 构造函数 析构函数 类的组合
- C51的一些误区和注意事项
- ui的设计原则
- 【DP专辑】ACM动态规划总结
- php 编程效率（2）
- Xunit和Nunit的区别
- JVM学习02:GC垃圾回收和内存分配
- git创建分支并上传仓库
- Backbone hello world
- ElasticSearch的matchQuery与termQuery区别
- 3.7 C++派生类构造函数调用规则
- jvm相关知识点
- node.js second day