Генерация XML nil='true' из SQL

Question

Я изо всех сил пытаюсь изменить некоторые поколения SQL XML.

Следующий пример кода генерирует 3 строки вывода, первые 2 верны, однако мне нужно 3-е для рендеринга в корневой элемент xsi:nil=true.

Большое спасибо.

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

Просто добавили XSINIL в ваши элементы. Это то, чего не хватало?

РЕДАКТИРОВАТЬ: С помощью этого трюка вы могли бы создать xsi:nil на один уровень выше, если оба элемента имеют значение NULL, но я бы лучше подумал о дизайне...

    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;

Еще одна попытка:

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

Вариант, который я выбрал, заключался в том, чтобы вложить два оператора select, теряющих элемент XMLRoot, в пользу двух отдельных операторов XML PATH, применяющих XSINIL к внешнему (старому корневому) элементу.

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

Это дало мне желаемый результат в XML:

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