iAnywhere.com - FOR XML and NULL values

FOR XML and NULL values

By default, elements and attributes that contain NULL values are omitted from the result. This behavior is controlled by the FOR_XML_NULL_TREATMENT option.

Consider a table that contains the following data:

id	fname	lname	company_name
100	Robert	Michaels	NULL
101	Michael	Devlin	The Power Group

If you execute the following query with the FOR_XML_NULL_TREATMENT option set to OMIT (the default)

SELECT id, fname, lname, company_name
FROM customer
FOR XML RAW

the company_name attribute is omitted from the result for Robert Michaels:

<row id="100" fname="Robert" lname="Michaels"/>
<row id="101" fname="Michaels" lname="Devlin" 
 company_name="The Power Group"/>'

If the FOR_XML_NULL_TREATMENT option is set to EMPTY, then an empty attribute is included in the result:

<row id="100" fname="Robert" lname="Michaels"
 company_name=""/>
<row id="101" fname="Michaels" lname="Devlin"
 company_name="The Power Group"/>'

In this case, an empty company_name attribute is generated for Robert Michaels.

For information about the FOR_XML_NULL_TREATMENT option, see FOR_XML_NULL_TREATMENT option [database].