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屋！