Query for Multiple Rows as XML 'NULL' question

[Former Community Member]

There are two fields in the database.

Foo = null (real null)

Foo1 = '' (blank string)

In my return xml the nodes are as follows:

<Foo>null</Foo>

<Foo1/>

Why is it that if there is a null in the database, that it puts the string null, and then when there is a blank string, the node is empty?

How can I get the null value to act like the blank value?

Cheers

[AKloft]

do a translate on deserialized xml to switch null with '', then re-serialize your xml and the '' will be in there.  Or when you're building your query, depending on the DBMS, you can do a SELECT CASE <field> IS NULL THEN '' ELSE <field> FROM <table>;

As to why it puts the null string literal in the xml, I can only speculate (and that may be wrong).

[_Nith_]

SELECT COALESCE(Foo,''),COALESCE(Foo1,'') FROM mytable;

The above query will return '' (empty string) if the columns has NULL value.

This is another solution.

Third solution is to apply XSL and remove NULL value.

[Former Community Member]

This is all very true.

If I add int he XSLT, there is no way to determine if the results is db null or string null.  So just striping it out is not great.

I'm just more interested in why the activity don't tell us if it is a db null or a true string null.