Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML PATH: Question about null elements Expand / Collapse
Author
Message
Posted Thursday, December 19, 2013 1:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 12:53 AM
Points: 18, Visits: 235
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.
Post #1524480
Posted Thursday, December 19, 2013 5:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:40 AM
Points: 12,953, Visits: 32,477
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1524531
Posted Thursday, December 19, 2013 2:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 7,179, Visits: 15,765
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?
Post #1524798
Posted Thursday, December 19, 2013 10:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 12:53 AM
Points: 18, Visits: 235
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

Post #1524862
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse