Thursday, 19 September 2013

Return NULL from XML EXPLICIT subquery where no rows exist

Return NULL from XML EXPLICIT subquery where no rows exist

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?

No comments:

Post a Comment