I can't extract data using fn:collection

Question

I am using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

I have existing table for eg.: my_scheme.my_existing_table I can select and see data from this table.

But when I try read data from this table using XMLQuery:

SELECT XMLQuery(
 'for $i in fn:collection("oradb:/my_scheme/my_existing_table")/content/text()
 return $i'
 returning content
)FROM DUAL;

Oracle generates the error:

ORA-00942: table or view does not exist

Maybe someone is missing some rights. Please advise me how to solve this problem.

Answer 1

For example, you have a table T:

SQL> create table t as select level a, 'b' b from dual connect by level<=10;

Table created.

SQL> select * from t;

         A B
---------- -
         1 b
         2 b
         3 b
         4 b
         5 b
         6 b
         7 b
         8 b
         9 b
        10 b

10 rows selected.

Your query should be like this:

SELECT XMLQuery(
 'for $i in fn:collection("oradb:/XTENDER/T")/ROW/A/text()
 return $i'
 returning content
) as res
FROM DUAL;

RES
----------------------------
12345678910

ie fn:collection() parameter should be "oradb:/SCHEMA/TABNAME" and then you should specify /ROW/COLNAME where /ROW before column name is mandatory.

In fact, if that's really what you want, you don't need for:

SELECT XMLQuery('fn:collection("oradb:/XTENDER/T")/ROW/A/text()' returning content) res FROM DUAL;

Though I would concatenate those values with ,, like this:

SELECT
   XMLQuery('
     fn:string-join(
       fn:collection("oradb:/XTENDER/T")/ROW/A/text()
       ,","
       )
    ' returning content) as res 
FROM DUAL;

RES
--------------------------------------------------
1,2,3,4,5,6,7,8,9,10

Or a couple of variants for all columns from T:

SELECT
   XMLQuery('
     fn:string-join(
       fn:collection("oradb:/XTENDER/T")/ROW/*
       ,","
       )
    ' returning content) as res 
FROM DUAL;

RES
--------------------------------------------------
1,b,2,b,3,b,4,b,5,b,6,b,7,b,8,b,9,b,10,b


SELECT
   XMLQuery('
     fn:string-join(
       for $i in fn:collection("oradb:/XTENDER/T")/ROW return string-join($i/*, ",")
       ,"; "
       )
    ' returning content) as res 
FROM DUAL;

RES
--------------------------------------------------
1,b; 2,b; 3,b; 4,b; 5,b; 6,b; 7,b; 8,b; 9,b; 10,b