Getting 'The argument 1 of the xml data type method "modify" must be a string literal' while inserting a attribute in xml

Question

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

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

Krongold

Try that!! It works!! Inserting dynamic XML Nodes via XML-DML modify? – Leakey

Answer 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:

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.

Hydrofoil

Any possiblity of using a dynamic path...instead of giving the absolute path – Krongold

@Sam: no, I'm afraid, not - at least I haven't ever been able to get it to work :-( With SQL Server 2008 you can specify a sql:variable for the value to insert - but not for the path where to insert to – Hydrofoil

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

Apart