Sql ForXml; concatenating fields returning either a comma seperated list or empty string

I have looked through several SO questions on using FOR XML but have either missed, or not found anything specific to my query.

Fortunately, or perhaps not, the service to which I need to submit data wants the fields from my table(s) submitted as attributes rather than elements which means that I can use FOR XML AUTO. However it does require some things that I'm not sure how to write the correct sql to achieve, and for which I would welcome some advice.

Some fields (let's say that I have three named Log1, Log2 and Log3) need to be returned as one attribute called for arguments sake Logs. If all three log fields are Null then it needs to be an empty string, or if they contain data they need to be concatenated with a comma as the separator. I suspect that a simple Coalesce is not the answer here, but I'm not entirely sure what else would do.

As I said I would welcome some suggestions. Thanks.

EDIT

Apologies, should have added some sql to assist.

SELECT  LandingId,
    VesselId,
    TicketNumber,
    CONVERT(VARCHAR(10),LandingDate1,112) AS landingdate1,
    Log1,
    Log2,
    Log3,
    COALESCE(VesselName,'') AS vesselName,
    VesselOwner
FROM Landings.LandingHeaders AS sale  FOR XML AUTO

Log1, Log2 and Log3 should become logs and be either an empty string if all are null or a comma separated list if they contain data.

EDIT2 (current Output with VesselName and Owner removed to preserve confidentiality)

<sale LandingId="3388" VesselId="1" TicketNumber="1         " landingdate1="20130705" />

Ass the Log values are null nothing is being returned, Not sure if I can use a form of coalesce to either produce the empty string or a comma separated list. I do know that I need to trim the ticketNumber.

Solution

It's better to use not for xml auto, but for xml path since it gives you much more flexibility.

Your desired goal (if I understood it correctly), can be achieved like this (I've skipped some fields)

select
    VesselId as '@VesselId',
    TicketNumber as '@TicketNumber',
    isnull(Log1 + ',', '') + isnull(Log2 + ',', '') + isnull(Log3 + ',', '') as '@Log123'
from Landings.LandingHeaders for xml path('sale')

update

Frankly, in your particular case you can achive your goal using for xml auto too, key idea of concatenating fields like

isnull(Log1 + ',', '') + isnull(Log2 + ',', '') + isnull(Log3 + ',', '')

to get empty string if all three fields are nulls and non-empty string with some data otherwise remains the same.