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.
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)
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