[Resolved] For XML Clause Invalid Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] For XML Clause Invalid Error

Author

Topic

geo039
Starting Member

8 Posts

Posted - 2011-03-29 : 17:19:43

I'm trying to send query data to an email in a table that has a union all. I keep getting "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."

I've been staring at this trying to figure out how to "wrap" it to work. Can anybody help

DECLARE @FirstDayCurrentMonth DateTime
DECLARE @LastDayNextMonth DateTime
DECLARE @LastDayPreviousMonth DateTime
DECLARE @TodaysDate VARCHAR(20) 
 
SET @TodaysDate = DateAdd(day, datediff(day,0, GETDATE()), 0)
SET @FirstDayCurrentMonth = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@TodaysDate)-1),@TodaysDate),101))
SET @LastDayNextMonth = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)))
SET @LastDayPreviousMonth = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))    
DECLARE @tableHTML NVARCHAR(MAX)

SET @tableHTML = N'<H1>Employee Evaluations Due</H1>' + 
N'<table border="1">' +
    N'<tr><th>SECTION</th><th>COST CENTER</th><th>COST CENTER SUPV</th><th>SUPERVISOR</th><th>EMPLOYEE</th><th>STATUS</th><th>EVAL TYPE</th>' +
    N'<th>EVAL DATE</th>' +
    N'</tr>' +
CAST ( ( Select
                  TD = descSection, '',
                  TD = COSTCENTER, '',
                  TD = dbo.fncEmployeeName(v.cc_sup_espn), '',
                  TD = dbo.fncEmployeeName(v.supvEspn), '',
				  TD = dbo.fncEmployeeName(v.ESPN), '',
				  TD = Case when descEmpStatus = 'Permanent' THEN 'Annual' End, '',
				  TD = descEmpStatus, '',
				  TD = Convert(Varchar,EVALDATE,101), ''
            FROM dbo.vCEDEmployees v
JOIN CodesCostCenters c
ON v.cdcstcntr = c.cdcstcntr
JOIN CodesSections s
ON c.cdSection = s.cdSection
WHERE 
DateAdd(day, datediff(day,0, dbo.fncGetEvaluationDate(v.espn)), 0) BETWEEN @FirstDayCurrentMonth AND @LastDayNextMonth
AND descEmpStatus = 'Permanent'
AND cdEmpStatus IN (1, 2)
AND cdEmpType = 1

UNION ALL

SELECT 
	  descSection as 'SECTION',
	  COSTCENTER,
	  dbo.fncEmployeeName(v.ESPN) as EMPLOYEE, 
	  dbo.fncEmployeeName(v.supvEspn) as SUPERVISOR,
	  v.supvusername as 'SUPVUSERNAME',
	  dbo.fncEmployeeName(v.cc_sup_espn) as COSTCENTERSUPV,
	   'EVALDATE' = dbo.fncGetEvaluationDate(v.espn),
	    descEmpStatus as 'EMPLOYEESTATUS',
	  'EVALTYPE' = 
	  CASE
		WHEN descEmpStatus = 'Probation' THEN 'Probation'
	  END	
FROM dbo.vCEDEmployees v
   
JOIN CodesCostCenters c
ON v.cdcstcntr = c.cdcstcntr
JOIN CodesSections s
ON c.cdSection = s.cdSection
WHERE 
 
DateAdd(day, datediff(day,0, dbo.fncGetEvaluationDate(v.espn)), 0) BETWEEN @TodaysDate AND @LastDayNextMonth
AND descEmpStatus = 'Probation'
AND cdEmpStatus IN (1, 2)
AND cdEmpType = 1

ORDER BY 'EVALTYPE' DESC 
      FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

 EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'name_SQLServerMailProfile',
@recipients='email',
    @subject = 'subject',
    @body = @tableHTML,
    @body_format = 'HTML' ;

END
GO

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-29 : 18:13:03

You need to take the union all out of the cast and put it into a subquery or CTE and then use the result of the subquery/CTE with the XML clause. The general idea is something like this (Not tested)

DECLARE @FirstDayCurrentMonth DateTime
DECLARE @LastDayNextMonth DateTime
DECLARE @LastDayPreviousMonth DateTime
DECLARE @TodaysDate VARCHAR(20) 
 
SET @TodaysDate = DateAdd(day, datediff(day,0, GETDATE()), 0)
SET @FirstDayCurrentMonth = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@TodaysDate)-1),@TodaysDate),101))
SET @LastDayNextMonth = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)))
SET @LastDayPreviousMonth = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))    
DECLARE @tableHTML NVARCHAR(MAX)

