I have the following code where I'm passing an XML string.
SET QUOTED_IDENTIFIER OFF
GO
DECLARE @xml XML =N'<Locations><LocId>USA</LocId><LocId>AUS</LocId><LocId>GER</LocId></Locations>'
CREATE TABLE #tmpLoc (LocId Varchar(100))
INSERT INTO #tmpLoc
SELECT t.c.value('.','varchar(100)')
FROM @xml.nodes('/Locations/LocId') t(c)
SELECT * FROM #tmpLoc
I get the following error when I run this query:
INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
I went through the documentation for XML data types and found that XQuery needs the QUOTED_IDENTIFIER to be ON. I set it to ON and it worked. The documentation does not give any explanation about the reason behind this. I would really appreciate it if someone could explain why the QUOTED_IDENTIFIER needs to be set to ON for this to work. Thanks!
