Convert XML output to varchar

Question

I have this query

select 
    ',' + CONVERT(VARCHAR,FileID) 
from 
    [RETRY_INPUT_JSON] A 
inner join 
    RETRY_JSON_STATUS B on JsonStatus = StatusID
where 
    RetryStatus = 'FAILED'          

Union

select 
    ',' + CONVERT(VARCHAR,FileID)   
from 
    [RETRY_INPUT_JSON] A 
inner join 
    RETRY_JSON_STATUS B on SendESBStatus = StatusID
where 
    RetryStatus In ('FAILED', 'Success')            
order by
    ',' + CONVERT(VARCHAR,FileID) 
FOR XML PATH('')

This returns this output:

I want to convert it to varchar.

For that I tried :

DECLARE @FILEID AS VARCHAR(MAX)

        SET @FILEID =(SELECT STUFF
                        (
                            (
select ',' + CONVERT(VARCHAR,FileID) from [RETRY_INPUT_JSON] A inner join RETRY_JSON_STATUS B
        on JsonStatus=StatusID
        where RetryStatus='FAILED'          
        Union
        select ',' + CONVERT(VARCHAR,FileID)   from [RETRY_INPUT_JSON] A inner join RETRY_JSON_STATUS B
        on SendESBStatus=StatusID
        where RetryStatus In ('FAILED','Success')           
        ORDER BY ',' + CONVERT(VARCHAR,FileID) FOR XML PATH('') 
    ),
                             1, 1, ''
                        ) )

print @FILEID

But I get an error :

Msg 1086, Level 15, State 1, Line 18
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.

Answer 1

Check This.

        DECLARE @FILEID AS VARCHAR(MAX)
        DECLARE @xml_var XML 

        SET @xml_var = ( select ',' + CONVERT(VARCHAR,A)   
                            from
                                (

                                        select 
                                        CONVERT(VARCHAR,FileID) A
                                        from 
                                        [RETRY_INPUT_JSON] A 
                                        inner join 
                                        RETRY_JSON_STATUS B on JsonStatus = StatusID
                                        where 
                                        RetryStatus = 'FAILED'     

                                Union

                                        select 
                                        CONVERT(VARCHAR,FileID)   A
                                        from 
                                        [RETRY_INPUT_JSON] A 
                                        inner join 
                                        RETRY_JSON_STATUS B on SendESBStatus = StatusID
                                        where 
                                        RetryStatus In ('FAILED', 'Success')            


                                )A   
                                order by A 
                                FOR XML PATH('') 
                        )

        select @FILEID = CONVERT(VARCHAR(MAX), @xml_var)
        SELECT @FILEID
        pRINT @FILEID