SQL & PL/SQL

Hi there,

I just read this thread (1248470
and since I havn't used xmltable before I thought I'd experiment a bit. (I thought I'd post a new thread seeing as this is perhaps a different question.)

I can't get that query to work:

SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select  id,
  2          archive
  3    from  XMLTable(
  4                   '/BSGDocs/Doc'
  5                   passing XMLType(Q'[<BSGDocs><Doc Id='562' Archive='1'></Doc><Doc Id='563' Arch
ive='0'></Doc></BSGDocs>  6                   columns
  7                     id varchar2(5) path '@Id',
  8                     archive varchar2(5) path '@Archive'
  9                  )
 10  / 
                 passing XMLType(Q'[<BSGDocs><Doc Id='562' Archive='1'></Doc><Doc Id='563' Archive='0'></Doc></BSG
                                                                                                    
ERROR at line 5:
ORA-01780: string literal required

I've looked at the documentation (http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb_xquery.htm#CBAJBBDD)

but it hasn't helped much, I still appear to be getting a syntax error of some kind.

I tried to simplify it down a bit for my own purposes:

SQL> create table xfoo as
  2  select xmltype(cursor(select * from dual)) x from dual ;

Table created.

SQL> select  dummy
  2    from xfoo,
  3        XMLTable('/ROWSET/ROW'
  4                   passing x
  5                   columns
  6                     dummy varchar2(5) path '/DUMMY'
  7                  );
                   dummy varchar2(5) path '/DUMMY'
                                                *
ERROR at line 6:
ORA-01780: string literal required

what am I doing wrong?