How to handle null objects in XML coming from SQL Server?

Question

I have a stored procedure that returns an object as XML. How do I handle the case where the object doesn't exist?

I can't return null or empty string as the XmlReader complains that it is not valid XML.

If I return an empty Tag, how do I tell if it is just an empty object or no object?

Answer 1

Well, a common approach is some marker attribute, such as xsi:nil="true" (although this is just an example).

Answer 2

Either return a self defined empty-tag () and check for it or just change the way you are reading the data:

• fetch the stream into a string-var first and check for String.IsNullOrEmpty
• just try-catch the reader for ArgumentNullException/ArgumentException

Answer 3

Create an out parameter for your stored procedure. Inside the stored procedure, set the value to 1 for an existing object or 0 for a non-existent. Only deserialize the xml result of the stored procedure if the out parameter is 1.

CREATE PROCEDURE sp_GetObjectAsXml         
(
    @intId int,
    @intExists int out
) 
AS  
BEGIN
    SELECT 'hello world'  WHERE 1 = @intId
    SET @intExists = @@ROWCOUNT
END

-- Happy Path
DECLARE @intExists int
exec sp_GetObjectAsXml 1, @intExists out
SELECT @intExists -- results in 1

-- Sad Path
DECLARE @intExists int
exec sp_GetObjectAsXml 10, @intExists out
SELECT @intExists -- results in 0

Answer 4

Depending on your "external constraints" (e.g. how much of the XML generating/consuming process you control), you can express null by not including the element at all.

The receiving side (schemal validation etc.) must of course be prepared to deal with an optional element.

Answer 5

The two ways that I've seen are:

1. Include an attribute to indicate whether the item is null or not (isnull="true|false"). Generally, false is assumed if the attribute is not included.
2. Don't include the element for that node. If it doesn't exist in the xml it's null. If it does exist and is empty, it's just an empty string. This, of course, relies on your parsing code being able to determine what elements should exist (via either a schema or information earlier in the xml file, generally).

The first option is generally easier to deal with, but results in more text. The second can result in a smaller xml file.