Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Using XML in the Database » Obtaining query results as XML » Using the FOR XML clause to retrieve query results as XML

FOR XML and binary data Next Page

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 an entry in the Customers table that contains a NULL company name.

INSERT INTO 
      Customers(ID,Surname,GivenName,Street,City,Phone) 
VALUES (100,'Robert','Michael',
       '100 Anywhere Lane','Smallville','519-555-3344');

If you execute the following query with the for_xml_null_treatment option set to Omit (the default), then no attribute is generated for a NULL column value.

SELECT ID, GivenName, Surname, CompanyName
FROM Customers
WHERE GivenName LIKE 'Michael%'
ORDER BY ID
FOR XML RAW

In this case, no CompanyName attribute is generated for Michael Robert.

<row ID="100" GivenName="Michael" Surname="Robert"/>
<row ID="101" GivenName="Michaels" Surname="Devlin" CompanyName="The Power Group"/>
<row ID="110" GivenName="Michael" Surname="Agliori" CompanyName="The Pep Squad"/>

If the for_xml_null_treatment option is set to Empty, then an empty attribute is included in the result:

<row ID="100" GivenName="Michael" Surname="Robert" CompanyName=""/>
<row ID="101" GivenName="Michaels" Surname="Devlin" CompanyName="The Power Group"/>
<row ID="110" GivenName="Michael" Surname="Agliori" CompanyName="The Pep Squad"/>

In this case, an empty CompanyName attribute is generated for Michael Robert.

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