Assigning an XML query result in SQL Server to an XML type variable produces an error

Question

SQL: 2008 Compatability: SQL 2008 OS: Vista The following query works just fine and produces an XML:

with Q1 as
    (select Job, Site from JJobs where Job > 602700)
select * from Q1 where Job = 602720
    for xml path('Detail'), type

I need to put this into a function that returns an XML variable so, I change to:

declare @xOut XML;
set @xOut =
with Q1 as
    (select Job, Site from JJobs where Job > 602700)
select * from Q1 where Job = 602720
    for xml path('Detail'), type

This produces the error: Incorrect syntax near the keyword 'with'. The query works but the assignment errors and indicates a problem with the query. Any ideas?

Mikael Eriksson · Accepted Answer · 2014-01-03 21:14:02Z

2

declare @xOut XML;

with Q1 as
(
  select Job, Site 
  from JJobs 
  where Job > 602700
)
select @xOut = (
               select * 
               from Q1 
               where Job = 602720
               for xml path('Detail'), type
               );

answered Jan 3, 2014 at 21:14

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Assigning an XML query result in SQL Server to an XML type variable produces an error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related