Return to Answer

ISNULL Quick Answer

SELECT ISNULL(NULL,'NULL') FOR XML PATH('foo')

This means if you want the XML element, etc. to have a value of NULL rather than being blank, then your SELECT statement would need to use the ISNULL function and say if the value is NULL then replace with literal NULL value to be as the actual XML value.

ELEMENTS XSINIL

Consider testing with the ELEMENTS XSINIL parameter too and see what results you get with your FOR XML PATH logic.

Resource: https://msdn.microsoft.com/en-us/library/ms178079.aspx

SNIPPET EXAMPLE BUILDING AN HTLM TABLE WHERE I USE ELEMENTS XSINIL

</tr>' + CAST((
            SELECT td = [Column1]
                ,''
                ,td = [Column2]
                ,''
                ,td = [Column3]
                ,''
                ,td = [Column4]
                ,''
                ,td = [Column5]
                ,''
                ,td = [Column6]
                ,''
                ,td = [Column7]
            FROM #tempReport
            ORDER BY [Column1] ASC
            FOR XML PATH('tr')
                ,TYPE
                ,ELEMENTS XSINIL
            ) AS NVARCHAR(MAX)) + '
    </table>
</body>
</html>';

ISNULL Quick Answer

SELECT ISNULL(NULL,'NULL') FOR XML PATH('foo')

This means if you want the XML element, etc. to have a value of NULL rather than being blank, then your SELECT statement would need to use the ISNULL function and say if the value is NULL then replace with literal NULL value to be as the actual XML value.

ELEMENTS XSINIL

Consider testing with the ELEMENTS XSINIL parameter too and see what results you get with your FOR XML PATH logic. This may work for your need as well.

SELECT ISNULL(NULL, 'NULL')
FOR XML PATH('foo')
    ,TYPE
    ,ELEMENTS XSINIL

Resource: https://msdn.microsoft.com/en-us/library/ms178079.aspx

The ELEMENTS directive constructs XML in which each column value maps to an element in the XML. If the column value is NULL, no element is added. By specifying the optional XSINIL parameter on the ELEMENTS directive, you can request that an element also be created for the NULL value. In this case, an element that has the xsi:nil attribute set to TRUE is returned for each NULL column value.

Quick potential answer. . .

SELECT ISNULL(NULL,'NULL') FOR XML PATH('foo')

I think this means if you want the XML element, etc. to have a value of NULL rather than being blank, then your SELECT statement would need to use the ISNULL function and say if the value is NULL then replace with literal NULL value to be as the actual XML value.

Also consider testing with the ELEMENTS XSINIL parameter too and see what results you get with your FOR XML PATH logic.

Resource: https://msdn.microsoft.com/en-us/library/ms178079.aspx

SNIPPET EXAMPLE BUILDING AN HTLM TABLE WHERE I USE ELEMENTS XSINIL

</tr>' + CAST((
            SELECT td = [Column1]
                ,''
                ,td = [Column2]
                ,''
                ,td = [Column3]
                ,''
                ,td = [Column4]
                ,''
                ,td = [Column5]
                ,''
                ,td = [Column6]
                ,''
                ,td = [Column7]
            FROM #tempReport
            ORDER BY [Column1] ASC
            FOR XML PATH('tr')
                ,TYPE
                ,ELEMENTS XSINIL
            ) AS NVARCHAR(MAX)) + '
    </table>
</body>
</html>';

ISNULL Quick Answer

SELECT ISNULL(NULL,'NULL') FOR XML PATH('foo')

This means if you want the XML element, etc. to have a value of NULL rather than being blank, then your SELECT statement would need to use the ISNULL function and say if the value is NULL then replace with literal NULL value to be as the actual XML value.

ELEMENTS XSINIL

Consider testing with the ELEMENTS XSINIL parameter too and see what results you get with your FOR XML PATH logic.

Resource: https://msdn.microsoft.com/en-us/library/ms178079.aspx

SNIPPET EXAMPLE BUILDING AN HTLM TABLE WHERE I USE ELEMENTS XSINIL

</tr>' + CAST((
            SELECT td = [Column1]
                ,''
                ,td = [Column2]
                ,''
                ,td = [Column3]
                ,''
                ,td = [Column4]
                ,''
                ,td = [Column5]
                ,''
                ,td = [Column6]
                ,''
                ,td = [Column7]
            FROM #tempReport
            ORDER BY [Column1] ASC
            FOR XML PATH('tr')
                ,TYPE
                ,ELEMENTS XSINIL
            ) AS NVARCHAR(MAX)) + '
    </table>
</body>
</html>';

Quick potential answer. . .

SELECT ISNULL(NULL,'NULL') FOR XML PATH('foo')

I think this means if you want the XML element, etc. to have a value of NULL rather than being blank, then your SELECT statement would need to use the ISNULL function and say if the value is NULL then replace with literal NULL value to be as the actual XML value.

Also consider testing with the ELEMENTS XSINIL parameter too.

SNIPPET EXAMPLE CODE

</tr>' + CAST((
            SELECT td = [Column1]
                ,''
                ,td = [Column2]
                ,''
                ,td = [Column3]
                ,''
                ,td = [Column4]
                ,''
                ,td = [Column5]
                ,''
                ,td = [Column6]
                ,''
                ,td = [Column7]
            FROM #tempReport
            ORDER BY [Column1] ASC
            FOR XML PATH('tr')
                ,TYPE
                ,ELEMENTS XSINIL
            ) AS NVARCHAR(MAX)) + '
    </table>
</body>
</html>';

Quick potential answer. . .

SELECT ISNULL(NULL,'NULL') FOR XML PATH('foo')

I think this means if you want the XML element, etc. to have a value of NULL rather than being blank, then your SELECT statement would need to use the ISNULL function and say if the value is NULL then replace with literal NULL value to be as the actual XML value.

Also consider testing with the ELEMENTS XSINIL parameter too and see what results you get with your FOR XML PATH logic.

Resource: https://msdn.microsoft.com/en-us/library/ms178079.aspx

SNIPPET EXAMPLE BUILDING AN HTLM TABLE WHERE I USE ELEMENTS XSINIL

</tr>' + CAST((
            SELECT td = [Column1]
                ,''
                ,td = [Column2]
                ,''
                ,td = [Column3]
                ,''
                ,td = [Column4]
                ,''
                ,td = [Column5]
                ,''
                ,td = [Column6]
                ,''
                ,td = [Column7]
            FROM #tempReport
            ORDER BY [Column1] ASC
            FOR XML PATH('tr')
                ,TYPE
                ,ELEMENTS XSINIL
            ) AS NVARCHAR(MAX)) + '
    </table>
</body>
</html>';