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?


No comments:

Post a Comment