sql - Extract XML using PLSQL based upon child element value -

i have following xml document stored in clob variable within plsql procedure:

 <soap:envelope>  <soap:body>  <ns2:getallissuehistoriesresponse>      <return>         <eventdate>2013-08-02t11:45:58.013+02:00</eventdate>         <eventtext>test</eventtext>         <issueeventtype>            <id>10</id>            <value>replied</value>         </issueeventtype>      </return>      <return>...</return>      <return>...</return>  </ns2:getallissuehistoriesresponse>  </soap:body>  </soap:envelope> 

i trying extract return nodes have , issueeventtype value of 10.

i have used following code in attempt achieve p_xml_content clob containing xml:

 l_xml_parser := '<?xml version="1.0" encoding="utf-8"?><results>';  l_xml_parser := l_xml_parser ||(xmltype(p_xml_content).extract('//return/issueeventtype/[id=10]').getclobval());  l_xml_parser := l_xml_parser ||'</results>'; 

this returns nothing despite there being issueeventtype value of 10 within xml document.

i've tried multiple variations such as:

  l_xml_parser := l_xml_parser ||(xmltype(p_xml_content).extract('//return/issueeventtype[id=''10'']').getclobval()); 

and

  l_xml_parser := l_xml_parser ||(xmltype(p_xml_content).extract('//return/issueeventtype/[@id=''10'']').getclobval()); 

but still nothing returned.

any ideas?

i think need little more careful xml namespaces.

the xml fragment you've posted isn't well-formed, doesn't have namespace binding soap nor ns2 namespaces. let's add them in , see happens:

sql> set serveroutput on sql> sql> declare   2      p_xml_content   varchar2(32767) := '<soap:envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns2="http://www.your-url.com/">   3   <soap:body>   4   <ns2:getallissuehistoriesresponse>   5       <return>   6          <eventdate>2013-08-02t11:45:58.013+02:00</eventdate>   7          <eventtext>test</eventtext>   8          <issueeventtype>   9             <id>10</id>  10             <value>replied</value>  11          </issueeventtype>  12       </return>  13       <return>...</return>  14       <return>...</return>  15   </ns2:getallissuehistoriesresponse>  16   </soap:body>  17   </soap:envelope>';  18  19   l_xml_parser       varchar2(32767);  20  begin  21   l_xml_parser := '<?xml version="1.0" encoding="utf-8"?><results>';  22   l_xml_parser := l_xml_parser ||(xmltype(p_xml_content).extract('//return/issueeventtype[id=10]').getclobval());  23   l_xml_parser := l_xml_parser ||'</results>';  24  25   dbms_output.put_line(l_xml_parser);  26  27  end;  28  / <?xml version="1.0" encoding="utf-8"?><results><issueeventtype>   <id>10</id>  <value>replied</value> </issueeventtype> </results>  pl/sql procedure completed. 

it seemed work. got output. how come you're not getting any?

i'm afraid i'm going have guess here. given you've omitted 2 namespace bindings xml document, i'm going assume you've omitted @ least 1 more, , amongst you've omitted 1 binds default namespace prefix non-empty url. (in other words, xml document has attribute looks xmlns="...".) happens if add namespace binding xml document?

sql> declare   2      p_xml_content   varchar2(32767) := '<soap:envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns2="http://www.your-url.com/" xmlns="http://www.example.com/">   3   <soap:body>   4   <ns2:getallissuehistoriesresponse>   5       <return>   6          <eventdate>2013-08-02t11:45:58.013+02:00</eventdate>   7          <eventtext>test</eventtext>   8          <issueeventtype>   9             <id>10</id>  10             <value>replied</value>  11          </issueeventtype>  12       </return>  13       <return>...</return>  14       <return>...</return>  15   </ns2:getallissuehistoriesresponse>  16   </soap:body>  17   </soap:envelope>';  18  19   l_xml_parser       varchar2(32767);  20  begin  21   l_xml_parser := '<?xml version="1.0" encoding="utf-8"?><results>';  22   l_xml_parser := l_xml_parser ||(xmltype(p_xml_content).extract('//return/issueeventtype[id=10]').getclobval());  23   l_xml_parser := l_xml_parser ||'</results>';  24  25   dbms_output.put_line(l_xml_parser);  26  27  end;  28  / declare * error @ line 1: ora-30625: method dispatch on null self argument disallowed ora-06512: @ line 22 

clearly didn't work.

what happened here call extract returned null. can't call getclobval() on null, , that's gives ora-30625 error. call extract returned null because nothing matched xpath expression.

an unqualified (i.e. unprefixed) name in xpath expression always refers name in 'default' namespace. have 3 such names, return, issueeventtype , id. in xpath expression, these names in namespace uri empty string. in xml document, have elements names return, issueeventtype , id in namespace uri http://www.example.com/. because namespace uris differ, elements not considered match.

the fix problem bind prefix same uri have default prefix bound to, , use prefix in xpath expression. extract method takes optional second parameter specifies additional prefixes bind namespaces, , format same 'attributes' specify bind prefix namespace in xml. instead of writing

    l_xml_parser := l_xml_parser ||(xmltype(p_xml_content).extract('//return/issueeventtype[id=10]').getclobval()); 

write

    l_xml_parser := l_xml_parser ||(xmltype(p_xml_content).extract('//e:return/e:issueeventtype[e:id=10]', 'xmlns:e="http://www.example.com/"').getclobval()); 

here, bind prefix e same uri bound default prefix, , qualify names return, issueeventtype , id prefix. can use different prefix if prefer, long use consistently.

lo , behold, works:

sql> declare   2      p_xml_content   varchar2(32767) := '<soap:envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns2="http://www.your-url.com/" xmlns="http://www.example.com/">   3   <soap:body>   4   <ns2:getallissuehistoriesresponse>   5       <return>   6          <eventdate>2013-08-02t11:45:58.013+02:00</eventdate>   7          <eventtext>test</eventtext>   8          <issueeventtype>   9             <id>10</id>  10             <value>replied</value>  11          </issueeventtype>  12       </return>  13       <return>...</return>  14       <return>...</return>  15   </ns2:getallissuehistoriesresponse>  16   </soap:body>  17   </soap:envelope>';  18  19   l_xml_parser       varchar2(32767);  20  begin  21   l_xml_parser := '<?xml version="1.0" encoding="utf-8"?><results>';  22   l_xml_parser := l_xml_parser ||(xmltype(p_xml_content).extract('//e:return/e:issueeventtype[e:id=10]', 'xmlns:e="http://www.example.com/"').getclobval());  23   l_xml_parser := l_xml_parser ||'</results>';  24  25   dbms_output.put_line(l_xml_parser);  26  27  end;  28  / <?xml version="1.0" encoding="utf-8"?><results><issueeventtype xmlns="http://www.example.com/">   <id>10</id>  <value>replied</value> </issueeventtype> </results>  pl/sql procedure completed.