MySQL Syntax assistance

[Fraser Mathieson]

Hi

I have an extensive MsSQL database that I am migrating into MySQL. Most of the syntax differences between the two systems I am happy with, but I have the follow routine that the 'format' is used in several others.

I am struggling to get the correct syntax so can anyone assist? I have included column, table names just for reference with what is used.

I guess the main bit here is that using the for XML, which essentially pulls together a list from another table, based on a given table holding a relationship list.

Thanks

Current MySQL create code:

CREATE PROCEDURE `Advert_GetByID`(in _AdvertID bigint)
begin

SELECT
m.AdvertID,
(select cast(m2c.MagazineCategoryID as nvarchar(3)) + ',' as 'data()'
from Advert2Categories m2c
left join Advert m2 on m2c.MagazineCategoryID=m2.AdvertCategoryID where m.AdvertID=m2c.AdvertID for xml PATH('')) as 'AdvertCategoryID',
(select cast(r.RelatedArticleID as nvarchar(3)) + ',' as 'data()'
from AdvertRelatedArticles r
left join Advert m3 on r.RelatedArticleID=m3.AdvertID where m.AdvertID=r.ArticleID for xml PATH('')) as 'RelatedArticles',
(select cast(c.CountryID as nvarchar(3)) + ',' as 'data()' from Advert2Countries c
left join Advert m4 on c.AdvertID=m4.AdvertID where m.AdvertID=c.AdvertID for xml path('')) as 'Countries',
m.AdvertStartDate,
m.AdvertExpireDate,
m.AdvertTitle,
m.AdvertDescription,
m.AdvertBody,
m.AdvertStatus,
m.AdvertImageFileName,
m.YouTubeVideo,
m.FriendlyURL,
CAST(1 as bit) as 'UseImages',
m.LanguageID,
(select mc.MagazineCatName + ',' as 'data()'
from Advert2Categories m2c
left join Advert m2 on m2c.MagazineCategoryID=m2.AdvertCategoryID
left join MagazineCategories mc on m2c.MagazineCategoryID=mc.MagazineCatID
where m.AdvertID=m2c.AdvertID for xml PATH('')) as 'MagCat',
m.TimesRead,
m.Featured,
m.Lat,
m.Lng,
sau.FirstName,
sau.Surname,
sau.Username as 'Email',
m.MiigenAdId,
m.Paid,
m.PaidDate,
m.PaidAmount,
m.BusinessAccountId,
m.AdvertType,
(
select cast(m2g.AccountUserGroupsID as nvarchar(3)) + ',' as 'data()'
from Advert2Groups m2g
left join Advert m2 on m2g.AdvertID=m2.AdvertID
where m.AdvertID=m2g.AdvertID
for xml PATH('')
) as 'AdvertGroupID'
FROM
Advert m
left join Social_AccountUsers sau on m.CreatorID=sau.ID
where
m.AdvertID=_AdvertID
ORDER BY
m.AdvertStartDate DESC, m.AdvertID DESC;
end