Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting XML to varchar(max) Expand / Collapse
Author
Message
Posted Tuesday, February 24, 2009 3:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 1:38 PM
Points: 13, Visits: 66
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
Post #663872
Posted Tuesday, February 24, 2009 9:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 1:38 PM
Points: 13, Visits: 66
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
Post #663984
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse