I am trying to use built-in XML functions, but I'm getting an %s: invalid identifier
error. Selects works fine without the XML functions.
Any ideas where is the mistake?
select id,full_name from users where id in(
with sample_data as
(
(select pm_id
from pm where p_id=30369)
)
select cast(t.column_value.extract('//text()') as varchar2(40)) val
from sample_data,
table( xmlsequence( xmltype(
'<x><x>' || replace(pm_id, '#@#', '</x><x>') || '</x></x>'
).extract('//x/*'))) t)
Best Answer
@elimerv,
Are you sure you are not having something else in place of "single-quote" that is expected there? Also, you don't tell what kind of data does the "pm" table has in the pm_id column (BTW, your latest SQL doesn't appear to be correct as your sample_data subquery has an "id" column whereas your main query refers to "pm_id" column). This is what I get on 11.2.0.1 First, your latest query, as it is (except for tables used):
Now, with correct column name used:
And, when I replaced one of the "single-quotes" with a different (but similar looking) character :
Does this give you any idea? BTW, are you sure you need XML processing to solve your problem ?