Inserting XML field from query error - QUOTED_IDENTIFIER ON does not fix-CodePudding

I need to insert a number of rows into a table using a query from another table. This issue I have is that one of the columns I am inserting is XML (in which I need to embed a value from my query) but when I try and run the insert statement in SSMS I get

Msg 1934 INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

I have tried setting QUOTED_IDENTIFIER ON but I still get the same error. I tried it within the batch and in a separate batch (the SET statement followed by GO, before the Insert statement.

I have no indexed view, filtered indexes or computed column in this table. Looking at the table properties I see Quoted Identifier is True.

Here is the essence of what I am trying to do. The columns Area, Action and EntityType are int, Data is XML and Title is nvarchar(20).

INSERT INTO AuditLog ( [Area], [Action], [EntityType], [Data] )
  SELECT (1,2,1,'<Name>'   [Title]   '<\Name>')
     FROM [Records] WHERE [TypeId] = 3;

I am able to create an XML variable and use this but I need to be able to insert multiple records and don't really want to resort to a cursor to do something that looks so simple. Even if I use simple static quoted XML or use CAST or CONVERT I have the same error.

I am running on SQL Server Standard 2016 with the latest SSMS (2019). All suggestions are gratefully received.

CodePudding user response：

Please try the following conceptual example.

XML data type cannot be treated as VARCHAR/NVARCHAR data types.

The desired XML could be easily composed via SELECT ... FOR XML ...

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Area INT, Title XML);
INSERT INTO @tbl (Area, Title) VALUES
(770, N'<root>Manager</root>');

DECLARE @target TABLE (ID INT IDENTITY PRIMARY KEY, Area INT, [DATA] XML);
-- DDL and sample data population, end

INSERT INTO @target (Area, [DATA])
SELECT Area
    , (
    SELECT title AS [Name]
    FOR XML PATH(''), TYPE
    ) AS [DATA]
FROM @tbl;

-- test
SELECT * FROM @target;

Output

 ---- ------ ----------------------------------- 
| ID | Area |               DATA                |
 ---- ------ ----------------------------------- 
|  1 |  770 | <Name><root>Manager</root></Name> |
 ---- ------ -----------------------------------

CodePudding user response：

I have resolved the issue, but I was initially unsure why it worked when little had changed but eventually, I figured out that I was running the query under the context of a database that had QUOTED_IDENTIFIER OFF (this was where the source data was located) whereas the database where the results were being inserted had QUOTED_IDENTIFIER ON, so changing the context of the execution (an Agent Job Step) fixed the issue. The source database is part of a third-party application so changing the QUOTED_IDENTIFER setting would have unknown consequences.