Oracle SQL查询使用xpath中的特定条件来检索特定事件? [英] Oracle SQL Query to retrieve specific Event using specific condition in xpath?
问题描述
<List>
<Event>
<eventTime>2016-08-03T15:41:12.000+05:30</eventTime>
<e:PlaceHolder xmlns:e="http://dpmt.element.com">US</e:PlaceHolder>
<e:flag xmlns:e="http://dpmt.rmg.org/pq/rs">true</e:flag>
<e:flag1 xmlns:e="http://dpmt.rmg.org/pq/rs">false</e:flag1>
</Event>
<Event>
<eventTime>2016-08-01T19:41:12.000+05:30</eventTime>
</Event>
</List>
我已经以这种方式存储了XML文档,其中可能包含多个<Event>
标记,使用X-Path查询,我只想获取e:flag值为true的事件,我的情况下,我只需要使用where子句获取第一个事件标签.我需要通过where子句中的条件使用EXTRACT或ExtractValue函数进行泛型查询,以检查其标志值.
I have stored my XML document in such a way , which may contains multiple <Event>
tags, using the X-Path query , I want to fetch only that event , that have e:flag value true, In my case i have to get only first event tag using where clause. I need a generic query using EXTRACT or ExtractValue function by condition in where clause to check its flag value.
我以这种方式编写查询:-
I am writing query in this way :-
选择* FROM(SELECT EXTRACT(doc,'//List').getClobVal()AS doc FROM my_table)T;
SELECT * FROM (SELECT EXTRACT(doc,'//List').getClobVal() AS doc FROM my_table) T;
推荐答案
这将为您提供帮助;标志标记使用空值进行测试.在功能中应添加其他值.
This is gonna be help you; Flag tag is tested with null value. Another values should be added in function.
DECLARE
vs_Xml VARCHAR2(32000):= '<List>
<Event>
<eventTime>2016-08-03T15:41:12.000+05:30</eventTime>
<e:PlaceHolder xmlns:e="http://dpmt.element.com">US</e:PlaceHolder>
<e:flag xmlns:e="http://dpmt.rmg.org/pq/rs">true</e:flag>
<e:flag1 xmlns:e="http://dpmt.rmg.org/pq/rs">false</e:flag1>
</Event>
<Event>
<eventTime>2016-08-01T19:41:12.000+05:30</eventTime>
</Event>
</List>';
vx_ParameterList XMLTYPE;
vx_Parameter XMLTYPE;
vn_ParameterIndex NUMBER;
vs_Key VARCHAR2(64);
vs_XPath VARCHAR2(255);
vs_Value VARCHAR2(10000);
vs_Value2 VARCHAR2(10000);
BEGIN
vx_ParameterList := xmltype(vs_Xml);
vn_ParameterIndex := 1;
vs_XPath := '/List/Event';
WHILE vx_ParameterList.existsNode(vs_XPath || '[' || vn_ParameterIndex || ']') = 1 LOOP
vx_Parameter := vx_ParameterList.extract(vs_XPath || '[' || vn_ParameterIndex || ']');
vs_Value := vx_Parameter.extract('//eventTime/text()').GetStringVal();
SELECT vx_Parameter.extract('//flag/text()', 'xmlns:e="http://dpmt.rmg.org/pq/rs"').GetStringVal() INTO vs_Value2 FROM DUAL; --Null value handling
vn_ParameterIndex := vn_ParameterIndex + 1;
dbms_output.put_line(vs_Value ||'-' || vs_Value2);
END LOOP;
END;
/
这篇关于Oracle SQL查询使用xpath中的特定条件来检索特定事件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!