This is how I got it working.
need 2 variables @X as Xml &@WebSiteXmlData as varchar(max)
@X will give you the xml output & @WebSiteXmlData will give the varchar output.
We need have another outer select & apply "for Xml explicit" to this outer select.
Assign everything to an 'Xml' variable & convert that to 'varchar'. with this way we can store the all the symbals of xml ( , </) as is.
---------------------
DECLARE @WebSiteXmlData As varchar(max)
Declare @X as Xml
set @X =
(Select Tag, Parent, [rss!1!version], [channel!2!title!element], [channel!2!link!element], [channel!2!description!element],
[channel!2!pubdate!element],[item!3!title!element],[item!3!description!element],[item!3!link!element],[item!3!pubdate!element] from
(select 1 As Tag,
null As Parent,
'2.0' as [rss!1!version],
null as [channel!2!title!element],
null as [channel!2!link!element],
null as [channel!2!description!element],
null as [channel!2!pubdate!element],
null as [item!3!title!element],
null as [item!3!description!element],
null as [item!3!link!element],
null as [item!3!pubdate!element]
union
select 2 As Tag,
1 As Parent,
null as [rss!1!version],
'FullDescription' as [channel!2!title!element],
'Domainname' as [channel!2!link!element],
'LongDescription' as [channel!2!description!element],
'null' as [channel!2!pubdate!element],
null as [item!3!title!element],
null as [item!3!description!element],
null as [item!3!link!element],
null as [item!3!pubdate!element]
from publication
Where PublicationID = @SourceAreaId
union
select 3 As Tag,
2 As Parent,
null as [rss!1!version],
null as [channel!2!title!element],
null as [channel!2!link!element],
null as [channel!2!description!element],
null as [channel!2!pubdate!element],
ISNULL(( Title ), '') as [item!3!title!element],
ISNULL(( [Description]), '') as [item!3!description!element],
ISNULL(( Link
),'') as [item!3!link!element],
null as [item!3!pubdate!element]
FROM @TblFind F
JOIN AOE_ContentDocument CD (NOLOCK) ON F.ContentDocumentId = CD.ContentDocumentId
) as TmpTable
Order by 1,2,3 for xml explicit)
select @X
select @WebSiteXmlData = cast(@x as nvarchar(max))
select @WebSiteXmlData