在xml中插入属性时，获取xml数据类型方法“modify”的参数1必须是字符串文字

Trying the following code. But getting 'The argument 1 of the xml data type method "modify" must be a string literal' error. searched alot but cant find any solution for this problem

下面的代码。但是获得xml数据类型方法“modify”的参数1必须是字符串文字的错误。搜索了很多，但是没有找到解决这个问题的方法

SET @Path = '/@ParentNodeName/@NodeName/child::*'
SET @x.modify('insert attribute status {sql:variable("@status")}
               as first into (' + @Path + ')[1]')

2 个解决方案

#1

The problem isn't the sql:variable with the value you're trying to insert - it's the way you include the XPath into your modify statement. You cannot string together that command - you need to use a literal:

问题不在于sql:变量和要插入的值——而是您将XPath包含到修改语句中的方式。您不能将该命令串在一起—您需要使用文字:

So you need to use:

所以你需要使用:

SET @x.modify('insert attribute status {sql:variable("@status")}
               as first into (/Parent/Node/)[1]')

Then it works just fine.

然后就可以了。

#2

You can use something like this - Just showing the usage of variable part. Same thing you can do as part of modify call

你可以用这样的东西-只是显示变量部分的用法。与修改调用的一部分一样

Assuming you have hierarchy like this

假设你有这样的等级制度

<Root>
     <Elem1/>
         <Parent1/>
              <Separator/>
                  <Child1/>
</Root>

Query:-

查询:

DECLARE @Root VARCHAR(50)
DECLARE @Entity VARCHAR(50)
DECLARE @ParentNode VARCHAR(50)
DECLARE @Separator VARCHAR(50)
DECLARE @ChildNode VARCHAR(50)


SET @Root = 'Root'
SET @Entity = 'Elem1'
SET @ParentNode = 'Parent1'
SET @Separator = 'separator'
SET @ChildNode = 'Child1'

select Parent.P.value('.', 'varchar(max)') as MyValue, 
T.uniqueId, T.XMLCol
from [XMLTable] as T
cross apply XMLTable.XMLCol.nodes('(/*[local-name()=sql:variable("@Root")]/*[local-name(.)=sql:variable("@Entity")]/*[local-name(.)=sql:variable("@ParentNode")]/*[local-name(.)=sql:variable("@Separator")]/*[local-name(.)=sql:variable("@ChildNode")])[1]')  as Parent(P)

#1