SQL Server XML数据类型和QUOTED_IDENTIFIER

问题描述

谁能提供对此的见识?

我已经使用XML数据类型开发了导入过程.在导入sProc将数据插入到表中之后，我运行另一个过程以使用导入的表更新另一个表.

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.

如果更新过程是在SET QUOTED_IDENTIFIER OFF的情况下创建的，则会引发异常.我想了解为什么会这样.

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

代码如下:

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

我假设它与引用的数据类型('VARCHAR(15)')或xml查询路径元素('/records/record')有关.

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.

推荐答案

一个非常简单的测试用例

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)

给予

通过此处

我还没有找到为什么这是xQuery的要求的原因.

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

这篇关于SQL Server XML数据类型和QUOTED_IDENTIFIER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！