FOR XML XSINIL: Emit Missing Fields as Explicit Nil Elements

FOR XML XSINIL can represent SQL NULL with an explicit element carrying the xsi:nil attribute. I compare that output with ordinary element omission. An empty string stays a separate input in both versions.

A consumer may care whether a field was omitted or explicitly included as nil. Those forms should be chosen as part of an interface contract. They are not automatically interchangeable representations for every application.

Serialize the same three rows twice

The input contains a NULL text value, an empty string and the word Blue. Each row has a stable identifier. Every text expression is explicitly typed as varchar with length ten.

The first subquery uses ordinary ELEMENTS output. The second adds XSINIL. Both use the same row order, row element name and outer rows wrapper.

TYPE returns XML values that can be inspected with XML methods. The final query tests field presence, the namespace-qualified nil attribute and populated text. It does not infer the result from a displayed blank cell.

WITH Input AS
(
    SELECT CaseId, TextValue
    FROM (VALUES
        (1, CAST(NULL AS varchar(10))),
        (2, CAST('' AS varchar(10))),
        (3, CAST('Blue' AS varchar(10)))
    ) AS v(CaseId, TextValue)
), Documents AS
(
    SELECT
        (SELECT CaseId AS Id, TextValue FROM Input ORDER BY CaseId
         FOR XML PATH('row'), ROOT('rows'), ELEMENTS, TYPE) AS Omitted,
        (SELECT CaseId AS Id, TextValue FROM Input ORDER BY CaseId
         FOR XML PATH('row'), ROOT('rows'), ELEMENTS XSINIL, TYPE) AS NilTagged
)
SELECT Omitted.exist('/rows/row[Id=1]/TextValue') AS OmittedNullElement,
       NilTagged.exist('/rows/row[Id=1]/TextValue') AS NilNullElement,
       NilTagged.exist('declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
           /rows/row[Id=1]/TextValue[@xsi:nil="true"]') AS NullMarkedNil,
       Omitted.exist('/rows/row[Id=2]/TextValue') AS OmittedEmptyElement,
       NilTagged.exist('/rows/row[Id=2]/TextValue') AS NilEmptyElement,
       NilTagged.exist('declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
           /rows/row[Id=2]/TextValue[@xsi:nil="true"]') AS EmptyMarkedNil,
       Omitted.value('(/rows/row[Id=3]/TextValue/text())[1]', 'varchar(10)') AS OmittedPresentText,
       NilTagged.value('(/rows/row[Id=3]/TextValue/text())[1]', 'varchar(10)') AS NilPresentText
FROM Documents;

Read the presence and nil flags

OmittedNullElement should be zero because the first serialization leaves out the NULL field. NilNullElement should be one in the XSINIL document. NullMarkedNil should also be one.

The empty-string field should exist in both documents. EmptyMarkedNil should be zero because an empty string is not SQL NULL. An empty element and a nil-tagged element have different diagnostic properties.

The populated row should return Blue from both documents. Adding XSINIL does not replace its ordinary text with nil. The expected result checks both populated outputs explicitly.

The eight-column model keeps omission, explicit nil, empty text and populated text together. Removing one of those categories would weaken the comparison. Their presence flags explain what a blank-looking XML value can hide.

Keep the namespace-qualified attribute

The nil test binds xsi to the XML Schema instance namespace URI. It then checks the qualified attribute rather than an ordinary attribute named nil. The namespace is part of the attribute’s identity.

SQL Server supplies the xsi declaration for XSINIL output. The query does not manually add an unqualified lookalike attribute. That keeps the result tied to the serializer’s actual nil representation.

I don’t assign a business reason to the NULL field. It could represent unavailable, withheld or inapplicable data depending on the interface. XSINIL specifies a representation, not the application’s reason for absence.

Likewise, an empty string might be an intentional value or a source-quality issue. The serializer does not decide that policy. Keep the source distinction visible before normalizing it for a recipient.

Validate the recipient contract

A schema or consumer can require fields to be present, allow them to be omitted, or recognize nil. Check those requirements before choosing the serialization option. The database’s preferred style alone cannot establish compatibility.

This example returns diagnostic values rather than relying on namespace declaration placement in a screenshot. XML identity is established by bindings and values. Formatting choices such as self-closing tags should not be mistaken for data changes.

The query changes no rows, objects or session settings. It demonstrates one bounded serialization choice. Keep all three input categories when testing a larger XML interface.

Pick omission or explicit nil on purpose, and the recipient will not have to guess.

A missing field is not an empty string, it is a NULL that XML must name or omit.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.