SQL Server XML 数据类型和 QUOTED_IDENTIFIER

发布于 2024-09-10 17:00:16 字数 803 浏览 2 评论 0原文

任何人都可以提供对此的见解吗？

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

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

代码如下：

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'）有关。

感谢您提供的任何见解。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

拥抱我好吗 2024-09-17 17:00:16

一个非常简单的测试用例

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)

给出

消息 1934，第 16 级，状态 1，第 8 行
SELECT 失败，因为以下原因
SET 选项设置不正确：
'QUOTED_IDENTIFIER'。验证设置
选项正确用于
索引视图和/或索引
计算列和/或查询
通知和/或 xml 数据类型
方法。

这是在此处传递中记录的

执行 XQuery 和 XML 数据
修改语句要求
连接选项
QUOTED_IDENTIFIER 已开启。

但我还没有看到为什么这是 xQuery 的要求的原因。

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