CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
kolbasov
kolbasov

Reputation: 1588

Pass the result of table(xmlsequence()) to a function as a parameter

Is there any way to pass a result from table(xmlsequence(extract(xmltype(xml), xpath))) to a function as a parameter?

I have a query:

select
    extractvalue(value(data), '/path/to/element')
from
    table(xmlsequence(extract(xmltype(in_xml), in_path))) data;

What I would like to do is to isolate all XML operations into a couple of functions and have something like this:

select
    get_value(data, '/path/to/element')
from
    get_table(in_xml, in_path) data;

OR at least:

select
    get_value(data, '/path/to/element')
from
    table(xmlsequence(get_table(in_xml, in_path)) data;

There is no problem with moving extract(xmltype(in_xml), in_path) into the get_table function but I have a trouble with passing the result to the get_value.

Any thoughts?

Upvotes: 0

Views: 1016

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

table() is a table collection expression, and it doesn't really make sense to pass an expression (or indeed a table) as a parameter.

The closest I can think off is to pass the intermediate result from xmlsequence(), obtained from a function if you want, directly into get_value():

create or replace function get_xmlseq(in_xml varchar2, in_path varchar2)
return xmlsequencetype
as
  l_xmlseq xmlsequencetype;
begin
  select xmlsequence(extract(xmltype(in_xml), in_path))
  into l_xmlseq
  from dual;

  return l_xmlseq;
end get_xmlseq;
/

create or replace function get_value (in_xmlseq xmlsequencetype, in_path varchar2)
return varchar2
as
  l_value varchar2(32767);
begin
  select extractvalue(value(data), in_path)
  into l_value
  from table(in_xmlseq) data;

  return l_value;
end get_value;
/

And then call it as:

select get_value(get_xmlseq(:in_xml, :in_path), '/path/to/element') from dual;

Which gives the same result as your original query, at least for a very simple example.

But if you're doing that then you might as well have a single function that takes (in_xml, in_path, '/path/to/element') and does the xmlsequence call as well. Or since that's deprecated, that uses xmltable and/or xquery.

Upvotes: 1

Related Questions