Problem :-

When you try to execute a dynamic query having the FOR XML AUTO like this (ignore the query itself for now, you can find the complete script at the end of this article):-

DECLARE @Data2 TABLE(col xml)
DECLARE @Query nvarchar(max)
SELECT @Query= 'SELECT Customer.CustomerID,Address.FullAddress,Phone.PhoneNumber
FROM #tempCustomer Customer
JOIN #tempAddress Address ON Address.CustomerID= Customer.CustomerID
JOIN #tempPhone Phone ON Phone.CustomerID= Customer.CustomerID
FOR XML AUTO, ELEMENTS'
INSERT @Data2 exec (@Query)

You get an error like the following

"The FOR XML clause is not allowed in a INSERT statement.”

From http://msdn.microsoft.com/en-us/library/aa226520(SQL.80).aspx:-

The above is applicable for SQL server 2005 as well. Very annoying…

However, if you have something like below, the query would get executed successfully:-

SELECT @Query= 'SELECT ( SELECT Customer.CustomerID,Address.FullAddress,Phone.PhoneNumber
FROM #tempCustomer Customer
JOIN #tempAddress Address ON Address.CustomerID= Customer.CustomerID
JOIN #tempPhone Phone ON Phone.CustomerID= Customer.CustomerID
FOR XML AUTO, ELEMENTS ) '

 

Notice that I prefixed the query with ‘SELECT(‘ and suffixed it with ‘)’. This would allow the query to execute but to return the data in text format rather than XML. However, you can have the result stored in a column of XML data type of a temporary table/variable and It would appear as if the result was XML.

So we need to stuff the prefix and then the suffix in the query. Here is where the STUFF function would come to rescue. It is different from REPLACE function as it allows you to replace a specific instance of a character for a length.

The complete query :- (Just run it)

/* Temporary table to hold sample data. Forgive me for not using the table variable as they would be out of scope when I would use them in the dynamic query*/
if OBJECT_ID('tempdb..#tempCustomer') is not null
BEGIN
DROP TABLE #tempCustomer
END
CREATE TABLE #tempCustomer (CustomerID INT)

if OBJECT_ID('tempdb..#tempAddress') is not null
BEGIN
DROP TABLE #tempAddress
END
CREATE TABLE #tempAddress (CustomerID INT,FullAddress VARCHAR(100))

if OBJECT_ID('tempdb..#tempPhone') is not null
BEGIN
DROP TABLE #tempPhone
END
CREATE TABLE #tempPhone (CustomerID INT,PhoneNumber VARCHAR(100))

/* Insert sample data*/
INSERT #tempCustomer VALUES(1)
INSERT #tempAddress VALUES(1,'Some Address')
INSERT #tempPhone VALUES(1,'212-111-2222')
INSERT #tempCustomer VALUES(2)
INSERT #tempAddress VALUES(2,'Other Address')
INSERT #tempPhone VALUES(2,'212-777-8888')

/* Build the dynamic query*/
DECLARE @Data2 TABLE(col xml)
DECLARE @Result XML
DECLARE @Query nvarchar(max)
SELECT @Query= 'SELECT Customer.CustomerID,Address.FullAddress,Phone.PhoneNumber
FROM #tempCustomer Customer
JOIN #tempAddress Address ON Address.CustomerID= Customer.CustomerID
JOIN #tempPhone Phone ON Phone.CustomerID= Customer.CustomerID
FOR XML AUTO, ELEMENTS'

/* There can be many 'SELECT' in the query, we need to get the last one as that would be the one
which is having the FOR XML AUTO, ELEMENTS .
So, reverse the string and get the first index of the 'select'
and then stuff the reverse of ‘SELECT (‘ and ‘)’ and reverse the result again to get the correct
string*/
--PRINT @Query
SELECT @Query=REVERSE(  @Query)
--PRINT @Query
DECLARE @FirstIndexOfSelect INT
SELECT @FirstIndexOfSelect = CHARINDEX(REVERSE('SELECT') , @Query )
--PRINT @FirstIndexOfSelect
SELECT @Query =STUFF(@Query,@FirstIndexOfSelect,6,REVERSE('SELECT (SELECT'))
SELECT @Query=REVERSE(@Query)+')'
--PRINT @Query
INSERT @Data2 exec (@Query)
SELECT @Result =Col FROM @Data2
SELECT @Result