Including field in XML output if not empty, leave out when empty

Question

my first question here, but I have been reading and learning an awful lot from this great site. Short intro, I'm a application support specialist for a global company. Started in November, but already swamped with work.

Currently I'm working on creating XML-files from our SQL Server environment to declare our shipments to Hungarian customs. It's all working fine already, with help from around Stackoverflow threads. Now I'm on fine-tuning duty and I'm struggling with the following.

In the XML there are optional fields which are to be filled sometimes, but not always. Trouble is, when the field is empty, the XML will fail. So when empty, the complete tag should not be included. And when the field is filled, then the tag and data should be included in the XML-file.

Example;

 SELECT    [LocationName] as name
          ,[LocationPhone] as phone
          ,[LocationEmail] as email
          ,[LocationCountry] as country
          ,[LocationCity] as city
    FROM [XML_view]
    FOR XML PATH ('Location'), ELEMENTS, TYPE

Name, Country and City are mandatory and always filled, but Phone and Email may or may not be empty.

The result is now as follows;

<Location 
  <name>BuildingOne</name>
  <phone />
  <email />
  <country>NL</country>
  <city>Amsterdam</city>
</Location>

But I would want phone and email to be not in the XML when they are empty.

I tried using IF EXISTS and IF NOT NULL, but this is not working. Anyone got any suggestions?

What if your query returns more than one row and some phones are empty and others aren't? — Tab Alleman
– Tab Alleman, Commented Feb 27, 2015 at 13:42
This is part of the XML and only included once, but to answer your question; if some Locations have phone and others don't, then I want to include the phone in the results that have it and leave the phone tag out of the results that don't have it. — RobNemoo
– RobNemoo, Commented Feb 27, 2015 at 14:03

Rhys Jones · Accepted Answer · 2015-02-27 13:53:35Z

3

My guess is you have empty strings instead of NULL values. This shows the difference;

;with cte as (
select 'John' as [LocationName], '0123456789' as [LocationPhone], '[email protected]' as [LocationEmail],'UK' as [LocationCountry],'London' as [LocationCity]
union all
select 'Peter' as [LocationName], '' as [LocationPhone], '' as [LocationEmail],'UK' as [LocationCountry],'Sheffield' as [LocationCity]
union all
select 'Sally' as [LocationName], null as [LocationPhone], null as [LocationEmail],'UK' as [LocationCountry],'Reading' as [LocationCity]
)
 SELECT    [LocationName] as name
          ,[LocationPhone] as phone
          ,[LocationEmail] as email
          ,[LocationCountry] as country
          ,[LocationCity] as city
    FROM cte
    FOR XML PATH ('Location'), ELEMENTS, TYPE

You can use NULLIF to change empty strings into NULLs and they will disappear from the XML;

;with cte as (
select 'John' as [LocationName], '0123456789' as [LocationPhone], '[email protected]' as [LocationEmail],'UK' as [LocationCountry],'London' as [LocationCity]
union all
select 'Peter' as [LocationName], '' as [LocationPhone], '' as [LocationEmail],'UK' as [LocationCountry],'Sheffield' as [LocationCity]
union all
select 'Sally' as [LocationName], null as [LocationPhone], null as [LocationEmail],'UK' as [LocationCountry],'Reading' as [LocationCity]
)
 SELECT    nullif([LocationName], '') as name
          ,nullif([LocationPhone], '') as phone
          ,nullif([LocationEmail], '') as email
          ,nullif([LocationCountry], '') as country
          ,nullif([LocationCity], '') as city
    FROM cte
    FOR XML PATH ('Location'), ELEMENTS, TYPE

answered Feb 27, 2015 at 13:53

Rhys Jones

5,5361 gold badge25 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RobNemoo Over a year ago

Thanks Rhys, will give this one a try.

RobNemoo Over a year ago

You were right Rhys, I had indeed empty strings instead of NULL. This works like a charm. Thanks a lot. :)

Collectives™ on Stack Overflow

Including field in XML output if not empty, leave out when empty

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related