How do I add xsi:nil="true" to only one field while generating an xml file from SQL Server 2008 using for 'xml path'

Question

Here is the code I am using:

DECLARE @returnXML XML  

SELECT   @ReturnXML = 
(SELECT [StudentID/SSN] = ssn, 
        [StudentName/LastName] = lastname,
        [StudentName/FirstName] = firstname,
        [StudentName/MiddleInitial] = Middlename,
        [PrimaryAddress/Street1] = line1,
        [PrimaryAddress/Street2] = line2,
        [PrimaryAddress/City] = city,
        [PrimaryAddress/State] = state,
        [PrimaryAddress/Country] = ISNULL(country,null),
        [PrimaryAddress/Zip] = zipcode

FROM StudentTable
FOR     XML Path('Student'), ROOT('Root'),TYPE
)

SELECT  @ReturnXML  
FOR     XML PATH('');

Only for the country node do I need to set xsi:nil="true". Thanks

Answer 1

Generating Elements for NULL Values Using the XSINIL Parameter

Add ELEMENTS XSINIL to the FOR XML clause.

SELECT   @ReturnXML = 
(SELECT [StudentID/SSN] = ssn, 
        [StudentName/LastName] = lastname,
        [StudentName/FirstName] = firstname,
        [StudentName/MiddleInitial] = Middlename,
        [PrimaryAddress/Street1] = line1,
        [PrimaryAddress/Street2] = line2,
        [PrimaryAddress/City] = city,
        [PrimaryAddress/State] = state,
        [PrimaryAddress/Country] = country,
        [PrimaryAddress/Zip] = zipcode
FROM StudentTable
FOR XML Path('Student'), ROOT('Root'), TYPE, ELEMENTS XSINIL
)

The above will set xsi:nil="true" for all nodes in the XML that is NULL.

To do it only for one field you can use a subquery with ELEMENTS XSINIL to build the XML for that field.

SELECT   @ReturnXML = 
(SELECT [StudentID/SSN] = ssn, 
        [StudentName/LastName] = lastname,
        [StudentName/FirstName] = firstname,
        [StudentName/MiddleInitial] = Middlename,
        [PrimaryAddress/Street1] = line1,
        [PrimaryAddress/Street2] = line2,
        [PrimaryAddress/City] = city,
        [PrimaryAddress/State] = state,
        [PrimaryAddress] = (SELECT Country = country FOR XML PATH(''), TYPE, ELEMENTS XSINIL),
        [PrimaryAddress/Zip] = zipcode
FROM StudentTable
FOR XML Path('Student'), ROOT('Root'), TYPE
)