Re: XMLATTRIBUTE question

From: <lizansi_at_gmail.com>
Date: 7 Jan 2007 07:41:31 -0800
Message-ID: <1168184490.865698.222650@11g2000cwr.googlegroups.com>

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_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 - 09:41:31 CST