XML PATH: Question about null elements

Question

Post reply

XML PATH: Question about null elements

dr.blowfin

Say Hey Kid

Points: 686
More actions
December 19, 2013 at 1:38 am

#302475

Hello,
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
SELECT
CASE WHEN @id is null
THEN null
ELSE 'Forehead Barcode' END
AS [IDType],
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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i had to wrap you r example a couple of times to get it to return an empty string instead of null;

does this help at all?

declare @id int

set @id = null

SELECT ISNULL(NULLIF(convert(varchar(max),Results),'<ID/>'),'') As Val

FROM

(

SELECT Results =(

SELECT

CASE WHEN @id is null

THEN null

ELSE 'Forehead Barcode' END

AS [IDType],

RTRIM(@id) as [Value]

FOR XML PATH('ID'), TYPE)

)x

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

As long as you're only dealing with one ID at a time, you can use this syntax:

declare @id int

set @id = null

SELECT

CASE WHEN @id is null

THEN null

ELSE 'Forehead Barcode' END

AS [ID/IDType],

RTRIM(@id) as [Value]

for XML PATH(''),TYPE, ELEMENTs absent

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

dr.blowfin Say Hey Kid Points: 686 More actions · Answer 3

Thanks to you both, great stuff! Those two approaches are the ways I assumed it could be solved, and they both work. By either wrapping in further statements and by using more explicit XML declaration. "Elements Absent" is a good pickup, I was aware of XSINIL but hadn't seen that one.

Just a small change to your code Matt (yes it is just one ID at a time) to put the Value inside the ID element if it's present, i'll stick with that as it's a bit cleaner than wrapping.

declare @id int

set @id = null

SELECT

CASE WHEN @id is null

THEN null

ELSE 'Forehead Barcode' END

AS [ID/IDType],

RTRIM(@id) as [ID/Value]

for XML PATH(''),TYPE, ELEMENTS ABSENT