CODEWITHSUNDEEP
sqlxmlsql-server-2008
RobNemoo
RobNemoo

Reputation: 17

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

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?

Upvotes: 0

Views: 3721

Answers (1)

Rhys Jones
Rhys Jones

Reputation: 5508

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

Upvotes: 3

Related Questions