Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to get NULL value columns in XML

5 posts in General Discussion Last posting was on 2008-05-27 06:55:13.0Z
Frank Vestjens <""F V e s t j e n s\" Posted on 2008-05-22 17:02:14.0Z
From: Frank Vestjens <""F V e s t j e n s\"@ i n f o r i t.n l">
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: How to get NULL value columns in XML
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4835a716$1@forums-1-dub>
Date: 22 May 2008 10:02:14 -0700
X-Trace: forums-1-dub 1211475734 10.22.241.152 (22 May 2008 10:02:14 -0700)
X-Original-Trace: 22 May 2008 10:02:14 -0700, vip152.sybase.com
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6853
Article PK: 5087

Is there a way to get columns with NULL values in the database in a XML
file?

All functions I'm using have "NULL element values and NULL attribute
values are omitted from the result." in the documentation.
But I need the columsn without any data.

Is that possible?

Frank Vestjens


Mark Culp Posted on 2008-05-23 15:48:05.0Z
Message-ID: <4836E733.76DB7352@iAnywhere.com>
From: Mark Culp <reply_to_newsgroups_only_please_nospam_mark.culp@iAnywhere.com>
X-Mailer: Mozilla 4.75 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: How to get NULL value columns in XML
References: <4835a716$1@forums-1-dub>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 23 May 2008 08:48:05 -0700
X-Trace: forums-1-dub 1211557685 10.22.241.152 (23 May 2008 08:48:05 -0700)
X-Original-Trace: 23 May 2008 08:48:05 -0700, vip152.sybase.com
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6855
Article PK: 5090

In XML, the absense of an attribute represents NULL, whereas an attribute
that as a empty value represents, well, an empty value.

i.e. Using
(1) <thing name="foo">
(2) <thing name="foo" value="">
In (1) foo's 'value' is NULL, whereas in (2) foo's value is "" (empty string)

If you need to have the 'value' attribute show up in the XML, you could
use the ISNULL() function to map NULL values to empty strings.
e.g. something like:

select name, isnull( value, "" ) as value
from thing
for XML AUTO;

But, as I have mentioned in the beginning, this changes the meaning of the
value.
--
Mark Culp
SQLAnywhere Research and Development
iAnywhere Solutions Engineering
-------------------------------------------------------------------------
-- SQL Anywhere Blog Center - http://www.sybase.com/sqlanyblogs
-- SQL Anywhere Developer Community:
-- http://www.sybase.com/developer/library/sql-anywhere-techcorner
-------------------------------------------------------------------------

Frank, Vestjens, > wrote:
>
> Is there a way to get columns with NULL values in the database in a XML
> file?
>
> All functions I'm using have "NULL element values and NULL attribute
> values are omitted from the result." in the documentation.
> But I need the columsn without any data.
>
> Is that possible?
>
> Frank Vestjens


Frank Vestjens <""F V e s t j e n s\" Posted on 2008-05-26 10:01:22.0Z
From: Frank Vestjens <""F V e s t j e n s\"@ i n f o r i t.n l">
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: How to get NULL value columns in XML
References: <4835a716$1@forums-1-dub> <4836E733.76DB7352@iAnywhere.com>
In-Reply-To: <4836E733.76DB7352@iAnywhere.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <483a8a72$1@forums-1-dub>
Date: 26 May 2008 03:01:22 -0700
X-Trace: forums-1-dub 1211796082 10.22.241.152 (26 May 2008 03:01:22 -0700)
X-Original-Trace: 26 May 2008 03:01:22 -0700, vip152.sybase.com
Lines: 54
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6857
Article PK: 5091

Mark,

That won't work because IsNull(value,"") will not work for date fields
for example.

Breck Carter gave me a good solution:

SELECT COALESCE(CAST(table.columname AS VARCHAR),'') as columnname
FROM table

That will do the trick.

Frank

Mark Culp wrote:
> In XML, the absense of an attribute represents NULL, whereas an attribute
> that as a empty value represents, well, an empty value.
>
> i.e. Using
> (1) <thing name="foo">
> (2) <thing name="foo" value="">
> In (1) foo's 'value' is NULL, whereas in (2) foo's value is "" (empty string)
>
> If you need to have the 'value' attribute show up in the XML, you could
> use the ISNULL() function to map NULL values to empty strings.
> e.g. something like:
>
> select name, isnull( value, "" ) as value
> from thing
> for XML AUTO;
>
> But, as I have mentioned in the beginning, this changes the meaning of the
> value.
> --
> Mark Culp
> SQLAnywhere Research and Development
> iAnywhere Solutions Engineering
> -------------------------------------------------------------------------
> -- SQL Anywhere Blog Center - http://www.sybase.com/sqlanyblogs
> -- SQL Anywhere Developer Community:
> -- http://www.sybase.com/developer/library/sql-anywhere-techcorner
> -------------------------------------------------------------------------
> Frank, Vestjens, > wrote:
>
>>Is there a way to get columns with NULL values in the database in a XML
>>file?
>>
>>All functions I'm using have "NULL element values and NULL attribute
>>values are omitted from the result." in the documentation.
>>But I need the columsn without any data.
>>
>>Is that possible?
>>
>>Frank Vestjens


Breck Carter [sqlanywhere.blogspot.com] Posted on 2008-05-26 10:49:24.0Z
From: "Breck Carter [sqlanywhere.blogspot.com]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: ianywhere.public.general
Subject: Re: How to get NULL value columns in XML
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <075l341e01sdh57j2vn1almbmdanp4qri4@4ax.com>
References: <4835a716$1@forums-1-dub> <4836E733.76DB7352@iAnywhere.com> <483a8a72$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 26 May 2008 03:49:24 -0700
X-Trace: forums-1-dub 1211798964 10.22.241.152 (26 May 2008 03:49:24 -0700)
X-Original-Trace: 26 May 2008 03:49:24 -0700, vip152.sybase.com
Lines: 68
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6858
Article PK: 5092

