Generating XML nil='true' from SQL

Question

I'm struggling to modify some SQL generation of XML.

The following sample code generates 3 lines of output, the first 2 are correct, however, I need the 3rd to render out to a root element of xsi:nil=true.

Many thanks.

CREATE TABLE #ReportPackComparativeIndices 
(
    ReportPackRequestID INT,
    IndexDescription VARCHAR(250),
    Value DECIMAL(18,1)
)

INSERT INTO #ReportPackComparativeIndices VALUES  (25984,   'ClientIndexID0',   28.3)
INSERT INTO #ReportPackComparativeIndices VALUES  (25984,   'ClientIndexID1',   43.5)
INSERT INTO #ReportPackComparativeIndices VALUES  (25984,   'ClientIndexID2',   81.1)
INSERT INTO #ReportPackComparativeIndices VALUES  (25984,   'ClientIndexID3',   24.5)
INSERT INTO #ReportPackComparativeIndices VALUES  (25985,   'ClientIndexID0',   93.9)
INSERT INTO #ReportPackComparativeIndices VALUES  (25985,   'ClientIndexID1',   53.8)
INSERT INTO #ReportPackComparativeIndices VALUES  (25985,   'ClientIndexID2',   69.3)
INSERT INTO #ReportPackComparativeIndices VALUES  (25985,   'ClientIndexID3',   26.8)
INSERT INTO #ReportPackComparativeIndices VALUES  (25986,   NULL,   NULL)

SELECT * FROM #ReportPackComparativeIndices


-- Render out the XML Fragments
SELECT ti.ReportPackRequestID,
CAST(
(
    SELECT 
        ti2.IndexDescription,
        ti2.Value
    FROM 
        #ReportPackComparativeIndices AS ti2
    WHERE 
        ti.ReportPackRequestID = ti2.ReportPackRequestID
    FOR XML PATH('ComparisonValue'),
        ROOT('ComparativeInvestments'), 
        ELEMENTS, 
        TYPE
) AS NVARCHAR(MAX)) AS XmlFragment
FROM 
    #ReportPackComparativeIndices AS ti
    GROUP BY 
        ti.ReportPackRequestID  
    ORDER BY
        ti.ReportPackRequestID

Answer 1

Just added XSINIL to your ELEMENTS. Is this what was missing?

EDIT: With this trick you could create a xsi:nil one level above, if both elements are NULL, but I would rather think about the design...

    SELECT ti.ReportPackRequestID,
CAST(
(
    SELECT 
        CASE WHEN ti2.IndexDescription IS NULL AND ti2.Value IS NULL THEN 'true' ELSE NULL END AS [@xsi:nil],
        ti2.IndexDescription,
        ti2.Value
    FROM 
        #ReportPackComparativeIndices AS ti2
    WHERE 
        ti.ReportPackRequestID = ti2.ReportPackRequestID
    FOR XML PATH('ComparisonValue'),
        ROOT('ComparativeInvestments'), 
        ELEMENTS XSINIL, 
        TYPE
) AS NVARCHAR(MAX)) AS XmlFragment
FROM 
    #ReportPackComparativeIndices AS ti
    GROUP BY 
        ti.ReportPackRequestID  
    ORDER BY
        ti.ReportPackRequestID;

One more attempt:

SELECT ti.ReportPackRequestID,
CAST(
(
    SELECT 
        CASE WHEN ti2.IndexDescription IS NULL AND ti2.Value IS NULL THEN 'true' ELSE NULL END AS [ComparativeInvestments/@xsi:nil],
        ti2.IndexDescription AS [ComparativeInvestments/ComparisonValue/IndexDescription],
        ti2.Value AS [ComparativeInvestments/ComparisonValue/Value]
    FROM 
        #ReportPackComparativeIndices AS ti2
    WHERE 
        ti.ReportPackRequestID = ti2.ReportPackRequestID
    FOR XML PATH('dummy'),
        ELEMENTS XSINIL, 
        TYPE
) AS NVARCHAR(MAX)) AS XmlFragment
FROM 
    #ReportPackComparativeIndices AS ti
    GROUP BY 
        ti.ReportPackRequestID  
    ORDER BY
        ti.ReportPackRequestID;

Answer 2

The option I ended up going with was to nest two select statements losing the XMLRoot element in favour of two separate XML PATH statements applying XSINIL to the outer (old root) element.

SELECT ti.ReportPackRequestID,
CAST(
(
    SELECT
    (
        SELECT 
            CASE WHEN ti2.IndexID IS NOT NULL 
                 THEN 'ClientIndexID' + CAST(ti2.RowNumber -1 AS VARCHAR(5)) 
                 ELSE NULL
            END AS IndexID,
            ti2.IndexTotalReturn AS Value
        FROM 
            #ReportPackComparativeIndices AS ti2
        WHERE 
            ti.ReportPackRequestID = ti2.ReportPackRequestID
        ORDER BY 
            ti2.RowNumber
        FOR XML PATH('ComparisonValue'), 
            ELEMENTS, 
            TYPE
    )
    FOR XML PATH('ComparativeInvestments'), 
        ELEMENTS XSINIL, 
        TYPE
) AS NVARCHAR(MAX)) AS XmlFragment
FROM 
    #ReportPackComparativeIndices AS ti
GROUP BY 
    ti.ReportPackRequestID  
ORDER BY
    ti.ReportPackRequestID

This got me the desired output in the XML :

<ComparativeInvestments xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>