Return NULL from XML EXPLICIT subquery where no rows exist

Question

This is probably simple to do, but I'm having a brain-fart on this one...

I'm using FOR XML EXPLICIT as part of a subquery so that I can explicitly define the format of the returned XML. Therefore I'm using UNION ALL to define that format.

This is working fine, but I need it to return NULL if there are no rows in that sub-query... at the moment it is returning an empty root element: <codes/>. That is because I need the first row for the definition.

Here is a sqlfiddlecom with everything below for you to look at.

This is a version of the TSQL as it currently is...

SELECT
    P.[PROJECTID],
    P.[PROJECTNAME],
    (   SELECT *
        FROM (
                SELECT
                    1 AS TAG,
                    NULL AS PARENT,
                    NULL AS 'codes!1',
                    NULL AS 'code!2!!element',
                    NULL AS 'code!2!split'
                UNION ALL
                SELECT
                    2 AS TAG,
                    1 AS PARENT,
                    NULL,                   
                    C.[CODE],
                    C.[SPLIT]
                FROM [CODES] C
                WHERE C.[PROJECTID] = P.[PROJECTID]
             ) AS [CODEXMLDATA]
        FOR XML EXPLICIT
    ) AS [CODESXML]
FROM [PROJECTS] P

Example data would be along the lines of

PROJECTS table
PROJECTID PROJECTNAME
1         This
2         That
3         Other

CODES table
PROJECTID CODE SPLIT
1         ABC  45
1         BCD  65
2         CDE  100

The result is coming out as...

PROJECTID PROJECTNAME CODESXML
1         This        <codes><code split="45">ABC</code><code split="55">BCD</code></codes>
2         That        <codes><code split="100">CDE</code></codes>
3         Other       <codes/>

The result I need is (note the NULL on the 3rd line)...

PROJECTID PROJECTNAME CODESXML
1         This        <codes><code split="45">ABC</code><code split="55">BCD</code></codes>
2         That        <codes><code split="100">CDE</code></codes>
3         Other       NULL

Can anybody give me a hint how I can make it return NULL when there are no CODES?

I'm not sure how you'd integrate a CASE WHEN into that @Viji, can you give an example as an answer? Or alter the sqlfiddle? — freefaller
– freefaller, Commented Sep 19, 2013 at 19:20

Viji · Accepted Answer · 2013-09-19 20:18:18Z

Try this code

SELECT distinct 
    P.[PROJECTID],
    P.[PROJECTNAME],
    case when (p.projectid = c.projectid) then 
    (   SELECT *
        FROM (
                SELECT
                    1 AS TAG,
                    NULL AS PARENT,
                    NULL AS 'codes!1',
                    NULL AS 'code!2!!element',
                    NULL AS 'code!2!split'
                UNION ALL
                SELECT
                    2 AS TAG,
                    1 AS PARENT,
                    NULL,                   
                    C.[CODE],
                    C.[SPLIT]
                FROM [CODES] C
                WHERE C.[PROJECTID] = P.[PROJECTID]
             ) AS [CODEXMLDATA]
        FOR XML EXPLICIT
    ) else null end AS [CODESXML]
FROM [PROJECTS] P
left join [CODES] C on  C.[PROJECTID] = P.[PROJECTID]

SQLFiddle : http://sqlfiddle.com/#!3/f8788/7/0

Collectives™ on Stack Overflow

Return NULL from XML EXPLICIT subquery where no rows exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related