XMLATTRIBUTE question

From: <lizansi_at_gmail.com>
Date: 6 Jan 2007 18:51:43 -0800
Message-ID: <1168138303.753431.11960@v33g2000cwv.googlegroups.com>

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. Received on Sat Jan 06 2007 - 20:51:43 CST