Как добавить xsi:nil="true" только в одно поле при создании файла XML из SQL Server 2008, используя для "путь XML"

Question

Вот код, который я использую:

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('');

Только для узла страны мне нужно установить xsi:nil="true". Спасибо

Answer 1

Создание элементов для значений NULL с использованием параметра XSINIL

добавлять ELEMENTS XSINIL к FOR 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] = country,
        [PrimaryAddress/Zip] = zipcode
FROM StudentTable
FOR XML Path('Student'), ROOT('Root'), TYPE, ELEMENTS XSINIL
)

Выше будет установлено xsi:nil="true" для всех узлов в XML, который NULL,

Чтобы сделать это только для одного поля, вы можете использовать подзапрос с ELEMENTS XSINIL построить 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] = (SELECT Country = country FOR XML PATH(''), TYPE, ELEMENTS XSINIL),
        [PrimaryAddress/Zip] = zipcode
FROM StudentTable
FOR XML Path('Student'), ROOT('Root'), TYPE
)