如何使用 SQL DML 动态更改索引值 [英] How to dynamically change the index value with SQL DML

问题描述

我是 XML 和 SQL 的初学者.如何在插入语句中指定索引，如下所示.以下语句给出了更新路径无效的 throws 异常.

I am a beginner in XML with SQL. How can I specify index within an insert statement as below. The following statement gives throws exception with invalid path to update.

set @xmldata.modify('insert <Test>Test1</Test> after (/xyz/abc)[sql:variable("@index")]')

sql:variable() 函数 (XQuery) 有一些例子，但这并不是说要更改索引，而是要更改语句的其他部分.

sql:variable() Function (XQuery) has some examples, but that doesn't speak about changing the index but other part of the statement.

推荐答案

中的Expression2insert (XML DML) 必须返回单个节点.检查在第一次解析查询时完成，然后 SQL Server 无法评估您用来确定表达式是否始终返回单个节点的谓词.但是，如果您在括号中指定文字值，SQL Server 知道只会返回一行.

Expression2 in insert (XML DML) has to return a single node. The check is done when the query is parsed the first time and SQL Server can then not evaluate the predicate you are using to figure out if the expression will always return a single node. But if you specify a literal value in the brackets SQL Server knows that only one row will ever be returned.

要解决您的问题，您需要在表达式末尾添加 [1].

To solve your issue you need to add a [1] to the end of your expression.

set @xmldata.modify('insert <Test>Test1</Test> 
                     after (/xyz/abc[sql:variable("@index")])[1]')

这会给你你想要的节点

/xyz/abc[sql:variable("@index")]

这将告诉 SQL Server 该表达式保证返回一个节点.

and this will tell SQL Server that the expression is guaranteed to return one node.

(/xyz/abc[sql:variable("@index")])[1]

SQL 小提琴

这篇关于如何使用 SQL DML 动态更改索引值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！