ASA SQL User's Guide
Using XML in the Database
Obtaining query results as XML
Using the FOR XML clause to retrieve query results as XML
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].