February 24, 2009 at 3:24 pm
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."
Thanks
venm
February 24, 2009 at 9:11 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply