SQL Server –Error – The FOR XML clause is not allowed in a INSERT statement. – Returning the result of a FOR XML, ELEMENTS in a dynamic query to a variable

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:-

image

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

Advertisements
  1. #1 by Carl Nestingen on August 19, 2011 - 12:28 am

    Here is a simpler solution from http://efreedom.com/Question/1-2353368/Insert-XML-SQL-Table-XML-Column

    DECLARE @tempTable TABLE
    (
    xmlValue xml
    )

    INSERT @tempTable (xmlValue)
    SELECT
    (
    SELECT EmployeeName, EmployeeSalary
    FROM Employee2
    FOR XML PATH(‘EmployeeDetails’)
    )

  2. #2 by ashishmgupta on November 29, 2011 - 12:45 am

    Thanks Carl. Will have a look.

  3. #3 by dda on January 11, 2012 - 1:04 am

    Perfect! Thanks.

  4. #4 by Suresh on September 20, 2012 - 3:33 pm

    Great solution..thanks dude..

  5. #5 by Madhusudan on October 22, 2013 - 11:13 am

    Thanks, Great Solution

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands

%d bloggers like this: