Xquery Error due to Upper/Lower Case

TrojanSpirit

Hello,

When I am writing my Xquery like following, it works !

Select RW.*
from ULQUEUE UL, XMLTABLE('//Row' PASSING UL.TEXT
COLUMNS
*"EVENT" CHAR(10) PATH 'Event',*
*"REASONCODE" CHAR(10) PATH 'Reason_Code',*
*"DESCRIPTION" CHAR(10) PATH 'Description'*
*) AS RW*
where
*UL.ID = '[Param.1]' and*
*UL.QUEUENAME = '[Param.2]' and*
*RW.EVENT = '[Param.3]' and*
*RW.REASONCODE = '[Param.4]'*

But I need to have Headers same as XML Node. SO when I write following query, it does not work :

Select RW.*
from ULQUEUE UL, XMLTABLE('//Row' PASSING UL.TEXT
COLUMNS
*"Event" CHAR(10) PATH 'Event',*
*"Reason_Code" CHAR(10) PATH 'Reason_Code',*
*"Description" CHAR(10) PATH 'Description'*
*) AS RW*
where
*UL.ID = '[Param.1]' and*
*UL.QUEUENAME = '[Param.2]' and*
*RW.Event = '[Param.3]' and*
*RW.Reason_Code = '[Param.4]'*

It gives me this error: java.sql.SQLSyntaxErrorException: ORA-00904: "RW"."REASON_CODE": invalid identifier

If I remove RW.Reason_Code = '[Param.4]' from where clause, it gives me this error: java.sql.SQLSyntaxErrorException: ORA-00904: "RW"."EVENT": invalid identifier

So just want to know, why in UPPERCASE the query works but in camel or Lowercase it does not.

What should I do to make columnheaders same as XML Nodes?

Edited by: 967327 on Oct 30, 2012 11:33 PM

odie_63

Hi,

The error has nothing to do with XQuery or XML.

In Oracle, identifiers are case-insensitive unless they are "quoted".
If you decide to use quoted identifiers, then you have to use them like this all the way down :

SELECT RW.*
FROM ulqueue ul
   , XMLTABLE('//Row' PASSING UL.TEXT
       COLUMNS
         "Event"        CHAR(10) PATH 'Event'
       , "Reason_Code"  CHAR(10) PATH 'Reason_Code'
       , "Description"  CHAR(10) PATH 'Description'
     ) as rw
where ul.id = '[Param.1]' 
and ul.queuename = '[Param.2]' 
and rw."Event" = '[Param.3]' 
and rw."Reason_Code" = '[Param.4]'
;

What should I do to make columnheaders same as XML Nodes?

You cannot, the SQL projection has to be known at parse time, in other words column names must be hardcoded in the query.

odie_63

Hi,

The error has nothing to do with XQuery or XML.

In Oracle, identifiers are case-insensitive unless they are "quoted".
If you decide to use quoted identifiers, then you have to use them like this all the way down :

SELECT RW.*
FROM ulqueue ul
   , XMLTABLE('//Row' PASSING UL.TEXT
       COLUMNS
         "Event"        CHAR(10) PATH 'Event'
       , "Reason_Code"  CHAR(10) PATH 'Reason_Code'
       , "Description"  CHAR(10) PATH 'Description'
     ) as rw
where ul.id = '[Param.1]' 
and ul.queuename = '[Param.2]' 
and rw."Event" = '[Param.3]' 
and rw."Reason_Code" = '[Param.4]'
;

What should I do to make columnheaders same as XML Nodes?

You cannot, the SQL projection has to be known at parse time, in other words column names must be hardcoded in the query.

TrojanSpirit

Thanks a lot.

Yes, writing a query with

*and rw."Event" = '[Param.3]'*
*and rw."Reason_Code" = '[Param.4]'*

does solve my problem.