use [Amozesh]
go
SELECT 1 AS Tag, 0 AS Parent,
'P' as 'Member!1!Category'
,'S' as 'Member!1!OrgActKind'
,replace(replace([Amozesh].[dbo].[fsf].[stcode],'m',''),'b','') as 'Member!1!MemberNum'
,replace(replace([Amozesh].[dbo].[fsf].[stcode],'m',''),'b','') as 'Member!1!BarCode'
,replace(replace([Amozesh].[dbo].[fsf].[stcode],'m',''),'b','') as 'Member!1!StudentNum'
,[Amozesh].[dbo].[mobile].[mobile] as 'Member!1!SmsNo'
,replace([Amozesh].[dbo].[fsf2].[date_tav],'/','') as 'Member!1!BirthDate'
,[family] as 'Member!1!Name'
,[name] as 'Member!1!FirstName'
,[namep] as 'Member!1!FatherName'
,[idd] as 'Member!1!GenNote'
,[idd_meli] as 'Member!1!NationNum'
,(case When Rtrim(ltrim([idd_meli])) = '' Then Null else [idd_meli] End) as 'Member!1!Pass'
,(case [sex] When 1 then 'M' When 2 then 'F' End ) as 'Member!1!Sex' ,addressd as 'Member!1!Address'
,tel as 'Member!1!PhoneNum'
,magh as 'Member!1!Grade'
,idresh as 'Member!1!Course'
,'13920101' as 'Member!1!MStartDate' -- جهت تاریخ شروع عضویت
,(case [code_posti] when '' then null when null then null else ('کد پستی: ' + code_posti) end) as 'Member!1!AddrNote'
,magh as 'Member!1!DefaultP'
,replace(replace([Amozesh].[dbo].[fsf].[stcode],'m',''),'b','') AS [Member!1!Key], NULL AS [Photo!2!!CDATA]
FROM [amozesh].[dbo].[fsf] inner join fsf2 on [Amozesh].[dbo].[fsf].[stcode] = [Amozesh].[dbo].[fsf2].[stcode]
inner join [amozesh].[dbo].[mobile] on [Amozesh].[dbo].[mobile].[stcode] = [Amozesh].[dbo].[fsf].[stcode]
where ([Amozesh].[dbo].[fsf].[idvazkol] = 1 or [Amozesh].[dbo].[fsf].[idvazkol] = 2) -- جهت دانشجویان فعال
-- and ([Amozesh].[dbo].[fsf].[stcode] like '95%') -- جهت دانشجویان مربوط به سال و ترم ورودی
UNION ALL
SELECT
2,
1,
null as 'Member!1!Category', --1
null as 'Member!1!OrgActKind',
null as 'Member!1!MemberNum',
null as 'Member!1!BarCode',
null as 'Member!1!StudentNum',
null as 'Member!1!SmsNo',
null as 'Member!1!BirthDate' ,
null as 'Member!1!Name',
null as 'Member!1!FirstName',
null as 'Member!1!FatherName',
null as 'Member!1!GenNote',
null as 'Member!1!NationNum',
null as 'Member!1!Pass',
null as 'Member!1!Sex',
null as 'Member!1!Address',
null as 'Member!1!PhoneNum',
null as 'Member!1!Grade', --15
null as 'Member!1!Course',
null as 'Member!1!MStartDate', --17
null as 'Member!1!AddrNote',
null as 'Member!1!DefaultP' ,
replace(replace([amozpic].[dbo].[stuimage].[stcode],'m',''),'b','') AS [Member!1!Key],
[amozpic].[dbo].[stuimage].[stu_pic] AS [Photo!2!!CDATA]
FROM [amozpic].[dbo].[stuimage]
where ([amozpic].[dbo].[stuimage].[stcode] like '96%')
order by [Member!1!Key] , Parent
FOR XML EXPLICIT, BINARY BASE64,ROOT('Members')