CODEWITHSUNDEEP
sql-servert-sqlunionfor-xml
gene
gene

Reputation: 2108

How to write an SQL Select for XML including Union logic?

I have the following statement to get both dates into @xmlData

declare @xmlData OUTOUT

SET @xmlData = (SELECT @FileDate AS [FileDate] UNION SELECT @satDate AS [FileDate] FOR XML RAW, ELEMENTS)

Then I will insert it into the table:

DECLARE @ListOfDates TABLE (FileDate varchar(50))

        INSERT @ListOfDates (FileDate)
        SELECT Tbl.Col.value('FileDate[1]', 'varchar(50)') 
        FROM @xmlData.nodes('//row') Tbl(Col)

When executing my select logic, I'm getting an error saying:

The FOR XML and FOR JSON clauses are 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 or common table expression or view and apply FOR XML or FOR JSON on top of it.

How to fix that?

Upvotes: 0

Views: 1605

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89191

I don't see why you're using XML at all here. But the error is telling you to push down that query into a derived table (subquery) or CTE, like this:

declare @xmlData xml
declare @filedate date = getdate()
declare @satdate date = '20140101'

SET @xmlData = (

select * from 
( SELECT @FileDate AS [FileDate] UNION ALL SELECT @satDate AS [FileDate] ) d
FOR XML RAW, ELEMENTS, type

)

select @xmlData

Upvotes: 1

Related Questions