I have a bit of a strange use case for XML PATH and it's causing me a bit of a headache. Basically, if there is no data present for the element, I want it to not be returned at all, rather than getting a null element back. This is because the XML is later validated against an XSD and this element is singleton-mandatory, i.e if the element is present the XSD validation fails if there is no data.
Here is the sample code:
declare @id int
set @id = null
CASE WHEN @id is null
ELSE 'Forehead Barcode' END
RTRIM(@id) as [Value]
FOR XML PATH('ID'), TYPE
This will return '<ID />' whereas I'd like it to just be a blank result.
So far I've tried using IFNULL on @id with no success.
If the XML PATH is set to an empty string then this works as desired (without using IFNULL), but the path is required so the element is recognisable in the output XML.
Any help would be greatly appreciated, this will save me doing a pass on the output XML files (several hundred thousdand) to scrub any null elements.