; with CTE as --Pull out everything in the select-union to a CTE
(
Select
                  TD = descSection, '',
                  TD = COSTCENTER, '',
                  TD = dbo.fncEmployeeName(v.cc_sup_espn), '',
                  TD = dbo.fncEmployeeName(v.supvEspn), '',
				  TD = dbo.fncEmployeeName(v.ESPN), '',
				  TD = Case when descEmpStatus = 'Permanent' THEN 'Annual' End, '',
				  TD = descEmpStatus, '',
				  TD = Convert(Varchar,EVALDATE,101), ''
            FROM dbo.vCEDEmployees v
JOIN CodesCostCenters c
ON v.cdcstcntr = c.cdcstcntr
JOIN CodesSections s
ON c.cdSection = s.cdSection
WHERE 
DateAdd(day, datediff(day,0, dbo.fncGetEvaluationDate(v.espn)), 0) BETWEEN @FirstDayCurrentMonth AND @LastDayNextMonth
AND descEmpStatus = 'Permanent'
AND cdEmpStatus IN (1, 2)
AND cdEmpType = 1

UNION ALL

SELECT 
	  descSection as 'SECTION',
	  COSTCENTER,
	  dbo.fncEmployeeName(v.ESPN) as EMPLOYEE, 
	  dbo.fncEmployeeName(v.supvEspn) as SUPERVISOR,
	  v.supvusername as 'SUPVUSERNAME',
	  dbo.fncEmployeeName(v.cc_sup_espn) as COSTCENTERSUPV,
	   'EVALDATE' = dbo.fncGetEvaluationDate(v.espn),
	    descEmpStatus as 'EMPLOYEESTATUS',
	  'EVALTYPE' = 
	  CASE
		WHEN descEmpStatus = 'Probation' THEN 'Probation'
	  END	
FROM dbo.vCEDEmployees v
   
JOIN CodesCostCenters c
ON v.cdcstcntr = c.cdcstcntr
JOIN CodesSections s
ON c.cdSection = s.cdSection
WHERE 
 
DateAdd(day, datediff(day,0, dbo.fncGetEvaluationDate(v.espn)), 0) BETWEEN @TodaysDate AND @LastDayNextMonth
AND descEmpStatus = 'Probation'
AND cdEmpStatus IN (1, 2)
AND cdEmpType = 1


) --Then us that CTE instead of the query with the union 
select @tableHTML = N'<H1>Employee Evaluations Due</H1>' + 
N'<table border="1">' +
    N'<tr><th>SECTION</th><th>COST CENTER</th><th>COST CENTER SUPV</th><th>SUPERVISOR</th><th>EMPLOYEE</th><th>STATUS</th><th>EVAL TYPE</th>' +
    N'<th>EVAL DATE</th>' +
    N'</tr>' +
CAST ( ( select * from CTE 

ORDER BY 'EVALTYPE' DESC 
      FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

 EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'name_SQLServerMailProfile',
@recipients='email',
    @subject = 'subject',
    @body = @tableHTML,
    @body_format = 'HTML' ;

END
GO

geo039
Starting Member

8 Posts

Posted - 2011-03-30 : 11:17:09

Thanks did a few mods and it worked. I had to move the TD out of the union select statement and put it down into the cast select portion from the CTE otherwise it yells the columns don't match

Now that I have an emailed table, I get to figure out how to get a cursor to loop thru and email only the portions of the table where a supervisor has something due so I may be posting again once I get to hair pulling

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources