<div>
Here is my SQL: 
@TblFind is my temp table 
 
-------------------------------------------------------- 
 
DECLARE @WebSiteXmlData As varchar(max) 
 
select @WebSiteXmlData = 
 
( 
select &nbsp;1 As Tag, 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null As Parent, 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '2.0' as [rss!1!version], 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [channel!2!title!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [channel!2!link!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [channel!2!description!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [channel!2!pubdate!element], 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [item!3!title!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [item!3!description!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [item!3!link!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [item!3!pubdate!element] &nbsp; &nbsp; 
union 
 
select &nbsp; &nbsp; &nbsp;2 As Tag, 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 As Parent, 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [rss!1!version], 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'FullDescription' as [channel!2!title!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Domainname' as [channel!2!link!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'LongDescription' as [channel!2!description!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'null' as [channel!2!pubdate!element], 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [item!3!title!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [item!3!description!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [item!3!link!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [item!3!pubdate!element] &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; from publication 
&nbsp; &nbsp; &nbsp; &nbsp; Where PublicationID = &nbsp;@SourceAreaId 
&nbsp; 
union 
 
select &nbsp; &nbsp; &nbsp;3 As Tag, 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 As Parent, 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [rss!1!version], 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [channel!2!title!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [channel!2!link!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [channel!2!description!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [channel!2!pubdate!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ISNULL(( SELECT TOP 1 CASE WHEN LEN(CDA.AttribValue) &gt;&nbsp;27 THEN LEFT(CDA.AttribValue, 27) + '...' ELSE CDA.AttribValue END 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM AOE_ContentFilter CF (NOLOCK) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; JOIN AOE_ContentDocAttrib CDA (NOLOCK) ON CF.ContentFilterId = CDA.ContentFilterId 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; JOIN AOE_ContentDocAttribType DAT (NOLOCK) ON CDA.ContentDocAttribTypeId = DAT.ContentDocAttribTypeId 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE CF.ContentDocumentHistId = CD.LastContentDocumentHistId AND 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CF.ContentFilterMasterId = F.ContentFilterMasterId AND 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DAT.AttribTypeKey = 'TITLE' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ORDER BY CF.CreateDate ), '') as [item!3!title!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ISNULL(( SELECT TOP 1 CASE WHEN LEN(CDA.AttribValue) &gt;&nbsp;27 THEN LEFT(CDA.AttribValue, 27) + '...' ELSE CDA.AttribValue END 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM AOE_ContentFilter CF (NOLOCK) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; JOIN AOE_ContentDocAttrib CDA (NOLOCK) ON CF.ContentFilterId = CDA.ContentFilterId 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; JOIN AOE_ContentDocAttribType DAT (NOLOCK) ON CDA.ContentDocAttribTypeId = DAT.ContentDocAttribTypeId 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE CF.ContentDocumentHistId = CD.LastContentDocumentHistId AND 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CF.ContentFilterMasterId = F.ContentFilterMasterId AND 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DAT.AttribTypeKey = 'BLURB' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ORDER BY CF.CreateDate ), '') as [item!3!description!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ISNULL(( SELECT TOP 1 UR.FakeWebPath 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM MWU_UrlRewrite UR (NOLOCK) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE UR.RelatedTable = 'AOE_ContentFilterMaster' AND UR.RelatedId = F.ContentFilterMasterId 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),'') &nbsp;as [item!3!link!element], 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null as [item!3!pubdate!element] &nbsp; &nbsp; 
FROM @TblFind F 
&nbsp; &nbsp; JOIN AOE_ContentDocument CD (NOLOCK) ON F.ContentDocumentId = CD.ContentDocumentId 
 
for xml explicit 
) 

</div>

<div>
try this 
 
But you can't store the value in the @WebSiteXmlData 
because the for xml clause may return more than one rows 
so you will have a problem 

<pre><code class="code-10 nolinks" id="code-259544">SELECT * 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(( SELECT TOP 1 CASE WHEN LEN(CDA.AttribValue) &gt; 27 THEN LEFT(CDA.AttribValue, 27) + '...' ELSE CDA.AttribValue END
 FROM AOE_ContentFilter CF (NOLOCK)
 JOIN AOE_ContentDocAttrib CDA (NOLOCK) ON CF.ContentFilterId = CDA.ContentFilterId
 JOIN AOE_ContentDocAttribType DAT (NOLOCK) ON CDA.ContentDocAttribTypeId = DAT.ContentDocAttribTypeId
 WHERE CF.ContentDocumentHistId = CD.LastContentDocumentHistId AND
 CF.ContentFilterMasterId = F.ContentFilterMasterId AND
 DAT.AttribTypeKey = 'TITLE'
 ORDER BY CF.CreateDate ), '') as [item!3!title!element],
 ISNULL(( SELECT TOP 1 CASE WHEN LEN(CDA.AttribValue) &gt; 27 THEN LEFT(CDA.AttribValue, 27) + '...' ELSE CDA.AttribValue END
 FROM AOE_ContentFilter CF (NOLOCK)
 JOIN AOE_ContentDocAttrib CDA (NOLOCK) ON CF.ContentFilterId = CDA.ContentFilterId
 JOIN AOE_ContentDocAttribType DAT (NOLOCK) ON CDA.ContentDocAttribTypeId = DAT.ContentDocAttribTypeId
 WHERE CF.ContentDocumentHistId = CD.LastContentDocumentHistId AND
 CF.ContentFilterMasterId = F.ContentFilterMasterId AND
 DAT.AttribTypeKey = 'BLURB'
 ORDER BY CF.CreateDate ), '') as [item!3!description!element],
 ISNULL(( SELECT TOP 1 UR.FakeWebPath
 FROM MWU_UrlRewrite UR (NOLOCK)
 WHERE UR.RelatedTable = 'AOE_ContentFilterMaster' AND UR.RelatedId = F.ContentFilterMasterId
 ),'') as [item!3!link!element],
 null as [item!3!pubdate!element] 
FROM @TblFind F
 JOIN AOE_ContentDocument CD (NOLOCK) ON F.ContentDocumentId = CD.ContentDocumentId
 
) t
FOR XML explicit
</code></pre>
</div>

<div>
Try casting it as nvarchar(max) after the XML is generated. 
 

<pre><code class="code-10 nolinks" id="code-259571">
DECLARE @WebSiteXmlData As varchar(max)
 
select @WebSiteXmlData =
 
cast((
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(( SELECT TOP 1 CASE WHEN LEN(CDA.AttribValue) &gt; 27 THEN LEFT(CDA.AttribValue, 27) + '...' ELSE CDA.AttribValue END
 FROM AOE_ContentFilter CF (NOLOCK)
 JOIN AOE_ContentDocAttrib CDA (NOLOCK) ON CF.ContentFilterId = CDA.ContentFilterId
 JOIN AOE_ContentDocAttribType DAT (NOLOCK) ON CDA.ContentDocAttribTypeId = DAT.ContentDocAttribTypeId
 WHERE CF.ContentDocumentHistId = CD.LastContentDocumentHistId AND
 CF.ContentFilterMasterId = F.ContentFilterMasterId AND
 DAT.AttribTypeKey = 'TITLE'
 ORDER BY CF.CreateDate ), '') as [item!3!title!element],
 ISNULL(( SELECT TOP 1 CASE WHEN LEN(CDA.AttribValue) &gt; 27 THEN LEFT(CDA.AttribValue, 27) + '...' ELSE CDA.AttribValue END
 FROM AOE_ContentFilter CF (NOLOCK)
 JOIN AOE_ContentDocAttrib CDA (NOLOCK) ON CF.ContentFilterId = CDA.ContentFilterId
 JOIN AOE_ContentDocAttribType DAT (NOLOCK) ON CDA.ContentDocAttribTypeId = DAT.ContentDocAttribTypeId
 WHERE CF.ContentDocumentHistId = CD.LastContentDocumentHistId AND
 CF.ContentFilterMasterId = F.ContentFilterMasterId AND
 DAT.AttribTypeKey = 'BLURB'
 ORDER BY CF.CreateDate ), '') as [item!3!description!element],
 ISNULL(( SELECT TOP 1 UR.FakeWebPath
 FROM MWU_UrlRewrite UR (NOLOCK)
 WHERE UR.RelatedTable = 'AOE_ContentFilterMaster' AND UR.RelatedId = F.ContentFilterMasterId
 ),'') as [item!3!link!element],
 null as [item!3!pubdate!element] 
FROM @TblFind F
 JOIN AOE_ContentDocument CD (NOLOCK) ON F.ContentDocumentId = CD.ContentDocumentId
 
for xml explicit
) as nvarchar(max))
</code></pre>
</div>

<div>
If that gives you a syntax error, which it may, assign it to an XML variable, then cast it to varchar(max): 
 

<pre><code class="code-10 nolinks" id="code-259574">DECLARE @WebSiteXmlData As varchar(max)
,@x xml
select @x =
 
(
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(( SELECT TOP 1 CASE WHEN LEN(CDA.AttribValue) &gt; 27 THEN LEFT(CDA.AttribValue, 27) + '...' ELSE CDA.AttribValue END
 FROM AOE_ContentFilter CF (NOLOCK)
 JOIN AOE_ContentDocAttrib CDA (NOLOCK) ON CF.ContentFilterId = CDA.ContentFilterId
 JOIN AOE_ContentDocAttribType DAT (NOLOCK) ON CDA.ContentDocAttribTypeId = DAT.ContentDocAttribTypeId
 WHERE CF.ContentDocumentHistId = CD.LastContentDocumentHistId AND
 CF.ContentFilterMasterId = F.ContentFilterMasterId AND
 DAT.AttribTypeKey = 'TITLE'
 ORDER BY CF.CreateDate ), '') as [item!3!title!element],
 ISNULL(( SELECT TOP 1 CASE WHEN LEN(CDA.AttribValue) &gt; 27 THEN LEFT(CDA.AttribValue, 27) + '...' ELSE CDA.AttribValue END
 FROM AOE_ContentFilter CF (NOLOCK)
 JOIN AOE_ContentDocAttrib CDA (NOLOCK) ON CF.ContentFilterId = CDA.ContentFilterId
 JOIN AOE_ContentDocAttribType DAT (NOLOCK) ON CDA.ContentDocAttribTypeId = DAT.ContentDocAttribTypeId
 WHERE CF.ContentDocumentHistId = CD.LastContentDocumentHistId AND
 CF.ContentFilterMasterId = F.ContentFilterMasterId AND
 DAT.AttribTypeKey = 'BLURB'
 ORDER BY CF.CreateDate ), '') as [item!3!description!element],
 ISNULL(( SELECT TOP 1 UR.FakeWebPath
 FROM MWU_UrlRewrite UR (NOLOCK)
 WHERE UR.RelatedTable = 'AOE_ContentFilterMaster' AND UR.RelatedId = F.ContentFilterMasterId
 ),'') as [item!3!link!element],
 null as [item!3!pubdate!element] 
FROM @TblFind F
 JOIN AOE_ContentDocument CD (NOLOCK) ON F.ContentDocumentId = CD.ContentDocumentId
 
for xml explicit
)
 
select @WebSiteXmlData = cast(@x as nvarchar(max))
</code></pre>
</div>

<div>
Thanks for the help &quot;BrandonGalderisi&quot;, But neither of them worked, both throws the same error as mentioned earlier. 
 
Thanks for your response &quot;dankangr&quot;. If I cant store the value in @WebSiteXmlData 
then how can I store xml data in a varchar(max) field? 

</div>

<div>
And again: &quot;To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.&quot; 

</div>

<div>
Exactly, that is called defining &quot;the FOR XML query as a derived table&quot;.
</div>

<div>
And this is the point where you you have received your answer and need to close your question.
</div>

<div>
Thanks &quot;acperkins&quot;, Now I understood what exactly meant by &quot;the FOR XML query as a derived table&quot;. 
 
Thanks every one for your help. 
 
 
 
 

</div>

<div>
Wiat a minute, Brandon was the one who gave you the lead to your solution. &nbsp;If you are going to award any points it should to to him.
</div>

<div>
Thank you acperkins for your honesty. :)
</div>

<div>
I am just sorry it did not help much. &nbsp;Obviously the author is overlooking that he/she may be back for more help ...
</div>

<div>
<div class="content wysiwyg-content">
I need to store my xml out put in an varchar(max) field. 
when i try to convert my xml (&quot;for xml explicit&quot;) i am getting the following error. 
any help will be greatly appreciated. 
 
Error: 
 
&quot;The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.&quot; 

</div>
</div>

I need to store my xml out put in an varchar(max) field.
when i try to convert my xml ("for xml explicit") i am getting the following error.
any help will be greatly appreciated.

Error:

"The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it."


Converting XML to varchar(max)

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.