Built-in XML functions 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(
    '&lt;x&gt;&lt;x&gt;' || replace(pm_id, '#@#', '&lt;/x&gt;&lt;x&gt;') || '&lt;/x&gt;&lt;/x&gt;'
  ).extract('//x/*'))) t)
@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):

with sample_data as (
   select '123#@#' id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('&lt;x&gt;&lt;x&gt;'||replace(pm_id,'#@#','&lt;/x&gt;&lt;x&gt;')||'&lt;/x&gt;&lt;/x&gt;').extract('//x/*'))) t
  )
 10  /
         table(xmlsequence(xmltype('&lt;x&gt;&lt;x&gt;'||replace(pm_id,'#@#','&lt;/x&gt;&lt;x&gt;')||'&lt;/x&gt;&lt;/x&gt;').extract('//x/*'))) t
                                                     *
ERROR at line 8:
ORA-00904: "PM_ID": invalid identifier


Now, with correct column name used:

with sample_data as (
   select '123#@#' pm_id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('&lt;x&gt;&lt;x&gt;'||replace(pm_id,'#@#','&lt;/x&gt;&lt;x&gt;')||'&lt;/x&gt;&lt;/x&gt;').extract('//x/*'))) t
  )
 10  /

no rows selected


And, when I replaced one of the "single-quotes" with a different (but similar looking) character :

with sample_data as (
   select '123#@#' pm_id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('&lt;x&gt;&lt;x&gt;'||replace(pm_id,'#@#','&lt;/x&gt;&lt;x&gt;')||'&lt;/x&gt;&lt;/x&gt;').extract(`//x/*'))) t
  )
 10  /
         table(xmlsequence(xmltype('&lt;x&gt;&lt;x&gt;'||replace(pm_id,'#@#','&lt;/x&gt;&lt;x&gt;')||'&lt;/x&gt;&lt;/x&gt;').extract(`//x/*'))) t
                                                                                                 *
ERROR at line 8:
ORA-00911: invalid character


Does this give you any idea?
BTW, are you sure you need XML processing to solve your problem ?