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

First google hit: [Example: Specifying XSINIL with the ELEMENTS Directive](https://msdn.microsoft.com/en-us/library/bb510413%28v=sql.120%29.aspx). Second google hit: [Generate Elements for NULL Values with the XSINIL Parameter](https://msdn.microsoft.com/en-us/library/ms178079%28v=sql.120%29.aspx). — GSerg, Sep 01 '15 at 11:18
Thanks for taking the time to respond, however, neither of the pages answer the question I asked. Adding XSINIL parameter to the ELEMENTS directive sets the internal elements 'IndexDescription' and 'Value' 'xsi:nil="true" ', not the root element 'ComparativeInvestments' — Jonnie, Sep 01 '15 at 12:24

Shnugo · Answer 1 · 2015-09-01T16:11:06.353

2

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;

edited Sep 01 '15 at 16:11

answered Sep 01 '15 at 11:36

Shnugo

66,100
9
53
114

Thanks but I'm afraid not, this sets IndexDescription and Value as xsi:nil, not the root element 'ComparativeInvestments' : – Jonnie Sep 01 '15 at 12:26
1

@JonathonRalfe, Should the root element itself really be NULL? I edited my answer, but I think there's something wrong with the design... – Shnugo Sep 01 '15 at 12:44
Thanks @Shnugo, That appears to give me a good xsi:nil=true on ComparisonValue, but not on the root element ComparisonInvestment. I understand your point regarding the root element ideally not null, but this sits within an SSIS package which is stitched together elsewhere and so is not truly the root element, but my hands are tied. – Jonnie Sep 01 '15 at 13:57
1

@JonathonRalfe, It is ugly, but you could simply replace "> – Shnugo Sep 01 '15 at 14:00
Thanks @Shnugo, not ideal, but probably the best achievable within the constraints. – Jonnie Sep 01 '15 at 15:29
1

@JonathonRalfe, There's one more solution within my answer now. I suround the XML with another level, thus making the root not being the root anymore... If this helps, please mark as accepted, thx! – Shnugo Sep 01 '15 at 16:12
Thanks @Shungo, quite similar to where I ended up. – Jonnie Sep 01 '15 at 16:44

score 1 · Answer 2 · answered Sep 01 '15 at 16:43

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"/>

Generating XML nil='true' from SQL

2 Answers2