Re: XMLATTRIBUTE question

Thanks a lot for your input. If I have Region with a constant, the
client has Region to be null than again the xml file will be invalid.
Is there any way of removing that constant and replacing it with null
values using xslt?

I really appreciate your help.

Vladimir M. Zakharychev wrote:
lizansi@xxxxxxxxx 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

.