Getting "Argument 1 of an xml-type data method" modify "must be a string literal" by inserting an attribute in xml

Question

Try the following code. But getting "Argument 1 of the xml data type" modify "method should be a string literal. Searched alot, but could not find a solution to this problem.

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

Answer 1

The problem is not with the sql variable: with the value you are trying to insert, since you include XPath in the modification statement. You cannot combine this command - you need to use a literal:

So you need to use:

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

Then it works fine.

Answer 2

You can use something like this - just showing the use of the variable part. The same thing you can do as part of a call change

Assuming you have such a hierarchy

 <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)