Re: XMLATTRIBUTE question

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 7 Jan 2007 02:24:25 -0800
Message-ID: <1168165464.981616.266040@42g2000cwt.googlegroups.com>

lizansi_at_gmail.com wrote:
> I am basically trying to get a function to return empty node if the
> attribute is null. The sql that I use is:
>
> select XMLELEMENT ("EmpName",
> XMLATTRIBUTES ("FirstName" || ' ' || "LastName" as
> "Name", "Region" ))
>
> from "Demo"."demo"."Employees";
>
>
> It returns:
>
> <EmpName Name="Nancy Davolio" Region="WA" />
>
> <EmpName Name="Andrew Fuller" Region="WA" />
> <EmpName Name="Janet Leverling" Region="WA" />
> <EmpName Name="Margaret Peacock" Region="WA" />
>
> <EmpName Name="Steven Buchanan" />
> <EmpName Name="Michael Suyama" />
> <EmpName Name="Robert King" />
> <EmpName Name="Laura Callahan" Region="WA" />
>
> <EmpName Name="Anne Dodsworth" />
>
> It produces an 'EmpName' elements with two attributes (if value of the
> column 'Region' is not NULL) or with one attribute (if value of the
> column 'Region' is NULL).
> I want to see both attributes even if one of them are null like:
>
> <EmpName Name="Nancy Davolio" Region="WA" />
>
> <EmpName Name="Andrew Fuller" Region="WA" />
> <EmpName Name="Janet Leverling" Region="WA" />
> <EmpName Name="Margaret Peacock" Region="WA" />
>
> <EmpName Name="Steven Buchanan" Region="" />
> <EmpName Name="Michael Suyama"
> Region="" />
> <EmpName Name="Robert King" Region=""
> />
> <EmpName Name="Laura Callahan" Region="WA" />
> <EmpName Name="Anne Dodsworth"
> Region="" />
>
> Any help will be appreciated.

According to the XMLAttributes clause specification, expressions that evaluate to NULL generate no attributes and this can't be changed. The only way you can work this restriction around is to use NVL() like this:

select XMLELEMENT ("EmpName",

                    XMLATTRIBUTES ("FirstName" || ' ' || "LastName" as

"Name", NVL("Region",'N/A') as "Region" ))

from "Demo"."demo"."Employees";

You can't create an empty attribute this way, because empty strings are NULLs in Oracle, but you can use some special constant value to replace NULLs.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Sun Jan 07 2007 - 04:24:25 CST