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



 

SQL Server 2008 - Audit SELECT performed on particular table


Trigger will fire for INSERT/UPDATE/DELETE  statements,not for SELECT statement

So SQL Server Profiler was used as the only way to audit SELECT statements,prior to SQL Server 2008.

SQL Server 2008 Enterprise edition helps us to audit SELECT statement performed on a particular table as shown in below example:

Step 1:

           Under Object Explorer,create a new Audit object as shown in below image:





Step 2:

               Mention Audit name,configure Audit settings as needed and Audit log destination type and destination path,if file is selected as Audit destination.