Contents Index FOR XML and binary data Invalid column names

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

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].


Contents Index FOR XML and binary data Invalid column names