SQL Server - XML - Error - "The FOR XML clause is not allowed in a INSERT statement"

When we try to insert into column of XML datatype,the result set from (SELECT * FROM TABLE FOR XML RAW/AUTO/PATH),we will end up with following error:

Msg 6819, Level 16, State 1, Line 1
The FOR XML clause is not allowed in a INSERT statement.

Two methods to overcome the above error :

--Sample data

CREATE TABLE TEST_TABLE(Col1 INT IDENTITY(1,1),Col2 VARCHAR(20))
INSERT TEST_TABLE SELECT 'SQL Server 2000'
INSERT TEST_TABLE SELECT 'SQL Server 2005'
INSERT TEST_TABLE SELECT 'SQL Server 2008'
INSERT TEST_TABLE SELECT 'SQL Server 2008R2'
INSERT TEST_TABLE SELECT 'SQL Server 2012'
 


SELECT * FROM TEST_TABLE
SELECT * FROM TEST_TABLE FOR XML AUTO,TYPE

CREATE TABLE TEST_XML(Column_XML XML)

--let us try to insert
 
INSERT TEST_XML SELECT * FROM TEST_TABLE FOR XML AUTO

Msg 6819, Level 16, State 1, Line 1
The FOR XML clause is not allowed in a INSERT statement.

--again,let us try to insert

INSERT TEST_XML SELECT * FROM TEST_TABLE FOR XML AUTO,TYPE

Msg 6819, Level 16, State 1, Line 1
The FOR XML clause is not allowed in a INSERT statement.



Method 1:

DECLARE @VAR VARCHAR(MAX) = 'SELECT * FROM TEST_TABLE FOR XML AUTO,TYPE'
INSERT TEST_XML EXEC (@Var)

SELECT * FROM TEST_XML

Method 2:

INSERT TEST_XML SELECT(SELECT * FROM TEST_TABLE FOR XML AUTO)

SELECT * FROM TEST_XML