SQL Server XML Data Type and QUOTED_IDENTIFIER

Question

Can anyone provide insight into this?

I've developed an import process using an XML data type. After the data is inserted in to a table by the import sProc I run another procedures to update another table with the imported table.

The update procedure throws an exception if it is created with SET QUOTED_IDENTIFIER OFF. I'd like to understand why that is happening.

Here's the code:

DECLARE @xmlRecords XML
SET     @xmlRecords = (SELECT importedXML FROM importTable WHERE importId = @lastImportId)

UPDATE  o
SET     o.ReferralCode = import.refCode
FROM    (
            SELECT  records.record.value('(@orderId)[1]', 'INT') AS orderId, 
                    records.record.value('(@refCode)[1]', 'VARCHAR(15)') AS refCode
            FROM    @xmlRecords.nodes('/records/record') records(record)
            ) import 
            INNER JOIN tblOrder o ON import.OrderId = o.orderId

I'm assuming it has to do with the quoted datatypes ('VARCHAR(15)') or the xml query path elements ('/records/record').

Thanks for any insight you can provide.

I

Answer 1

A very simple test case

set quoted_identifier off


DECLARE @xmlRecords XML
SET     @xmlRecords = '<records><record orderId="1" refCode="1234"></record></records>'


SELECT  records.record.value('(@orderId)[1]', 'INT') AS orderId
FROM    @xmlRecords.nodes('/records/record') records(record)

Gives

Msg 1934, Level 16, State 1, Line 8 SELECT 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 query notifications and/or xml data type methods.

This is documented in passing here

Executing XQuery and XML data modification statements requires that the connection option QUOTED_IDENTIFIER be ON.

I haven't seen a reason why this is a requirement for xQuery though.

Sanguinary

Thanks very much, Martin. Msg 1934 is exactly the message I've been getting in my scenario. I'll review the documentation. It is reassuring to know that this is indeed expected behavior. Thanks again. – I