T-SQL FOR XML EXPLICIT - add attribute "xsi:nil" to elements

Question

I need to generate XML files to third-party specifications. The one case where I'm having trouble is where there is a NULL column. The spec says that along with the xsi:nil="true" attribute, there needs to be a reason attribute.

The node would need to look like this:

<Phone xsi:nil="true" Reason="none" />

I can generate nodes with attributes and values, but not xsi:nil with an additional attribute.

This generates the proper format for everything except the xsi:nil attribute:

DECLARE @T TABLE(
    [Id] [int],
    [OwnerId] [int],
    [Number] [char](12),
    [Type] [char](4),
    [Reason] [char](4))
INSERT INTO @T VALUES
    (1,1,'414-555-1212','cell',NULL),
    (2,2,NULL,NULL,'None'),
    (3,3,'202-555-1212','work',NULL),
    (4,3,'212-555-1212','cell',NULL)

SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS [Root!1],
    NULL AS [Phone!2!OwnerId],
    NULL AS [Phone!2!Type],
    NULL AS [Phone!2!Reason],
    NULL AS [Phone!2]
union all
SELECT
    2 AS Tag,
    1 AS Parent,
    NULL,
    OwnerId,
    Type,
    Reason,
    Number
FROM
    @T
FOR XML EXPLICIT;

The output is:

<Root>
    <Phone OwnerId="1" Type="cell">414-555-1212</Phone>
    <Phone OwnerId="2" Reason="None" />
    <Phone OwnerId="3" Type="work">202-555-1212</Phone>
    <Phone OwnerId="3" Type="cell">212-555-1212</Phone>
</Root>

I can use ELEMENTXSINIL to get the proper treatment of NULL values with the namespace defined in the root node with this, but the phone number is a different element:

SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS [Root!1],
    NULL AS [Phone!2!OwnerId],
    NULL AS [Phone!2!Type],
    NULL AS [Phone!2!Reason],
    NULL AS [Phone!2!Number!ELEMENTXSINIL]
union all
SELECT
    2 AS Tag,
    1 AS Parent,
    NULL,
    OwnerId,
    Type,
    Reason,
    Number
FROM
    @T
FOR XML EXPLICIT;

<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Phone OwnerId="1" Type="cell">
        <Number>414-555-1212</Number>
    </Phone>
    <Phone OwnerId="2" Reason="None">
        <Number xsi:nil="true" />
    </Phone>
    <Phone OwnerId="3" Type="work">
        <Number>202-555-1212</Number>
    </Phone>
    <Phone OwnerId="3" Type="cell">
        <Number>212-555-1212</Number>
    </Phone>
</Root>

The specs are very consistent and Reason as an attribute will only appear on NULL values. I can do some post processing and add xsi:nil="true" to those nodes and the namespace definition in the root element, but I'd prefer to get this step to do it correctly.

Is it possible?

Answer 1

If you're committed to using FOR XML EXPLICIT, which doesn't really make for understandable and maintainable code, then you can add an namespace declaration to the Root element and manually add the xsi:nil="true" attribute like the following:

SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS [Root!1],
    'http://www.w3.org/2001/XMLSchema-instance' AS [Root!1!xmlns:xsi],
    NULL AS [Phone!2!OwnerId],
    NULL AS [Phone!2!Type],
    NULL AS [Phone!2!Reason],
    NULL AS [Phone!2!xsi:nil],
    NULL AS [Phone!2]
union all
SELECT
    2 AS Tag,
    1 AS Parent,
    NULL,
    NULL,
    OwnerId,
    Type,
    Reason,
    CASE WHEN Number IS NULL THEN 'true' END,
    Number
FROM
    @T
FOR XML EXPLICIT;

Which produces the output:

<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Phone OwnerId="1" Type="cell">414-555-1212</Phone>
    <Phone OwnerId="2" Reason="None" xsi:nil="true"/>
    <Phone OwnerId="3" Type="work">202-555-1212</Phone>
    <Phone OwnerId="3" Type="cell">212-555-1212</Phone>
</Root>

The same output can be produced with the same manual attribute management using FOR XML PATH like the following:

WITH XMLNAMESPACES (
  'http://www.w3.org/2001/XMLSchema-instance' AS xsi
)
SELECT
    OwnerId AS [@OwnerId],
    Type AS [@Type],
    Reason AS [@Reason],
    CASE WHEN Number IS NULL THEN 'true' END AS [@xsi:nil],
    Number AS [data()] -- or [node()] or [text()]
  FROM @T
FOR XML PATH ('Phone'), ROOT('Root');

Although you might have expected that the above could be simplified with ELEMENTS XSINIL for some reason FOR XML PATH doesn't recognize the null output from data(), node() nor text() to add the xsi:nil="true" attribute itself.

To use FOR XML PATH with ELEMENTS XSINIL more simply we can rely on a different technique - empty element names get excluded from the output:

SELECT
    OwnerId AS [Phone/@OwnerId],
    Type AS [Phone/@Type],
    Reason AS [Phone/@Reason],
    Number AS [Phone]
  FROM @T
FOR XML PATH (''), ROOT('Root'), ELEMENTS XSINIL;