FWIW: y'all show "doublequotes" in IsNull(value,"") where I think you
might want 'singlequotes'.

Breck

On 26 May 2008 03:01:22 -0700, Frank Vestjens <""F V e s t j e n s\"@

i n f o r i t.n l"> wrote:

>Mark,
>
>That won't work because IsNull(value,"") will not work for date fields
>for example.
>
>Breck Carter gave me a good solution:
>
>SELECT COALESCE(CAST(table.columname AS VARCHAR),'') as columnname
>FROM table
>
>That will do the trick.
>
>Frank
>
>Mark Culp wrote:
>> In XML, the absense of an attribute represents NULL, whereas an attribute
>> that as a empty value represents, well, an empty value.
>>
>> i.e. Using
>> (1) <thing name="foo">
>> (2) <thing name="foo" value="">
>> In (1) foo's 'value' is NULL, whereas in (2) foo's value is "" (empty string)
>>
>> If you need to have the 'value' attribute show up in the XML, you could
>> use the ISNULL() function to map NULL values to empty strings.
>> e.g. something like:
>>
>> select name, isnull( value, "" ) as value
>> from thing
>> for XML AUTO;
>>
>> But, as I have mentioned in the beginning, this changes the meaning of the
>> value.
>> --
>> Mark Culp
>> SQLAnywhere Research and Development
>> iAnywhere Solutions Engineering
>> -------------------------------------------------------------------------
>> -- SQL Anywhere Blog Center - http://www.sybase.com/sqlanyblogs
>> -- SQL Anywhere Developer Community:
>> -- http://www.sybase.com/developer/library/sql-anywhere-techcorner
>> -------------------------------------------------------------------------
>> Frank, Vestjens, > wrote:
>>
>>>Is there a way to get columns with NULL values in the database in a XML
>>>file?
>>>
>>>All functions I'm using have "NULL element values and NULL attribute
>>>values are omitted from the result." in the documentation.
>>>But I need the columsn without any data.
>>>
>>>Is that possible?
>>>
>>>Frank Vestjens

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com


Frank Vestjens <""F V e s t j e n s\" Posted on 2008-05-27 06:55:13.0Z
From: Frank Vestjens <""F V e s t j e n s\"@ i n f o r i t.n l">
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: How to get NULL value columns in XML
References: <4835a716$1@forums-1-dub> <4836E733.76DB7352@iAnywhere.com> <483a8a72$1@forums-1-dub> <075l341e01sdh57j2vn1almbmdanp4qri4@4ax.com>
In-Reply-To: <075l341e01sdh57j2vn1almbmdanp4qri4@4ax.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <483bb051$1@forums-1-dub>
Date: 26 May 2008 23:55:13 -0700
X-Trace: forums-1-dub 1211871313 10.22.241.152 (26 May 2008 23:55:13 -0700)
X-Original-Trace: 26 May 2008 23:55:13 -0700, vip152.sybase.com
Lines: 81
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6861
Article PK: 5096

Breck,

That still won't work for fields defined as date fields. I'll ge the
error: can not convert to a timestamp

Frank

Breck Carter [sqlanywhere.blogspot.com] wrote:
> FWIW: y'all show "doublequotes" in IsNull(value,"") where I think you
> might want 'singlequotes'.
>
> Breck
>
> On 26 May 2008 03:01:22 -0700, Frank Vestjens <""F V e s t j e n s\"@
> i n f o r i t.n l"> wrote:
>
>
>>Mark,
>>
>>That won't work because IsNull(value,"") will not work for date fields
>>for example.
>>
>>Breck Carter gave me a good solution:
>>
>>SELECT COALESCE(CAST(table.columname AS VARCHAR),'') as columnname
>
>>FROM table
>
>>That will do the trick.
>>
>>Frank
>>
>>Mark Culp wrote:
>>
>>>In XML, the absense of an attribute represents NULL, whereas an attribute
>>>that as a empty value represents, well, an empty value.
>>>
>>>i.e. Using
>>>(1) <thing name="foo">
>>>(2) <thing name="foo" value="">
>>>In (1) foo's 'value' is NULL, whereas in (2) foo's value is "" (empty string)
>>>
>>>If you need to have the 'value' attribute show up in the XML, you could
>>>use the ISNULL() function to map NULL values to empty strings.
>>>e.g. something like:
>>>
>>> select name, isnull( value, "" ) as value
>>> from thing
>>> for XML AUTO;
>>>
>>>But, as I have mentioned in the beginning, this changes the meaning of the
>>>value.
>>>--
>>>Mark Culp
>>>SQLAnywhere Research and Development
>>>iAnywhere Solutions Engineering
>>>-------------------------------------------------------------------------
>>>-- SQL Anywhere Blog Center - http://www.sybase.com/sqlanyblogs
>>>-- SQL Anywhere Developer Community:
>>>-- http://www.sybase.com/developer/library/sql-anywhere-techcorner
>>>-------------------------------------------------------------------------
>>>Frank, Vestjens, > wrote:
>>>
>>>
>>>>Is there a way to get columns with NULL values in the database in a XML
>>>>file?
>>>>
>>>>All functions I'm using have "NULL element values and NULL attribute
>>>>values are omitted from the result." in the documentation.
>>>>But I need the columsn without any data.
>>>>
>>>>Is that possible?
>>>>
>>>>Frank Vestjens
>
>
> --
> Breck Carter http://sqlanywhere.blogspot.com/
>
> RisingRoad SQL Anywhere and MobiLink Professional Services
> breck.carter@risingroad.com