Re: Dynamic XMLelement generation

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 2 Jun 2005 11:02:43 -0700
Message-ID: <1117735363.589723.30480@g44g2000cwa.googlegroups.com>

Hi there. I believe the problem here is your syntax.

xmlelement(name "DBA_CONSTRAINTS") ??

Don't you really mean one of (from your posting I'm not exactly sure what you want the query resultset to be):

1. XMLELEMENT(TABLE_NAME,"DBA_CONSTRAINTS")
2. XMLELEMENT(TABLENAME,TABLE_NAME)

Regards
Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429

EliR wrote:
> Hi ,
>
> I have a very small question regarding Xmlelement function , can the
> XMLelement tag name can be generated dynamically (base on CASE
> expression / decode).
> I am using the new XSQL capabilities in oracle 9i and i failed to do it
>
>
> This is very simple case , I would like to know the concept :
>
> SQL> select table_name from user_tables;
>
> TABLE_NAME
> ------------------------------
> DBA_CONSTRAINTS
> DBA_INDEXES
> DBA_IND_COLUMNS
> DBA_LOBS
> DBA_SEQUENCES
> DBA_SOURCE
> DBA_TABLES
> DBA_TAB_COLUMNS
> DBA_TRIGGERS
> DBA_VIEWS
> OPCONFIG
> PLAN_TABLE
>
> select CASE WHEN table_name='DBA_CONSTRAINTS' THEN xmlelement(name
> "DBA_LOBS") ELSE xmlelement(name "DBA_CONSTRAINTS") END from
> user_tables;
> And this is what i get
>
> CASEWHENTABLE_NAME='DBA_CONSTRAINTS'THENXMLELEMENT(NAME"DBA_LOBS")ELSEXMLELEMENT(NAME"DBA_CONSTRAINTS")END
> -----------------------------------------------------------------------------------------------------------------------------------
> <DBA_LOBS></DBA_LOBS>
> <DBA_LOBS></DBA_LOBS>
> <DBA_LOBS></DBA_LOBS>
> <DBA_LOBS></DBA_LOBS>
> <DBA_LOBS></DBA_LOBS>
> <DBA_LOBS></DBA_LOBS>
> <DBA_LOBS></DBA_LOBS>
> <DBA_LOBS></DBA_LOBS>
> <DBA_LOBS></DBA_LOBS>
> <DBA_LOBS></DBA_LOBS>
> <DBA_LOBS></DBA_LOBS>
> <DBA_LOBS></DBA_LOBS>
Received on Thu Jun 02 2005 - 13:02:43 CDT