CODEWITHSUNDEEP
sql-serverxmlsql-server-2008
ca9163d9
ca9163d9

Reputation: 29159

Convert the result of dynamic sql to xml

I want to execute the following T-sql

declare @sql varchar(max) = 'select 1 a, 2 b for xml path (''''), root(''root'')'
declare @t table (x xml)
insert into @t exec (@sql)

However, I got the error message of

Msg 6819, Level 16, State 5, Line 2

The FOR XML clause is not allowed in a INSERT statement

Upvotes: 2

Views: 794

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Embed the query that creates the XML in one extra select statement.

declare @sql varchar(max) = '
select
  (
  select 1 a,
         2 b
  for xml path (''''), root(''root'')
  )'

declare @t table (x xml)
insert into @t exec (@sql)

Upvotes: 3

Related Questions