Re: Dynamic XMLelement generation

From: Lewis C <lewisc_at_excite.com>
Date: Thu, 02 Jun 2005 23:53:20 GMT
Message-ID: <8g6v919lineouhobj4j0qnjdhjsb5aeuus@4ax.com>

On 2 Jun 2005 07:25:19 -0700, "EliR" <elire_at_amdocs.com> 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>

I honestly am not sure what you're looking for eactly in your query but you asked if you can have dynamic XMLElement generation.

By that, I'm assuming you mean dynamic tags in the output. If so then yes it is possible. Here is an example using the SCOTT sample user.

SELECT

     CASE table_name
       WHEN 'EMP' THEN
         xmlelement("PrivateInfo", table_name || '')
       WHEN 'BONUS' THEN
         xmlelement("PrivateInfo", table_name )
       ELSE
         xmlelement("PublicInfo", table_name  )
       END xml_frag

FROM USER_TABLES
ORDER BY table_name
/

XML_FRAG

<PrivateInfo>BONUS</PrivateInfo>
<PublicInfo>DEPT</PublicInfo>
<PrivateInfo>EMP</PrivateInfo>
<PublicInfo>EMP_AUD</PublicInfo>
<PublicInfo>ORDERS</PublicInfo>
<PublicInfo>PRODUCT_GROUPS</PublicInfo>
<PublicInfo>SALGRADE</PublicInfo>

7 rows selected.

I'm not sure where this feature would be useful and it seems a little buggy to me. Notice the ||''. I'm researching that. I'm not sure if it's an artifact of the CASE or the XMLElement or a combination. Fascinating. (I'm making Spock eyebrows right now).

BTW, I'm running 10g. Not 9i.

Hope that helps,

Lewis

Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database http://www.suite101.com/welcome.cfm/oracle

Received on Thu Jun 02 2005 - 18:53:20 CDT