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.
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.